Connect dBase

[samui]

Hello,

How can I use ADO connect to dBase database by connection string ? (Now I can only connect to access)

[dwhawley]

samui,

i am working on the same problem as you. i posted a thread about the same time you did asking the same question. i found that there is a property called the "Jet OLEDB:Engine Type" property. in order to call it you must specify a provider using the provider property first. here is the code that i am using.

Set conDBF = New ADODB.Connection
conDBF.Provider = "Microsoft.Jet.OLEDB.4.0"
conDBF.Properties("Data Source") = cdlMain.FileName
conDBF.Properties("Jet OLEDB:Engine Type") = 11
conDBF.Open

the value 11 supposedly references the dbase III format, though now i am starting to wonder. if you find anything out, i would love to hear about it. if i figure something out first i will post a solution right away.

[jdavison]

what is this running on?

[dwhawley]

i'm running vb6 sp3 w/ ado 2.1

[jdavison]

what platform is it running. Is it an as400 or an nt boxor...

most likely your best bet is to set up a dsn in odbc and connect to it through there. I dont believe the jet drivers will work for dbase as far as I know.

[dwhawley]

sorry. i'm running windows 98. the ado documentation implies that you should be able to use jet for a variety of data formats. but everything that i have tried does not seem to work. i would like to use to an odbc driver to make my connection, but the database table being viewed will change depending on what the viewer wants to see. unfortunately, the database is fairly old and those no "container" housing all of the tables. therefore, each table needs to be selected individually, putting a wrinkle into writing sql statements.

[dwhawley]

to all of those wondering how on earth you can connect to a dbase III table. you need to use {Microsoft FoxPro VFP Driver (*.dbf)}. i built a connection string using a new data link file and then just pasted it into my code. i must defer to our fellow vb-world member, bar, who actually gave me the solution.

[jdavison]

is the database running on a 98 machine? we just recently had an project on an as400 running dbase. what we had to do is create an odbc connection in the odbc administrator using the odbc driver and used ado to connect through there. we could only use the connection object though. we just ran all the sql through the connection and set a variant equal to the output from the connection. I hope this helps some.

[dwhawley]

the database resides on a windows 98 machine. i used the following code to establish my connection and create a recordset:

'define the default directory for the connection string
iDefDir = InStrRev(cdlMain.FileName, "\") - 1
strDefDir = Left$(cdlMain.FileName, iDefDir)

Set conDBF = New ADODB.Connection
'note that the connection string does not specify an odbc
'driver and that the "SourceDB" is equal to the directory
'that contains the table or tables of interest.
conDBF.ConnectionString = "Driver={Microsoft FoxPro " & _
"VFP Driver (*.dbf)};UID=;SourceDB=" & strDefDir & _
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes" & _
";Collate=Machine;Null=Yes;Deleted=Yes;"
conDBF.Open

If Err <> 0 Then
MsgBox Error$, vbCritical, Err
Exit Sub
End If

Set rsDBF = New ADODB.Recordset
rsDBF.Open "inventry.dbf", conDBF, adOpenDynamic, & _
adLockOptimistic, adCmdTable

If Err <> 0 Then
MsgBox Error$, vbCritical, Err
Exit Sub
End If