Check to see if database object is open

Posted on 2004-10-11
Last Modified: 2006-11-17
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
        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
        Set dbsNew = Nothing
    End If
Question by:tdfreeman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 41

Expert Comment

ID: 12279158
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.

Author Comment

ID: 12279197
<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.
LVL 41

Accepted Solution

shanesuebsahakarn earned 250 total points
ID: 12279265
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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 39

Expert Comment

ID: 12279548
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.

LVL 39

Expert Comment

ID: 12279549
oops ... that is what shane said :-)
LVL 41

Expert Comment

ID: 12279569
So I did, fancy that :-) :-)

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question