Recognizing Access is Open

[mmogul]

This is an Excel VBA Issue. I have an excel app that reads data and runs some complex queries in an Access DB and returns the data to Excel. I have a process that opens access. This runs everytime the user initiates a query from Excel.

Set objAccess = GetObject(, "Access.Application")
If (Err.Number <> 0) Then
    'Or (objAccess.CurrentDb.Name <> sDBName)
    Set objAccess = Nothing
    Set objAccess = CreateObject("Access.Application")
    objAccess.Visible = False
    objAccess.OpenCurrentDatabase (sDB)

Else
End If

When the user closes excel, I have another process that closes Access. However, it is possible that the user decides not the close excel when prompted with a Save dialog box. At that point, Access has been closed.

Set objAccess = GetObject(, "Access.Application")

'** if err <> 0, then access is not open
If (Err.Number <> 0) Then
Else
    objAccess.Quit
    Set objAccess = Nothing
End If

[\code]

When this situation occurs, if the user initiates another query, the code:

       Set objAccess = GetObject(, "Access.Application")

no longer recognizes that Access is not running.  The err.Number returns a zero.  So further down the code, the code crashes when the application tries to read from a table in the Access database, because the Access db is not open.

Anyone have any thoughts why the code no longer properly recognizes the status of Access (running or not running) and have a solution or workaround.

Thanks.

 

[kjv1611]

As far as seeing when Access is open, if you're looking for a specific database, then there are a couple of different methods, each with their strengths and weaknesses (and there may be other methods).

1. Using some windows api code, you can reference the open windows, and see if there is an open window that starts with "Microsoft Access".. as well as checking for the full database name. The main drawback I see to this method is that if you also need to know whether someone else has the database open, this obviously will not tell you that - it only reads from windows open on your machine.

2. Using the FileSystemObject or the Dir() function to test and see whether the database lock file exits. If you use the Dir() function, that could be a VERY fast check. However, there is another piece you'd want to check for. You'd need to see if that lock file is actually dead, and was left there because of a previous database crash, system failure, power outage, etc. So I would say: use Dir() to see if the file is there, then use the Kill command to try and delete the lock file, and then use the Dir() function to check for it's existence again. Basically, if you're able to delete it, then it was a dead file, and the database was not actually open. If you are unable to delete it, and use the Dir() function to verify it's still there, then yep - that database is open, but again... you don't really know by whom.

So if you REALLY need to see if the database is open.. AND that it's open on the local machine, you may want to use both methods just to make absolute certain. I suppose it depends upon how badly you need/want to know.


As a side note, we cannot see all of the related code in the snippets you posted, but it may be better to use either DAO objects for your database, and any recordsets/tables/queries rather than just straight objects. Here's a link showing an example and some info on that point:

http://support.microsoft.com/kb/146406

An alternative would be ADODB, but I most of the time use DAO when working with MS Office applications.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57