Check to see if database object is open

With the following how do I check and see if the database object has opened?  I can check to be if it is set to something, but how do I check to see if it has been opened or not?


Global dbsNew      As Database

    Set dbsNew = wrkDefault.CreateDatabase(DBFilename, _
        dbLangGeneral, dbEncrypt)
....
    Set dbsNew = wrkDefault.OpenDatabase(DBFilename)

***************
I tried the following, but this only shows if the database has been set.  If the code fails between the first statement
above and the next statement, how do I know the correct way to clean up my connections?  If the database is open, I want to run the SQL statement below, otherwise nothing really happened, so I don't.
If the first statement runs, and the second does not, the following code executes the else, but dbsNew is not open and the execute does not work.
***************

    If Not dbsNew Is Nothing Then
        dbsNew.Close
    Else
        strSQL = "INSERT INTO RunInfo (Program_Started, SQL_Finished, DB_Created, Register_Loaded_Count, Values_Updated_Count) "
        strSQL = strSQL & "VALUES (#" & Format(dtStarted, "YYYY-MM-DD HH:MM:SS") & "#, #"
        strSQL = strSQL & Format(dtSQLProcessed, "YYYY-MM-DD HH:MM:SS") & "#, #"
        strSQL = strSQL & Format(dtDBCreated, "YYYY-MM-DD HH:MM:SS") & "#,"
        strSQL = strSQL & intRecordCounter1 & ", " & intRecordCounter2 & ");"
        Debug.Print strSQL
        dbsNew.Execute strSQL
        dbsNew.Close
        Set dbsNew = Nothing
    End If
LVL 1
tdfreemanAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
This code:

    Set dbsNew = wrkDefault.CreateDatabase(DBFilename, _
        dbLangGeneral, dbEncrypt)

returns an *open* database object as if you have opened it with OpenDatabase. You do not have to explicitly open it with OpenDatabase.
0
 
shanesuebsahakarnCommented:
Can you not just assume that if the variable is not Nothing then it is open? Whenever you close it, you should set it to Nothing in any case.
0
 
tdfreemanAuthor Commented:
<Can you not just assume that if the variable is not Nothing then it is open?>

From my errors I would have to say no, to the question.

But if I have set it but haven't opened it, it is no longer nothing, but it isn't open.  How do I check for that?  I set it when I create a database.  Later in the process I open the database to work with it.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
stevbeCommented:
CreateDatabase returns an open database object ... so If you do not error out it is already open and there is no need for the second line of code.

Steve
0
 
stevbeCommented:
oops ... that is what shane said :-)
0
 
shanesuebsahakarnCommented:
So I did, fancy that :-) :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.