Use Access VBA to capture Field List from Visual FoxPro (dbf) file??? 2

[GeekToMe]

Greetings All!

Can anyone tell me how I can use Access 2000 VBA to connect to a Visual FoxPro file (dbf) and return the field/column list from that file? Code samples would be greatly appreciated.

Thanks in advance,

NateTheGreek

 

[Golom]

Dim conn                        As New ADODB.Connection
Dim rs                          As New ADODB.Recordset
Dim fd                          As ADODB.Field
Dim LPath                       As String
Dim File                        As String

LPath = "C:\myDBFFolder\"
File = "myFile.dbf"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & LPath & ";Extended Properties=dBASE IV;" & _
          "User ID=;Password="

rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.Open File, conn

For Each fd In rs.Fields
    Debug.Print fd.Name
Next

Set rs = Nothing
Set conn = Nothing

 

[JerryKlmns]

Just add

rs.Close
conn.Close

before exterminating them.

 

[GeekToMe]

Thanks for the prompt replies! This is exactly what I needed.

NateTheGreek

 

[GeekToMe]

Sorry, I guess I spoke too soon...

The above connection string is throwing the following error:

Run-time error '-2147217900 (80040e14)':

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Anyone have any additional thoughts?

Thanks again,

NateTheGreek

 

[PHV]

And this ?
rs.Open File, conn, adOpenKeyset, adLockReadOnly, adCmdTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GeekToMe]

I answered my own question. This is the Visual FoxPro connection string that ended up working for me:

conn.Open "Provider=VFPOLEDB.1; Data Source=" & Path & ";"

Thanks everyone for all the feedback!

NateTheGreek