Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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
0
tdfreeman
Asked:
tdfreeman
  • 3
  • 2
1 Solution
 
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
 
shanesuebsahakarnCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now