Test if Acces file open

JillB · #1 (**permalink**) 01-16-08, 17:10

I am running an Excel VBA module which opens Access file and runs a VBA Access module.

The problem is as soon as the Access file opens, then Excel continues the next steps of the code which relate to copying the Access database.

I would like to halt the Excel code after the Access file is opened, detect if the Access file has been closed, and then let the Excel code continue.

Is this possible in Excel VBA?

All suggestions and poineters would be gratefully received

with thanks,

Jill

Code:

Dim objAccess_Tx_Master As Access.Application
Application.DisplayAlerts = False

Set objAccess_Tx_Master = CreateObject(TempFilePath) ' the local database copy
objAccess_Tx_Master.Visible = True

'*************************************
'DO ALL THE EDITING NOW ON THE DATABASE
'*************************************

'when editing completed, and DB file closed    <<< HOW TO DO THIS TEST?
myStr = MsgBox("Did you modify the database?", vbYesNo)
If myStr = vbYes Then
 MsgBox"This will copy the modified database back to another location"

Marvels · #2 (**permalink**) 01-17-08, 10:21

YOULL HAVE TO CHECK THIS BUT SHOULD BE ABOUT RIGHT

Set app = GetObject(, "Acces.Application")
If app Is Nothing Then
' Acces is not running, create new instance
Set app = CreateObject("Acces.Application")
app.Visible = True
End If

do until
if app.Visible = false
exit do
endif
loop

JillB · #3 (**permalink**) 01-17-08, 17:52

This is quite clever, and so simple, like all good code. Many thanks Marvels

Internet Services

Web Development

Digital Marketing

Consumer Tech