When is the "OpenDatabase" Method Needed? (1 Viewer)

[cblaine99]

Hello,

I am quite new to both Microsoft Access and VBA. I am using Access 2007.

I have been asked to write some simple VBA code to be used inside an existing
.mdb database. I will be using DAO.

My question is very simple. If I want to open a recordset, is the following code
sufficient?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblStudents")

Or, do I first need to explicitly open the database using the "OpenDatabase" method?

I would think that the database is already open when I launch the application,
so I don't know why I would need to open it. On the other hand, I see many VBA code examples
that use the "OpenDatabase" method.

Thank you!

 

[pbaldy]

What you show is what I normally do. This is my template code, which includes the cleanup code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  set rs = nothing
  set db = nothing

 

[Galaxiom]

The database where the code is running is already open and is specified by CurrentDb. The OpenDatabase method is used to work with other databases.

The code you posted is more than sufficient. This is enough:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblStudents")

Many developers use the extra unnecessary step of setting CurrentDb to a variable because that is what they saw while learning but have little idea why.

CurrentDb is not an object but a Method of the Application. It returns a pointer to the database where the code is running. Each time you call it, a separate pointer with no relation to the previous CurrentDb calls is returned.

It is used far more than it needs to be (such as in the example code you posted). It is only necessary when the database is referred to more than once in the same context, becomeing more useful when working with the TableDef and QueryDef Collections of the database.

 

[cblaine99]

Thank you, Galaxiom and pbaldy, for your answers and sample code. They are greatly appreciated.

 

[Galaxiom]

What you show is what I normally do. This is my template code, which includes the cleanup code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  set rs = nothing
  set db = nothing

As I pointed out previously, setting a variable to the output of CurrentDb isn't really required in this circumstance.

However there is one step that aught to be included in that template code. The Recordset should be closed when no longer required. Setting the variable to Nothing removes it from the client memory but it doesn't tell the server.

rs.Close

If this step is not done, the database engine will only release it after the timeout. While this might not be important in a small database it could add to the significantly to the server's load in a big system.

 

[cblaine99]

Thank you for explaining the need for the Close method.

 

[pbaldy]

However there is one step that aught to be included in that template code. The Recordset should be closed when no longer required. Setting the variable to Nothing removes it from the client memory but it doesn't tell the server.

rs.Close

If this step is not done, the database engine will only release it after the timeout. While this might not be important in a small database it could add to the significantly to the server's load in a big system.

Thanks, I'm always open to learning. I've heard different views on that, and I suppose what swayed me is a statement on the MSDN site regarding .Close, which says:

An alternative to the Close method is to set the value of an object variable to Nothing

I interpreted that to say that setting to nothing would accomplish the same thing as the .Close. Have I misunderstood?

 

[MarkK]

I always use .Close, but I just let the object variable go out of scope. Increasingly actually, I don't even declare a variable, I just open the recordset in a With block, like . . .

With CurrentDb.OpenRecordset(SQL)
   Do While Not .EOF
      ...
      .MoveNext
   Loop
   .Close
End With

. . . but still close it at the end.

 

[JANR]

But what if you get an error within your with-block?

On Error Goto Handler

With CurrentDb.OpenRecordset(SQL)
   Do While Not .EOF
      ...
      .MoveNext
   Loop
   .Close
End With

ExitPoint:
Exit Sub

Handler:
	msgBox Err.Number
	Resume ExitPoint
End Sub

Dosen't that leave the recordset still open?

So we can put the .Close statement in the ExitPoint, but what if an error occured before you open a recordset. Would not an error occur if you try to .Close a recordset that has not been open?

Jan R

 

[Galaxiom]

So we can put the .Close statement in the ExitPoint, but what if an error occured before you open a recordset. Would not an error occur if you try to .Close a recordset that has not been open?

I have used a CloseObject function that does an Is Not Nothing test followed by a State property test on the variable before closing it if necessary. It could close recordsets and connections and Set them to Nothing. I have posted it on this forum before.

Now I tend to avoid errors by encapsulating the steps, testing everything going in to each step and the requirements to set up the variable. If expected functioning fails in any way, I send an error flag back from the function rather than actually failing within.

 

[The_Doc_Man]

You asked about using OpenDatabase and similar constructs to give you a database context. There is one and only one time when I need to use the construct

Set dbCur = CurrentDB

That is because I am using a version of Access for which ADO and DAO methods can both be in use for different actions depending on the object in question, but in the given case I specifically want a DAO pointer to the database. So I have to declare

Dim dbCur = DAO.Database

in order to assure the correct "flavor" of pointer, then use the Set statement.

This comes up for me because of the requirement of using DAO databases to get to the .Execute method to run SQL queries. {DAOdatabase}.Execute is more efficient than DoCmd.RunSQL and also has better error handing properties. you can have it do an automatic "rollback on fail" to make cleanups easier. That method can give me counts of .RecordsAffected - and even can trigger trap events if I want them.

Before you jump on this particular bandwagon, there are merits to using the DoCmd way as well. This is a case of picking your favorite flavor. It is ONLY relevant because you enquired regarding opening a particular database other than the current one.

 

[JANR]

I have used a CloseObject function that does an Is Not Nothing test followed by a State property test on the variable before closing it if necessary. It could close recordsets and connections and Set them to Nothing. I have posted it on this forum before.

This methode is for ADO right Galaxiom? since DAO do not have a .State property as far as I know.

But I can however perhaps do something like this to be sure that recordsets as closed after I'm done with the DAO Recordset

Set rs=Currentdb.OpenRecordset("whatever")
..... do whatever
rs.Close

ExitPoint:
On Error Resume Next
rs.Close
Exit Sub

Handler:
....
Resume ExitPoint
End Sub

JanR

 

[Cronk]

Galaxiom,

I always set a reference to Currentdb. I got tripped up once with the following:

Currentdb.execute ("some sql to affect table entries")

Currentdb.recordsaffected returns 0 even if some records are modified/deleted/added

 

[Galaxiom]

Thanks, I'm always open to learning.

That goes for all of us.

I've heard different views on that, and I suppose what swayed me is a statement on the MSDN site regarding .Close, which says:

An alternative to the Close method is to set the value of an object variable to Nothing

I interpreted that to say that setting to nothing would accomplish the same thing as the .Close. Have I misunderstood?

You clearly have not misunderstood what is written there but one wonders how authoritative their statement would be. They make other patently ridiculous claims. Consider this alternative advice, also from Microsoft. (I realise that this is about older versions of Access.)
https://support.microsoft.com/en-au/kb/289562

If you do not release a recordset's memory each time that you loop through the recordset code, DAO may recompile, using more memory and increasing the size of the database.

To avoid consuming unnecessary resources and increasing database size, use the Close method of the Recordset object to explicitly close the recordset's memory when you no longer need the recordset.

Curiously their sample makes no mention of setting the object variable to Nothing.

Perhaps we should check for this next time someone complains about unexplained bloating?

Personally I doubt that setting the object variable to Nothing is going to do the same as a Close. A recordset is opened by the engine and loaded into a memory structure. Locks are put on the records as required. Close runs a method of the object. Presumably the locks are released as part of the Close. Setting the variable to Nothing does not suggest to me that it does anything but deallocate the memory leaving the engine to time out on the locks.

I am an adherent to the philosophy "If you Open it Close it."

 

[pbaldy]

I appreciate your thoughts; I'm going to add a Close to my template code.