Solved

Check to see if database object is open

Posted on 2004-10-11
6
257 Views
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
        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
0
Comment
Question by:tdfreeman
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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.
0
 
LVL 1

Author Comment

by:tdfreeman
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.
0
 
LVL 41

Accepted Solution

by:
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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 39

Expert Comment

by:stevbe
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.

Steve
0
 
LVL 39

Expert Comment

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

Expert Comment

by:shanesuebsahakarn
ID: 12279569
So I did, fancy that :-) :-)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
trying to catch ODBC error when database opens 2 28
Microsoft Access Write errors seem to be caused by bit fields 4 37
Filter a form 8 15
Dcount help 2 17
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often nā€¦
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

832 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