VBA to Import Visual FoxPro database (DBC) table (DBF) into Access

[lucycard]

I need your help with this!

I have a Visual FoxPro version 9 database (DBC container) that contains
several DBF's that I'm trying to write VBA code to import them into Access.

The application is still in Access 97. I found that the
DoCmd.TransferDatabase command is looking for FoxPro 2.6 version files.

I have been able to create a ODBC to the VFP database container and manually
select and import the files.

I must be missing a step because I'm getting an error "Operation is not
supported for this type of object DAO.Recordset":

Dim mycon As ADODB.Connection
Dim myrst As ADODB.Recordset
Dim strFolder, strTabName as string
Set mycon = CreateObject("ADODB.Connection")
Set myrst = New ADODB.Recordset
strfolder = "C:\myfolder\mydb.dbc"
strTabNmae = "myfile.dbf"

mycon = "Provider=vfpoledb;" & _
"Data Source=" & strfolder & ";" & _
"Mode=ReadWrite;" & _
"Collating Sequence=MACHINE:" & _
"Password="

mycon.Open
myrst.Open strTabName, mycon, adOpenDynamic, adLockReadOnly
' In both cases I get the error message:
'Try number one
' DoCmd.TransferDatabase acImport, myrst, strfolder, acTable,
strTabName, strTabName, False
'Try number two
DoCmd.TransferDatabase acImport, , strFolder, acTable,
strTabName, strTabName, False

This is the only code in the app referring to ADODB.

How do I correct this to automate the file import process?

Your help is appreciated!

 

[Cindy Winegarden]

Hi,

I'm not an Access developer but when I look in the MSDN Library at the
TransferDatabase method's arguments, for the DatabaseType argument the list
is about the same as the list in the Access Get external data dialog:
Access, Jet, dBase, Paradox, ODBC Databases, WSS.

Your connection string uses OLE DB and it appears that OLE DB is not
supported.

 

[Douglas J. Steele]

I believe that's correct: you must use ODBC, not Ole DB.

 

[lucycard]

Hi,

I changed approach and tried to use TransferDatabase using ODBC in stages,
first with VFP Free Table, then with a DBF in a DBC. In both cases I created
separate DSNs. I still need some help with this:

I created an ODBC connection to a VFP free table and manually linked it into
Access. This is what the linked file's description looks like:

"ODBC;DSN=Visual FoxPro
Tables;SourceDB=C:\DATAVFP;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;;Table=helloworld"

Next I tried to modify the TransferDatabase MSDN Library example to pull in
the DBF which is part of the DBC and it's failing:

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=VFPDBC;;;LANGUAGE=us_english;" _
& "DATABASE=c:\data\my.dbc", acTable, "helloworld", "helloworld"

Your help is appreciated!