Import Visual FoxPro file using macro (Access 2000)

Topic

New Reply

Very long line broken into parts by HansV to prevent horizontal scrolling

I have regularly been importing Visual Foxpro files into my access database. I now want to automate this procedure with code/macro. However I’m having trouble with the sytax.

To get the Database Name property, I linked the required table and looked at the table properties as suggested in the help file. However, it looks like there are too many arguments within it to me.

Also not really sure what to put for source and destination.

This is the code I have so far:
DoCmd.TransferDatabase acImport, “ODBC”, “ODBC;DSN=Visual FoxPro Tables;” & _
“SourceDB=U:ColdharbourComp_B.DBC;SourceType=DBC;Exclusive=No;” & _
“BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE=wname”, _
acTable, “u:coldharbourComp_B.DBC”, “wname_b”, False

any help would be much appreciated.

Amanda

Reply | Quote

Replies

Hi Amanda,

Sorry for not responding sooner – I don’t normally work with FoxPro so it took some research on my part to try to answer you question. First of all, I don’t believe you can paste in the link information from the ODBC table properties as you apparently did. Based on the help file from VBA (I assume you are really using VBA and not trying to do this in a macro) this is what I think your statement should look like:

DoCmd.TransferDatabase acImport, _
“ODBC Database”,
“ODBC;DSN=Visual FoxPro Tables;” & “SourceDB=U:ColdharbourComp_B.DBC;SourceType=DBC;Exclusive=No;” & “BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE=wname”, _
acTable, _
“wname”, _
“wname_b”

I tried to break it for each parameter so it would be clear what was what. Note that you will have to keep the ODBC link in order for this to work – see Microsoft Knowledge Base Article 225861 for details on why – briefly, the ISAM driver for FoxPro was removed when Jet 4.0 was developed.

Since that is the case, why not adopt an alternate strategy for doing the import, and simply use a make table query to copy the table to your Access database. Running a query with VBA (or even with a macro – not recommended) is fairly straightforward. Hope this helps.

Reply | Quote

Wendell

Many thanks for doing that research. Unfortunately I still can’t get the code to work (yes I am doing it in VBA).

Could you expand at all on your suggestion on running a make table query. That would be fine, but how would I get the query to look to the V foxpro table?

Many thanks

Amanda

Reply | Quote

It’s a pretty straightforward process. Since you have an ODBC link to the table you can open the table and read it’s contents. So what you do is create a select query that contains all the fields of the table, and has no criteria so you get all the records. Then you change that query to a make-table query using the menu options or toolbar – Access will prompt you for the table name. Then you can run that make-table query repeatedly and it will create an Access version of the table. If the table already exists, it will warn you that the existing Access table is going to be deleted. It’s also easy to automate, as all you have to do is create a VBA statement that runs the query – there are several options in VBA for doing that – and you can turn off the warnings in VBA as well. Hope this helps – if you need more details on any of the steps, please post back.

Reply | Quote

OK. I see what you mean. Thanks a lot.

Reply | Quote

Amanda,

To add to Wendell’s reply, the attached text file is an exported code module that has some functions I use when working with Visual FoxPro (VFP) databases via ODBC. You can import/copy this file into a standard code module to test code. See ImportOrLinkTableODBC function and TestImportTableODBC sub for example of importing (or linking) VFP table. You’ll have to replace the generic parameters in test sub with your actual database path, DSN name etc. If interested there are also functions for creating an ODBC DSN for a specific VFP database programatically, relinking linked VFP tables if the path to the .DBC changes (e.g., from a local to network path), and setting the “Description” property of linked tables (ODBC or otherwise) to reflect the connection string. These can be useful if your Access applications need to interact with FoxPro databases. (Note: The FoxPro applications I work with are written in VFP 6.0).

Also, if you are importing VFP tables that are “cataloged” in a .DBC file (as seems to be case), normally the generic DSN you’d use would be “Visual FoxPro Database” rather than “Visual FoxPro Tables”, which normally connotes a “Free Table Directory”, where you specify a path to folder with “uncataloged” (standalone) .DBF files instead of path to .DBC file. I find it more reliable to create a new DSN for specific VFP database (either programatically or thru user interface). You can then use this DSN name in the code the links or imports the tables.

HTH

Reply | Quote

Mark

Perfect. That is fantastic. It works a treat.

Many many thanks

Amanda

Reply | Quote

Mark

I was wondering if you could help me. I’ve been using your function ImportOrLinkTableODBC to import a vfoxpro table. I now want to import several vfoxpro tables in one session. I have put your function in a class module and am passing in the different *.dbc name each time. It looks as though all the correct tables have been imported, yet when I open them, they are all from the same *.dbc (the first one).

When I test each one individually it is still importing the first *.dbc table no matter what name I give it.

for example when I change the last bit of strSourceDB to comp_C.dbc it still imports comp_B

Sub testFoxpro()
Dim strDSN As String
Dim strSourceDB As String
Dim strTable As String

Dim clsfoxpro As clVFoxPro

Set clsfoxpro = New clVFoxPro
strDSN = “Visual FoxPro Tables”
strSourceDB = “C:Documents and SettingsGMy DocumentsTRPColdcomp_bdbc”
strTable = “wname”
clsfoxpro.ImportTableODBC strDSN, strSourceDB, strTable
End Sub

Hope this makes sense. Any help would be greatly appreciated.

Amanda

Reply | Quote

Amanda,

Problem probably due to using same DSN for each import. Recommend create new DSN for each VFP database you want to import tables from. Easiest way to do this is to “manually” import/link table – select “ODBC Databases()” from list in import dialog, then create new Machine Data Source, specify “Visual FoxPro database (.DBC)” option and path to the .DBC file. Once this is done, to avoid having to hard-code paths and table names, recommend create two new tables: tbl_DSN, to store DSN name & database path; and tbl_Import, to store table names (assuming you are importing same tables from different source databases). tbl_DSN has 3 fields, DSN_ID (Long) – an index to sort on, DSN_Name (Text – 30) – name used when DSN created, DBC_Path (Text – 255) – full path to .DBC file. tbl_Import has 2 fields, tbl_ID (Long) – index, tbl_Name (Text – 50) – name of table to import. (You don’t have to use these names, just an example – the sample code uses these names.) The attached text file is exported code module. TestImportFoxPro sub opens two recordsets based on the two tables & loops thru each, importing each table listed in tbl_Import from each data source specified in tbl_DSN, calling ImportOrLinkTableFoxPro function (same code as previously posted) for each iteration of inner loop. I tested this by creating two new DSN’s (named “VFP1″ and VFP2” to keep things simple), updating tbl_DSN with paths, etc, and adding 3 table names to tbl_Import. The test sub successfully imported total of six tables (the second set of tables automatically had a “1” appended to table name). The Debug.Print statement (inner “Next” loop) is used to help demonstrate how the loop sequence works.

You may be able to adapt this technique for running multiple FoxPro imports automatically.

HTH

Reply | Quote

Thanks Mark D,

Your help has been very valuable.

Amanda

Reply | Quote