I am currently in the process of automating a data conversion program that is written in access. The Conversion takes dbase IV files from a DOS FoxPro based accounting system to a VB based accounting system using Dbase 5.0 files.
In my .mdb, I have created all the append queries that I need for the conversion to take place, and they all work fine. I am now putting a frontend on the system that will allow the user to select what module(s) that want to convert (Receivables, Payables, Inventory, etc). The system will automatically pull in all the FoxPro tables, and Dbase 5.0 tables that are needed, run the queries, and export the Dbase 5.0 tables back into the proper system directory.
Now comes the problem. In all the following code, I have already selected the Paths and Folders. This is the code that I use to import my Dbase 5.0 files, and this works good:
DoCmd.TransferDatabase acImport, "DBASE 5.0", pstrDestPath, , pstrDestFolder & rstTables!TableName, _
"CM" & rstTables!TableName, 0
Here is the bit of code that is causing the problems:
DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=Foxproconversion;UID=User2;PWD= & "DATABASE=pubs" & pstrSourcePath, , rstTables!TableName & pstrSourceComp & strHistory, "AM" & rstTables!TableName & strHistory, 0
The problem is that when it hits this line to import the FoxPro tables, it wants the user to enter the path to the file. In this conversion 30-40 files will be imported, and I dont want the user to have to select the path each time. I have tried to use the TransferDatabase-Dbase 5.0 method (above) with these files, and that code will import most of the needed foxpro files, but not all. I get a "External Table is Not in Expected Format" error on some of the files.
Now I could set the path in the bit of ODBC code, but this data exchange will be used in a number of places, and I dont want to have to change the ODBC driver search location each time.
Question: How do I pass the folder location to the ODBC driver, without it opening up a box each time for user entry?
Sorry this is so long, but i wanted everyone to know what I was talking about. Thanks for any help.
If anyone would like to see what I have so far, I can zip it down and send it to you for browsing.
-Chuck
In my .mdb, I have created all the append queries that I need for the conversion to take place, and they all work fine. I am now putting a frontend on the system that will allow the user to select what module(s) that want to convert (Receivables, Payables, Inventory, etc). The system will automatically pull in all the FoxPro tables, and Dbase 5.0 tables that are needed, run the queries, and export the Dbase 5.0 tables back into the proper system directory.
Now comes the problem. In all the following code, I have already selected the Paths and Folders. This is the code that I use to import my Dbase 5.0 files, and this works good:
DoCmd.TransferDatabase acImport, "DBASE 5.0", pstrDestPath, , pstrDestFolder & rstTables!TableName, _
"CM" & rstTables!TableName, 0
Here is the bit of code that is causing the problems:
DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=Foxproconversion;UID=User2;PWD= & "DATABASE=pubs" & pstrSourcePath, , rstTables!TableName & pstrSourceComp & strHistory, "AM" & rstTables!TableName & strHistory, 0
The problem is that when it hits this line to import the FoxPro tables, it wants the user to enter the path to the file. In this conversion 30-40 files will be imported, and I dont want the user to have to select the path each time. I have tried to use the TransferDatabase-Dbase 5.0 method (above) with these files, and that code will import most of the needed foxpro files, but not all. I get a "External Table is Not in Expected Format" error on some of the files.
Now I could set the path in the bit of ODBC code, but this data exchange will be used in a number of places, and I dont want to have to change the ODBC driver search location each time.
Question: How do I pass the folder location to the ODBC driver, without it opening up a box each time for user entry?
Sorry this is so long, but i wanted everyone to know what I was talking about. Thanks for any help.
If anyone would like to see what I have so far, I can zip it down and send it to you for browsing.
-Chuck