VB Script and Access

Question

I have a vbscript which does the following:

1. Open an access 2003 database
2. Run a subroutine to populate the tables in the database
3. Close the database
4. Run a batch file which uploads the database to an ftp

This vbscript is set up to run as a scheduled task in windows XP. When I manually run the script, everything works perfectly. If I have my computer locked when the scheduled task runs, it also works perfectly. However, if I log out, the task seems to hang up. When I log back in after the task is supposed to run, I find that msaccess.exe is still running in the task manager. Furthermore, there is a lock file on the database that will not go away until I kill the msaccess.exe. I am sure that the credentials I set the task up with are correct since the task obviously starts to run. I see no errors in any of the event logs.

Any ideas?

Answer 1

It is generally not advisable to try to run an app that has user interface without being logged on -- sometimes it works, sometimes it doesn't.

However, you're in luck, as you're likely to be able to move all your VBA code out of Access and into your vbScript, which is quite compatible with VBA. You may have to make some minor changes because it's basically late binding, but it works quite well.

As a start, try porting your VBA code to vbScript and then post back here for help with what doesn't work.

Answer 2

What I ended up doing was creating a VB program that populated tables in an Access database. Then I was able to transfer that file over the FTP.