Opening the DB connection globally

[VB-Mike]

I am writing a DAO to Access application in which I open the DB and create recordsets every time a new form is loaded throughout my app. Can the DB be opened in one location that can be shared by the entire application. Then create your recordsets and queries based on that open connection rather than closing it and opening several times while the app is running. Can the DB open be put in a module? Would like to clean out my code a bit and feel that opening and closing the db several times in the code is not very efficient!

[bsmith]

Yes you can put the db open in a module. If you declare a global variable in the module that is the database connection, you can access it from anywhere.

' Declare database as you have it.
Public g_dbDatabase as database

The you can reference g_dbDatabase anywhere in your code.

[VB-Mike]

This is what I have so far and cannot get it to work. This code is all in the module:

Public db As Database

Public Sub DBOpen()
Dim DatabaseName As String

DatabaseName = App.Path & "\" & "main2000.mdb"
Set db = OpenDatabase(DatabaseName)
End Sub

I am trying to make the open in the module and create recordsets throughout the application based on that open connection but it keeps giving me the error:

Run time error 91

Object variable or with block variable not set. What am I doing wrong?

[bsmith]

When are you receiving the error. When you open the database or when you are trying to access a recordset connected using db?

[VB-Mike]

I receive the error when trying to open the recordset from the open DB within the form:

Private Sub form_load()
SQL$ = "SELECT * FROM CustMain"
Set rs = db.OpenRecordset(SQL$) 'Errors

inpFName.Text = rs![FName]
inpLName.Text = rs![LName]
End Sub

[bsmith]

You first need to open the database. Or have you already done this?

Private Sub form_load()

    ' This will call the function to Open the database and set the global variable
    DBOpen
    
    SQL$ = "SELECT * FROM CustMain"
    Set rs = db.OpenRecordset(SQL$) 'Errors

    inpFName.Text = rs![FName]
    inpLName.Text = rs![LName]
End Sub

[VB-Mike]

That did it!!!

I was not calling the DBOpen sub routine from within my first form when it loaded, stupid me. I removed all the opens from each form and it works great!! I want to thank you again for the assistance.....

[pardede]

just for the sake of argument, i thought that opening and closing database connection is more efficient thatn opening it globally and keeping it open throughout the execution of the app... ?

The idea is.. keef off your data until you really need it..