Changing mdbs (Acc2k VBA)

Topic

New Reply

Hi there all!

I am in DB1 and I am wanting to open DB2, open the form DB2 and start running some VBA code in it. The VBA code in DB2 must then close DB1 so that it can do a compact and repair on it.

I have got the VBA code to do the compact and repair, I just do not know how to get the VBA code in DB1 to open DB2 and start running the VBA code in it, and for the VBA code in DB2 to close DB1.

Does anyone know how to do this?

James

Reply | Quote

Replies

James,
This code will get your DB2 open but you may have to be innovative once the code is running in DB2 as to how to check the the code in DB1 has terminated and therefore allows you to run the code in DB2 on DB1.

Dim App as Object
Dim strDB1Path As String, strDB2Path As String
strDB1Path = CurrentProject.name
strDB2Path = “whatever the target db path is”

Set app = CreateObject(“access.application.9”)
app.OpenCurrentDatabase (strDB2Path)
app.run yourprocedurename, arg1, arg2…
set app = nothing

The Problem with this approach is if you need to pass wrkgrp info or other command line switches you can’t.

So

Dim lngReturn As Long
‘–using the shell method allows us to pass the wrkgrp and user info–

lngReturn = Shell(“C:Program filesMicrosoft officeOfficemsaccess.exe” /wrkgrp (your wrkgrp path) /NoStartup “strDB2Path in brackets here” /cmd (pass an value to the database here, perhap DB1Path for use in you code in DB2 ))
DoEvents

Set app = GetObject(strDB2Path)
app.run yourprocedurename, arg1, arg2…
Set app = nothing
docmd.RunCommand acCmdExit

DB2 will do its thing and DB1 will close. You could use the argument passed in the cmd variable in your autoexec macro or startup form in DB2 to point back at the calling DB1 perhaps.

It is a bit sketchy but hopefully it gives you a starting point.

Cheers
Simon

Reply | Quote

Thanks very much, i’ll play with this on Friday, Monday and Tuesday and see how it comes how!

James

Reply | Quote

Hi Simon,

I have tried this out and I’ve almost got it working the way it needs to.

The catch is that the code in DB1 wants to wait until the code in DB2 is finished and is closed before continuing. This causes a bit of a problem as the code in DB2 does a compact and repair on DB1, and since DB1 is open – DB2’s code fails.

Any suggestions of what to do?

James

Reply | Quote

Why don’t you simply set the second database to compact on close?

Reply | Quote

Got it working fine thanks!

James

Reply | Quote

I thought this might be a problem but wasn’t sure. The only thing I can think of is in DB1 open DB2 using the shell command I gave you therefore creating no reference in DB1 to DB2. If you plan using this compact routine from different DBs then you can pass the calling DB’s path in the /cmd command line switch of the shell command thereby telling DB2 which DB to work on.
After calling the shell command in DB1 immediately use the docmd.acruncommand accmdExit or similar which will close DB1.
Hopefully DB1 will close before your routine in DB2 tries to access it.

In DB2 start your compact routine from an autoexec macro or startup form or similar. Get the target DB path from the /cmd argument using the Command function. You could trap the error created by DB1 not being ready then check it again after a small interval.
I don’t know if VB has a sleep function so try this api call
Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)

so you would construct a loop to run x times and get it to pause at the end of each loop with the sleep call..

Sleep (500)
then try your compact again and exit the loop if you are successful.

Hope this helps.
Simon.

Reply | Quote

Finally got it working thanks!

James

Reply | Quote