Access table automatic file transfer to as400

[hoodgy]

What is the easiest way to transfer data nightly from an access database table on our windows server, which is updated hourly to the AS400 I was hoping to do this automatically every night so i can then process the data tranferred.
Thankyou in advance for any suggestions.

 

[koresnordic]

The only way I have found to do this is:
1) If the file you wish to transfer data to on the AS400 doesn't exist, then create it.
2) Use Access to link to this table via ODBC. Also link in the Excel spreadsheet that contains the data you want to transfer.
3) Create an append query to add the data from the linked spreadsheet to this file
4) Create a macro that runs this query (using the openquery method) and quits Access. Either name this Autoexec or have Autoexec reference this macro.
5) Using the Windows scheduler, set it up to run the database every night. This will then open the databse, run the macro (thereby runing the append query) and close Access.

Using the above method, I run reports daily and fortnightly, using data from the AS400. And I transfer data daily back to the AS400 that has been extracted from another database.



Graham

 

[koresnordic]

P.S. sorry for the delay in replying, I have been to busy to look at these pages for a couple of weeks.



Graham

 

[hoodgy]

This may be the long winded way to do it but in the end I did the following as I only needed to do this once a day:-

1. Ran an access macro using a batch file to convert the table to an excel spreadsheet.

2. Ran a AS400 data tansfer session from client access to transfer the data to a file on the as400.

Thankyou very much for your response.