Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    48

    Unanswered: Test if Acces file open

    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"
    Last edited by JillB; 01-16-08 at 18:11. Reason: typos

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    re

    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

  3. #3
    Join Date
    Dec 2007
    Posts
    48
    This is quite clever, and so simple, like all good code. Many thanks Marvels

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •