I don't even have DAO library selected in references. DAO is default (since 2007?) so apparently the reference is not necessary.
I don't even have DAO library selected in references. DAO is default (since 2007?) so apparently the reference is not necessary.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
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.
Thank you, 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.
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
Microsoft ActiveX Data Objects 2.5 Library
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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!!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
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.
If you use int6ellisense, when you enter DAO. you will get a list of what is available.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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?
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thank you.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.
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.
I think most often it's along the lines of:
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.Code: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
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.
Last edited by Micron; 04-15-2024 at 01:27 PM. Reason: added comment
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
In your example, shouldn't db.close also be included before doing db = nothing?I think most often it's along the lines of:
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.Code: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
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.