Web Development

Ask something
Question Locked

Compact On Close

0 helpful
7 replies

by pd_2004

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello,

I am calling MS Access from a VBA script in MS Excel, executing some queries, then closing Access. The database is set up to compact on close, which is what I want it to do. My question is this, in my VBA script I then open another database. Do I need to insert some code to allow the first database to compact before opening the second? More specifically, I find situation where I would like to be sure that the script waited until the compact process was complete before proceeding with the VBA script. Any insights would be appreciated.

Thank you.

7 replies

by pd_2004

Thank you, Tony. That makes me more comfortable about the first part of my question. However, what if the next part of my code uses a database that is linked to the database that is compacting? At that point I am pretty sure that I get an error message stating that the compacting database is locked. In this situation it would be nice to have the script wait until the database is finished compacting before proceeding with the code. At this point I am thinking some code to check if the compacting database is open, then pause for a time and check again, etc. That may be a crude way to do it. I also am not sure what that code would look like. I appreciate your insights.

Thank you,

Pat

by tony hopkinson

Detect close and fire off a batch file to C&R that database. When it finishes you get an event and then you can make the DB avaiabkle again.

ShellExecute, CreateProcess and WaitForSingleObject will be worth looking up.

HtHs

by pd_2004

Thank you for your help. I found/modified the code to open the Access application, then wait for it to close. In between I want to have it open a specific database and run a module in that database. I believe I need to set the appaccess variable to the instance of access that was created in the command line code below. Any help would be appreciated.

Thank you,

Pat

Public Sub ExecCmd(cmdline As String)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ReturnValue As Integer

' Initialize the STARTUPINFO structure:
start.cb = Len(start)

' Start the shelled application:
ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

Dim appAccess As Access.Application

Application.OpenCurrentDatabase "C:\etc\etc\etc\databasename.mdb", True

Do
ReturnValue = WaitForSingleObject(proc.hProcess, 0)
DoEvents
Loop Until ReturnValue <> 258

ReturnValue = CloseHandle(proc.hProcess)
End Sub

by pd_2004

Hi,

I changed the application.opencurrentdatabase to appaccess.opencurrentdatabase below. I believe I need to set appaccess to the instance that was created in the command line execution above it.

Thank you,

Pat

Pat

Public Sub ExecCmd(cmdline As String)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ReturnValue As Integer

' Initialize the STARTUPINFO structure:
start.cb = Len(start)

' Start the shelled application:
ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

Dim appAccess As Access.Application

appAccess.OpenCurrentDatabase "C:\etc\etc\etc\databasename.mdb", True

Do
ReturnValue = WaitForSingleObject(proc.hProcess, 0)
DoEvents
Loop Until ReturnValue <> 258

ReturnValue = CloseHandle(proc.hProcess)
End Sub

This discussion is closed to new replies.