vba TO IMPORT FOXPRO dbfs or just replace table in 2003

[johnnywinter via AccessMonster.com]

In Access 2003, I have a Macro that imports 5 Foxpro dbfs from our accounting
software at end of each month. I first run another macro that deletes those
files. (If Access 2003 has option to just replace a table that already
exists when it is imported, I cannot find it)

I want to put both of those into VBA code instead of 2 macros. I have the
delete part of it working fine, but having trouble with the import.

Any help, code or a link to where I can figure this out is appreciated.
Actually if there is VBA code that just replaces a file then I could do away
with the delete steps.

Or if you can point out where/how in Access 2003 I can just replace the files
using a macro (so I eliminate the delete steps)


All 5 Tables are in the same source directory on our server and are all
imported with their original Foxpro file name becoming the table name in
Access.

The 5 Foxpro dbf names are: arcust11 , arcash11 , artran11 , archst11 ,
arinvt11


Here is OBCD info on one Foxpro table (archst11) in my macro:

ODBC;DSN=Visual FoxPro Tables;SourceDB=\\SERVER\C\CURRENT MONTH END SBT DBFS\
;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE=archst11

 

[John W. Vinson]

In Access 2003, I have a Macro that imports 5 Foxpro dbfs from our accounting
software at end of each month. I first run another macro that deletes those
files. (If Access 2003 has option to just replace a table that already
exists when it is imported, I cannot find it)

I want to put both of those into VBA code instead of 2 macros. I have the
delete part of it working fine, but having trouble with the import.

Any help, code or a link to where I can figure this out is appreciated.
Actually if there is VBA code that just replaces a file then I could do away
with the delete steps.

Or if you can point out where/how in Access 2003 I can just replace the files
using a macro (so I eliminate the delete steps)


All 5 Tables are in the same source directory on our server and are all
imported with their original Foxpro file name becoming the table name in
Access.

The 5 Foxpro dbf names are: arcust11 , arcash11 , artran11 , archst11 ,
arinvt11


Here is OBCD info on one Foxpro table (archst11) in my macro:

ODBC;DSN=Visual FoxPro Tables;SourceDB=\\SERVER\C\CURRENT MONTH END SBT DBFS\
;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE=archst11

I would suggest not importing or making new tables at all. Instead, run a
Delete query on each local table to empty it (keeping the table structure);
Compact the database to free up the space the tables occupy; and then linking
to the .dbf files (using the TransferDatabase method) and running append
queries to migrate the data.

See the VBA help for TransferDatabase, and use the querydef Execute method to
execute the delete and append queries.

 

[johnnywinter via AccessMonster.com]

Thank you.