Open DBF file with ADO

[nvwildfire]

I cannot figure out what I am doing wrong and it is getting pretty frustrating. Maybe someone out there can see my error or at least point me in the right direction. The following is the code I am using:

strSQL = "SELECT * FROM " & dlgOpenDBF.FileTitle

Set fConn = New ADODB.Connection
fConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & strFilePath & ";" & _
"Extended Properties=""DBASE IV;"";"

Set frst = New ADODB.Recordset
frst.CursorType = adOpenKeyset
frst.LockType = adLockOptimistic
frst.Open strSQL, fConn

Where dlgopendbf.filetitle = "FMUS_072303.dbf"
and strFilePath = "X:\fuels\GIS_Projects\FMU-Select\GIS_DATA\071703"

I keep getting the following error:

The Microsoft Jet database engine could not find the object 'FMUS_072303.dbf'. Make sure the ofbject exists and that you spell its name and the path name correctly.

All I want to do is read a dbf file into an array using ADO and jet, I was doing this with the foxpro driver but would like to avoid using the foxpro drive because it is not on our xp machines.

Any help on this would be greatly appreciated.

thank you,

kgk

 

[billybobk]

See my FAQ entitled "ADO Basics -- Connect to Access Database" and use this connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myPath & ";" & _
"Extended Properties=dBASE IV;"
(took the quotes off of dbase IV)
make sure your path is correct, and it will work. I use it for .dbf read/write all the time. If it's a network path, be sure to map the network drive to the database! Good luck!


--Bill

 

[nvwildfire]

I figured out whate the problem was, if you try to read in a file name that is longer than 8 characters it will bomb. So what I do now is test for file length and if it is longer than 8 characters it copys the file to a temporary file. The following is the code I finally used, it works great.

intLength = InStr(1, dlgOpenDBF.FileName, dlgOpenDBF.FileTitle, vbTextCompare)
intLength = intLength - 2
strFilePath = Mid(dlgOpenDBF.FileName, 1, intLength)

intSpace = InStr(1, dlgOpenDBF.FileTitle, " ", vbTextCompare)

If intSpace < 1 Then
strFileTitle = dlgOpenDBF.FileTitle
Else
NewName = strFilePath & &quot;\tmpDBF1.dbf&quot;
OldName = strFilePath & &quot;\&quot; & dlgOpenDBF.FileTitle
FileCopy OldName, NewName
strFileTitle = &quot;tmpDBF1.dbf&quot;
End If

If Len(strFileTitle) > 12 Then

result = Dir$(strFilePath & &quot;\tmpDBF1.dbf&quot
If result <> &quot;&quot; Then
Call MsgBox(&quot;Error, file exists. Please delete the file &quot; & strFilePath & &quot;\tmpDBF1.dbf&quot;, vbInformation + vbOKOnly, &quot;Error&quot
Exit Sub
Else
NewName = strFilePath & &quot;\tmpDBF1.dbf&quot;
OldName = strFilePath & &quot;\&quot; & dlgOpenDBF.FileTitle
FileCopy OldName, NewName
strFileTitle = &quot;tmpDBF1.dbf&quot;
End If

Else
strFileTitle = strFileTitle
End If

strSQL = &quot;SELECT * FROM &quot; & strFileTitle

Set fConn = New ADODB.Connection
fConn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source= &quot; & strFilePath & &quot;\&quot; & &quot;;&quot; & _
&quot;Extended Properties=&quot;&quot;DBASE IV;&quot;&quot;;&quot;

Set frst = New ADODB.Recordset
frst.CursorType = adOpenKeyset
frst.LockType = adLockOptimistic
frst.Open strSQL, fConn, , , adCmdText


Thanks for your input bill.

kgk