Old Access Database Conversion

[June7]

I don't even have DAO library selected in references. DAO is default (since 2007?) so apparently the reference is not necessary.

[isladogs]

The DAO reference library items are included as part of the newer Microsoft Office xx.0 Access database engine Object Library where xx is the version number e.g. 12 for Access 2007
You don't need (and indeed cannot have) both that and the old DAO library reference.

If you also have an ADO reference such as Microsoft ActiveX Data Objects 6.1 Library make sure that is lower in the list so that the DAO code takes priority.
Disambiguation means you will also be able to use ADO code in the same database if required.

[Cosimo]

I don't even have DAO library selected in references. DAO is default (since 2007?) so apparently the reference is not necessary.

I did a little investigation, and first saw that I didn't have a library reference with "DAO" in its name after converting my 2003 mdb file to accdb in Access 2007.

When I attempted to select "Microsoft DAO 3.6 Object Library" I received an MS error message to the effect that selecting it conflicted with another library reference that is already selected.

When I googled the issue, I quickly found the answer to the same problem someone else was having while using Access 2010.

The important part of the solution is as follows:

"in the new .accdb file format the DAO functionality is included in the “Microsoft Office 14.0 Access Database engine object library”. If you uncheck this reference, you can then check the DAO 3.6 reference if you want to, but the preferred option would be to leave the default reference to the 14.0 database engine library checked."

In my case because I am using Access 2007 instead of Access 2010 (as the user who had the similar problem above), the library reference in my accdb database is likely the one named "Microsoft Office 12.0 Access Database engine object library”

So it's likely that "Microsoft Office 12.0 Access Database Engine Object Library,” possibly among other things, fulfills the purpose of the "Microsoft DAO 3.6 Object Library" in MS Access 2007.

[Cosimo]

The DAO reference library items are included as part of the newer Microsoft Office xx.0 Access database engine Object Library where xx is the version number e.g. 12 for Access 2007
You don't need (and indeed cannot have) both that and the old DAO library reference.

If you also have an ADO reference such as Microsoft ActiveX Data Objects 6.1 Library make sure that is lower in the list so that the DAO code takes priority.
Disambiguation means you will also be able to use ADO code in the same database if required.

Thank you, Isladogs.

I am uncertain what Library Reference is an ADO reference and what is not an ADO reference in my Access 2007 accdb file.

Could you please take a quick look at the Library References I have selected in Access 2007 to see if any of the references need to be sorted below "Microsoft Office 12.0 Access database engine Object Library"?

Here are all the Library References in the order they appear for me:

Visual Basic For Applications
Microsoft Access 12.0 Object Library
Microsoft Office 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Office 12.0 Access database engine Object Library
Microsoft Visual Basic for Applications Extensibility 5.3

[Micron]

Microsoft ActiveX Data Objects 2.5 Library

[isladogs]

This is the order I would use:

Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object Library 'DAO
Microsoft Office 12.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library 'ADO
Microsoft Visual Basic for Applications Extensibility 5.3

[Cosimo]

Microsoft ActiveX Data Objects 2.5 Library

LOL. A.D.O., of course!

Thanks!

[Cosimo]

This is the order I would use:

Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object Library 'DAO
Microsoft Office 12.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library 'ADO
Microsoft Visual Basic for Applications Extensibility 5.3

That's wonderful. Thank you!!

[Cosimo]

Shot in the dark, but I'd try disambiguating the declarations:

Dim GetPerDb As DAO.Database
Dim PersonSet As DAO.Recordset

Hi again,

I've searched my database and added the DAO prefix where it was missing from "As Database" and "As Recordset".

Can you or anyone else point me to a list of other terms (are they called, "objects"?) I should search my code for to see if "DAO" should be added to those other terms?

I searched the net and compiled the following list. Does it look like a complete list, and if not complete, what is missing or where can I find a list of all such terms that would need to have "DAO" added?

Here is the list I compiled:

Dim X As DAO.Database

Dim X As DAO.Recordset

Dim X As DAO.Workspace

Dim X As DAO.TableDef

Dim X As DAO.Field

Dim X As DAO.Index

Dim X As DAO.QueryDef

Dim X As DAO.Property

Dim X As DAO.Relation

Thanks in advance.

[Welshgasman]

If you use int6ellisense, when you enter DAO. you will get a list of what is available.

[Cosimo]

If you use int6ellisense, when you enter DAO. you will get a list of what is available.

Thank you.

Yes, I since noticed that when I typed "DAO." a drop down list automatically appeared on typing the "." with a list. If what I was attempting to add the prefix to was not on that list but I tried to add "DAO." anyway, that change would be flagged as an error when I compiled the code.

But this left me with one small remaining puzzle: When I examined my “General Variables Declarations” module, I found that adding "DAO." to “Global gMyWorkSpace As Workspace” changing it to “Global gMyWorkSpace As DAO.Workspace” compiled successfully, and was the only such instance of attempting to add "DAO." my other Global declarations that compiled successfully.

So in general, should the "DAO." prefix be added to entries in all modules, including "General Variables Declarations" when the thing (i.e., object?) to which I attempt to prefix "DAO." to appears in the drop down list and also compiles successfully?

[Micron]

DAO is a code library. If it doesn't contain what you need you can't just go making up stuff. While I've seen properties/methods that don't exist in the property sheet for a selected object, I've never seen documentation for one that doesn't exist in any library. To answer your question - possibly OK and wise to do so. We call that disambiguation. However, be careful because some objects (e.g. recordsets) exist in more than one library. If you were to declare a recordset as DAO and tried to use a method or property that belongs to ADO, you will break the code at least.

[Cosimo]

DAO is a code library. If it doesn't contain what you need you can't just go making up stuff. While I've seen properties/methods that don't exist in the property sheet for a selected object, I've never seen documentation for one that doesn't exist in any library. To answer your question - possibly OK and wise to do so. We call that disambiguation. However, be careful because some objects (e.g. recordsets) exist in more than one library. If you were to declare a recordset as DAO and tried to use a method or property that belongs to ADO, you will break the code at least.

Thank you.

And if I remember aright, in cases where I created a variable to use for a recordset, I included a line in the code that went something like this "recordsetvariable.close" when the place where I used that variable was finished running (I assume I also included it in the error handling too). I think also I did something like "recordsetvariable = nothing" on the next line.

But I will remember to investigate this the next time I write code again.

Thanks again.

[Micron]

I think most often it's along the lines of:

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

Set db = CurrentDb
Set rs = db.OpenRecordset(record source here, options here)
'Do stuff

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

There is more than one school of though around setting to Nothing. People get into long point/counter point discussions on how Access deals with garbage (yes that is a real thing) and it's automatic so not needed (so it's bad coding); others say you can't rely on that. I say it can't hurt and if you're so Puritan about unnecessary code then you do your thing and I'll do mine. Note- you may see code that doesn't declare a db variable: Currentdb=OpenRecordset... IMO that's ok if in the procedure there is only one rs, otherwise you end up with multiple CurrentDb statements. That and you certainly should not do that in a loop.

rs.Close is more of a candidate for redundancy I think - because if you set it to Nothing, it is gone out of memory anyway. Not sure if that closes it or not.

[Cosimo]

I think most often it's along the lines of:

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

Set db = CurrentDb
Set rs = db.OpenRecordset(record source here, options here)
'Do stuff

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

There is more than one school of though around setting to Nothing. People get into long point/counter point discussions on how Access deals with garbage (yes that is a real thing) and it's automatic so not needed (so it's bad coding); others say you can't rely on that. I say it can't hurt and if you're so Puritan about unnecessary code then you do your thing and I'll do mine. Note- you may see code that doesn't declare a db variable: Currentdb=OpenRecordset... IMO that's ok if in the procedure there is only one rs, otherwise you end up with multiple CurrentDb statements. That and you certainly should not do that in a loop.

rs.Close is more of a candidate for redundancy I think - because if you set it to Nothing, it is gone out of memory anyway. Not sure if that closes it or not.

In your example, shouldn't db.close also be included before doing db = nothing?