Using ODBC to connect to DBF via Internet

[James Bott]

I need to connect to ODBC via the Internet, to send & receive data from DBFs. The Internet connection will be sending SQL commands thus the need for the ODBC connection. The Internet connection will be via a third-party product so I don't have any options there.

Questions:

1) Can this be done?

2) Alternately, perhaps we could use a SQL database for the third-party product to connect to, then move the data back and forth to the DBFs?

3) Does anyone have any sample code, and/or other ideas of how to do this?

I have searched the forum and couldn't find any references to this type of connection.

James

[Rick Lipkin]

James

We used to use Ms Access .mdb for some of our local intranet apps .. don't see why it would not work using an IP address from the outside tunnel using ADO .. perhaps something like this behind your HTML :

Code: Select all  Expand view

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := "\\10.1.0.0\share\folder\Your.mdb"  // use your IP address
xPASSWORD := "whatever"

xString := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD

// global connection string
oCn := CREATEOBJECT( "ADODB.Connection" )

TRY
   oCn:Open( xString )
CATCH oErr
   Saying := "Could not open a Global Connection to Database "+xSource
   MsgInfo( Saying )
   RETURN(.F.)
END TRY

 

Granted you probably could not use the above exact code in your HTML but I am sure there are some work arounds .. All the above code gets you is the connection to the MS Access database and from there you can use your ado connection to create a oRs ( recordset ) to be able to interact with .Dbf ..

The above is only meant as a concept no ODBC needed .. native ADO.

Rick Lipkin

[James Bott]

Rick,

I have not done this sort of thing before, and I don't think I was clear.

We have a third-party vendor that needs to read and write to our DBFs via the Internet. They are used to doing this when accessing SQL databases but we need them to access our DBFs in an existing application.

So, can we do this via ADO?

James

[Marc Vanzegbroeck]

James,

I never used it, but there is a connectionstring for a DBF

https://www.connectionstrings.com/dbf-foxpro/

[Enrico Maria Giordano]

Yes, you can use Microsoft Visual FoxPro Driver. Install it on the web server (if you are allowed to do it) and use the following connection string:

Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDb=<dbfpath>

EMG

[James Bott]

ADO and Indexes

Does anyone know, if you update DBFs via ADO, are the indexes updated too? If so, how does this work--is there a configuration file that tells the ADO which indexes go with the DBF. Which index types are supported, ntx, cdx, ?

I have been searching the internet but can't find these answers.

James

[Enrico Maria Giordano]

As far as I know, no, you can't use/update DBF indexes using ADO.

EMG

[James Bott]

Thanks, Enrico. That was my fear. Although you can write data, if you can't update indexes, it seems that using ADO has little value except for reading data.

James

[Gale FORd]

I think it depends on the driver. I know the jet engine can update dbf index files. If you have Advanced Database Server you can also provide remote access via ODBC client or OLE DB client for ADO.

[Enrico Maria Giordano]

Thanks, Enrico. That was my fear. Although you can write data, if you can't update indexes, it seems that using ADO has little value except for reading data.

James

ADO is useful when you have to do with SQL. And SQL doesn't need of (external) indexes.

EMG

[Enrico Maria Giordano]

I think it depends on the driver. I know the jet engine can update dbf index files. If you have Advanced Database Server you can also provide remote access via ODBC client or OLE DB client for ADO.

Yes, that's true, it depends on the driver. But I don't know of any driver capable to update DBF indexes concurrently with [x]Harbour or similar. Maybe ADS, but I'm not familiar with it.

EMG

[Gale FORd]

The jet driver and some others allow you to connect to different Foxpro and dBase version. Would have to match up the locking schemes.

[James Bott]

Gale,

Are you suggesting that the Jet ODBC driver could be used by SQL engines other than Access?

James

[Gale FORd]

Yes, Jet is just 1 of many Providers.
There is also Ace. An example connection string for dBase:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\folder;
Extended Properties=dBASE IV;User ID=Admin;

I also found example SQL statement for remote access using Microsoft ODBC driver.
Remote Drives

Driver={Microsoft dBase Driver (*.dbf)};datasource=dBase Files;

Then the SQL syntax example: "select * from \\remotemachine\thefile.dbf"

There are some issues with indexes. Not much help but here are a couple of links.
https://www.connectionstrings.com/dbf-foxpro/
http://www.motobit.com/tips/detpg_asp-dbf-database/

[James Bott]

Tanks, Gale, all good information.

We may not have to deal with indexes. We may just put the incoming data into batch files and process them in the FWH app. Still being able to use indexes would be great.

James