TransferDatabase of FoxPro file produces error (Access XP)

[Tomasz Nogalski]

In an Access 97 application I used the following command to
import a FoxPro file named "bdeupd.dbf":

DoCmd.TransferDatabase acImport, "FoxPro 3.0", "c:\temp\",
acTable, "bdeupd.dbf", "tblImport"

"tblImport" is a table that already exists in the Access 97
database.

Now I had to update the application to Access XP which has
no direct support for FoxPro files anymore. When I use the
command above I get an error '2507' which tells me that
'the format "FoxPro 3.0" is not an installed database
format or does not support the selected action' (I cannot
print the exact error message here because it is a german
Access XP). I found out that I have to use ODBC instead. So
I created a system DSN named "BDE", using the "Microsoft
FoxPro VFP Driver (*.dbf)" (version 6.01.8630.01).

Then I tried to import the FoxPro database file using
TransferDatabase with ODBC, but without success:

DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=BDE;
UID=;PWD=;SourceDB=c:\Temp;SourceType=DBF;"

produces a 'run-time error 3011'.

DoCmd.TransferDatabase acImport, "ODBC", "DSN=BDE;
SourceDB=c:\temp\", , , "tblImport"

produces a 'run-time error 3170: Could not find installable
ISAM'.

DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=BDE;
SourceDB=c:\temp\", , , "tblImport"

produces a 'run-time error 3011: The Microsoft Jet database
module could not find the object "tblImport1"' (note the
'1' at the end of "tblImport1"; the table "tblImport"
(without a '1') already exists).

Can anyone help me with the correct ODBC string?

Tomasz

 

[Cindy Winegarden]

In [email protected],

In an Access 97 application ...
Now I had to update the application to Access XP which has
no direct support for FoxPro files anymore. ...
produces a 'run-time error 3170: Could not find installable
ISAM'.

The following article may help:
http://support.microsoft.com/?id=283881
"Could Not Find Installable ISAM" Error Message

You may need to download and install one of the earlier versions of MDAC -
perhaps v2.5. See
http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860
for details.

 

[Joe Fallon]

Case "Foxpro"
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=" &
strDSN, acTable, strTableName, strTableName

 

[Tomasz Nogalski]

When I fill in the variables suitable to my application,
the command looks like the following:

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;
DSN=BDE", acTable, "tblImport", "tblImport"

where "tblImport" is the name of a table in the Access
database.

However, I still get a 'run-time error 2507: the format
ODBC Database is not an installed database format or does
not support the selected action'.

Regards
Tomasz

 

[Tomasz Nogalski]

I am a little bit confused about the article "http:
//support.microsoft.com/?id=283881": A FoxPro ISAM is not
explicitly mentioned. So I checked the dbase ISAM entries
in the registry - they are okay; if I rename the file
"Msxbde40.dll" (just to reinstall it from the MS Office
cd-rom), Windows File Protection will restore it
immediately.

I am also confused about the MDAC articles. In "http:
//support.microsoft.com/default.aspx?
scid=/support/servicepacks/MDAC/2.6/RTMmanifest.asp" you
find the information that "The Visual Fox Pro ODBC driver
is shipped with Visual FoxPro, Microsoft Windows® 2000,
Microsoft Office 2000, and MDAC 2.5.". As I am working with
Windows 2000, I assume that I have the Visual FoxPro ODBC
Driver installed (it is available when I create a new DSN).

I can successfully create a link in an Access database to a
FoxPro database using "File", "External data", "Link
tables", then selecting "ODBC databases" from the "Data
types" combo box and selecting the DSN named "BDE",
choosing table "bdeupd" and selecting a unique identifier
in this table: The table gets linked and I can access the
data in it. But in VBA code, all I get is error messages :
-(

Regards
Tomasz

 

[Will]

I hope you don't give up this fight. I'm in the same
situation as you. I tried the solutions Joe and Cindy
suggested. Joe's solution produced 'could not find the
object' "tblImport" or "tblImport1" depending on the
extensions I used. I already tried Cindy's suggestion
about installing earlier versions.

 

[Tomasz Nogalski]

Some trial-and-error revealed the following solution:

DoCmd.TransferDatabase acExport, "ODBC", "ODBC;
DSN=ImportExport", acTable, pstrTable, pstrExportFile

The following prerequisites are necessary:

* A (System-)DSN with data source name "ImportExport",
Microsoft Visual FoxPro Driver, database type "Folder with
free tables" (a german-to-english translation of mine),
field "Path" may not be empty (although it does not matter
which path you fill in there).
* The table "pstrTable" must exist in the Access database.
* The export file "pstrExportFile" has to be specified with
path and extension. It does not matter if the export file
exists or not - it will be created new with each export.

Example:
DoCmd.TransferDatabase acExport, "ODBC", "ODBC;
DSN=ImportExport;", acTable, "tblImport", "C:\Temp\export.
dbf"

Two problems are appearing:

* The export file can not be accessed during the run-time
of MS Access XP. For example, you get an error when you try
to delete the file in the windows explorer. You have to
close MS Access XP to further work with the file (it is not
sufficient to close the MS Access file).
This problem did not exist in MS Access 97.
* In MS Access 97, the command "DoCmd.TransferDatabase
acExport, "FoxPro 2.0", pstrPath, acTable, pstrTable,
pstrExportFile" created (besides the DBF file) another two
files with the extensions ".cdx" and ".inf". These files
may be needed by other programs to work with the FoxPro
file.


The import of data from a FoxPro database (.DBF) into an MS
Access XP database works using the following command:

DoCmd.TransferDatabase acImport, "ODBC",
"ODBC;DSN=ImportExport;SourceDB=C:\Temp\;SourceType=DBF;",
acTable, pstrImportFile, pstrTable

The following prerequisites are necessary:

* A DSN (see above).
* The table "pstrTable" should not exist before in the
Access database (otherwise a consecutive number will be
added to the table name).
* The import file "pstrImportFile" has to be specified
without path and without extension. Instead, the path is
specified in "SourceDB=" and the extension is specified in
"SourceType=". Otherwise a run-time error 3011 is produced.

Example:
DoCmd.TransferDatabase acImport, "ODBC",
"ODBC;DSN=ImportExport;SourceDB=c:\Temp\;SourceType=DBF",
acTable, "import", "tblImport"

One problem is appearing:

* The import file can not be accessed during the run-time
of MS Access XP (see above, analog to export file). In my
case this is precarius as the imported files should be
renamed after the import. This problem did not exist in MS
Access 97.

If anyone has a solution for the problems I mentioned, I
would be happy to know them ;-)

Regards
Carsten