Can't open Access 2000+ with vbscript if Access 97 recently closed

[sschrupp]

So I have an Access 97 database that some of my users need, and the database needs data from Access 2000. In A2000 I link to A97 and pass data to A97.

I have vbscript that can be run outside of access that'll run A2000 code, but it's annoying for the user to have to run the script and then go into A97 to run more code to finish updating data.

I made a batch file that can be run from A97 that closes A97 and is then supposed to run the A2000 module, but after it has closed A97 vbscript can't access the A2000 database saying that it's missing or opened exclusively.

It's not missing or opened exclusively. After I've been in A97 any script I try to run that would interact with A2000 fails with the same message. If however I open A2000 myself and close it, then any script that interacts with A2000 runs just fine.

So, is there a way to close A97 and somehow make Windows forget that it was recently in A97 so that it can interact with A2000+?

The obvious solution is for everyone to use A2003, but unfortunately different teams have different versions of Access here with no hope of changing that.

 

[macropod]

Hi sschrupp,

Are you sure it isn't just a timing issue? Try making the code wait a bit to ensure A97 is completely unloaded before continuing on to A2000.


Cheers
[MS MVP - Word]

 

[sschrupp]

Thanks for the suggestion macropod but that doesn't seem to help even after waiting 30 seconds.

This is so wierd because I can manually open the first one with Access 97, close it and immediately open the other one in Access 2003 manually just fine.

The script closes the A97 just fine. It's just when it tries to open A2003 that it chokes saying "C:\stuff\TestUpdateTable.vbs(5, 1) MSAccess: Microsoft Access can't open the database because it is missing, or opened exclusively by another user."

Here are the batch and scripts I'm trying to run in case someone notices a glaring mistake.

Batch file that runs the scripts

cscript "C:\stuff\TESTCloseDB.vbs"
PING 1.1.1.1 -n 1 -w 5000 >NUL
cscript "C:\stuff\TESTUpdateTable.vbs"

pause

TESTCloseDB.vbs (that works fine)

dim accessApp

set accessApp = GetObject("C:\stuff\A97.mdb")
accessApp.quit

set accessApp = nothing
WScript.sleep (10000)

TESTUpdateTable.vbs

dim accessApp

WScript.sleep (10000)
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\stuff\Test.mdb")

accessApp.Run "UpdateSRTable"
accessApp.Quit
set accessApp = nothing

WScript.sleep (10000)

set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\stuff\A97.mdb")

accessApp.Run "UpdateSR"
accessApp.Quit
set accessApp = nothing

The only thing that tickles my suspicion bone is where I create the access object and then I use "OpenCurrentDatabase". I'm not sure how you open a database if it's current, but I THINK that's how I'm supposed to open it! lol

 

[mrmovie]

your batch file:

cscript "C:\stuff\TESTCloseDB.vbs"
PING 1.1.1.1 -n 1 -w 5000 >NUL
cscript "C:\stuff\TESTUpdateTable.vbs"

pause

i am always confused as to when and if one should use "start /wait", i guess you should use it to be on the safe side all the time?

 

[sschrupp]

Phooey, that didn't work either, mrmovie, but thanks for the tip!