Import A Text File Weekly Within A Batch File

[troncarver]

hello everyone, i really need some detailed help as the deadline is approaching and I need to find a solution for this. Any help would be greatly appreciated

I currently have a batch file that ftps a text file from a Red Hat Linux Server to my W2k C:\. I would like to make another command in the batch file that imports this text file into an existing access table. I would like the text file to repopulate the table everytime it is imported. I do not want the data added on to the existing data in the table.

Thank you for your time and insight.!!!

 

[Bat17]

You can link a table to a text file. That way it will always be up-to-date.
For an automated system that will run from a batch file I would probably just build a small DB with the main table linked and the text file linked Create two macro's, one to delete existing data and the other to append the new data. You can then run code when the DB opens to run the two queries and then shut itself down again.
The only problems I see with this is making sure that the FTP has completed before calling the new DB and the possibility of record locking if someone is using a record when you delete the table.

Peter

 

[troncarver]

Peter thanks for responding. What macro would I use to delete the table data? What macro would I use to append the new data? Do i make an import spec and then reference that within the macro?

What code would i run when the DB opens? VB? and where would I find this code and where would I put it in the process? Would I put the code in the macros?

Also, how are these macros going to execute when the program opens?

 

[Bat17]

1) Create a new DB.
2) Link to your existing data table.
3) Link to your text file - use the wizard to make sure that the data types are correct.
4) Create a form, and on the On open event of the form add this code

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE MyData.* FROM MyData"
DoCmd.RunSQL "INSERT INTO MyData SELECT MyTextFile.* FROM MyTextFile"
DoCmd.SetWarnings True
Application.Quit

Adjust table names in code to match the tables you have just created in 2 and 3.
5)Tools>Startup>Display Form/Page... add the form you just created above.

That should do it. The DB will close itself after it has run the code so if you need to get back in to change some thing you need to hold in the shift key when you open the DB as this will bypass the startup options.