How many db's do I really have open?

[adhoustonj]

Hey AWF,
Hypothetically if I have a form event that had a

Private Sub PlaceholderNameRoutine_Click()
Dim db as DAO.Database
Set db = currentdb()

'call function(i)
TestMyCurrentDB1

set db = nothing
End Sub

Which called 9 other functions in a module/subs such as TestMyCurrentDB2, 3, 4, etc - would I be essentially opening up 10 different "DB's", DAO.Databases, or what.

Public Function TestMyCurrentDB1()
Dim db as DAO.Database

set db = currentdb()
TestMyCurrentDB2

set db = nothing

End Function

I tested recordsets a few days ago but unsure how to test DB's to fully understand what is going on.
For recordsets I did a similar exercise and just did a debug.print between events, subs, modules, and made sure all were outputting expected values. I'm unsure how to go about running a similar test with db's, so here I am if anyone would enlighten me please.

I'm asking this because I'm thinking about moving most of my database operations to be similar to the below

Private Sub PlaceholderNameRoutine_Click()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String

set db = currentdb()
strSQL = ".....x"
set rs = db.openrecordset(strSQL)
With rs
    .addnew
    .update
  
  
    .edit
    .update

rs.close
set rs = nothing
set db = nothing
End Sub

versus just executing strSQL statements.

Private Sub PlaceholderNameRoutine_Click()
Dim db as DAO.Database
Dim strSQL as string

strSQL = "INSERT INTO (xxxx) " & _
              "VALUES ('"xxxxxxxx"')"
db.execute strSQL

strSQL = "UPDATE tblLaDiDa SET xxx = " & YugeNumber & ""
db.execute strSQL

set db = nothing

End sub

Just curious.


Thanks all. Hope all is well.

 

[The_Doc_Man]

Put a breakpoint in the deepest point of your code before you start closing things and explore.

Workspace.Databases property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Open the Immediate window. In that window, type:

Debug.Print Workspace(0).Databases.Count

That should tell you the answer to your question. I could answer for you, but it is better for you to learn how to find this kind of thing out for yourself. Come back and tell us what you learned.

 

[adhoustonj]

Put a breakpoint in the deepest point of your code before you start closing things and explore.

Workspace.Databases property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Open the Immediate window. In that window, type:

Debug.Print Workspace(0).Databases.Count

That should tell you the answer to your question. I could answer for you, but it is better for you to learn how to find this kind of thing out for yourself. Come back and tell us what you learned.

Cool cool, thanks Doc. I'll check this out as soon as I'm back in the office tomorrow.
First thoughts are if I need to Dim x as DAO.Workspace and if it is still relevant in latest Access build version.
Just thinking out loud not asking anyone to put in the legwork. I thought Workspaces were obsolete but I must be confused with an older Access Workgroup or something.


Thank you.

 

[The_Doc_Man]

For the immediate window, you do not need to DIM anything that would be there anyway as an intrinsic structure. Further, the workspace you would create via that DIM isn't necessarily the one you would be using even if you somehow associated to it with perhaps a SET statement. By the time you CAN activate the Immediate window in the context of a database, Workspace(0) already exists and was created by launching MS Access.EXE in whatever way you launched it.

 

[adhoustonj]

That checked out well and the outcome was what I was expecting/hoping.

So just by opening access with none of my VBA/Macros/Forms active:

Debug.Print DBEngine.Workspaces(0).Databases.Count

This returns 1 and then each additional SET adds another database object to the databases collection

Dim db as DAO.Database
set db = currentdb()

with the below removing them.

set db = nothing

 

[Pat Hartman]

I'm asking this because I'm thinking about moving most of my database operations to be similar to the below

Using recordsets, which remain open, is more efficient than running an append query for each row. Each time you run a query that you created with VBA, the database engine needs to "compile" the query and create an execution plan. This takes workspace and time. If you're running a couple of queries, who cares. If you're running thousands, you'll see just how inefficient the method is.

However, the best solution is to use Access the way Access is intended to be used and eliminate all that code by using bound forms.