Allen Browne: Using Combo Box to Find Records

[oldblindpew]

Allen, I couldn't get your method to work. I got a compile error because
Access or VBA couldn't recognize DAO.Recordset. This seems incredible. I
pressed F1 help and got this:

"The type is a valid type, but the object library or type library in which
it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate object
library or type library. For example, if you don't check the Data Access
Object in the References dialog box, types like Database, Recordset, and
TableDef aren't recognized and references to them in code cause this error."

I went to Tools, References in Visual Basic and there was no "Data Access
Object" in the list. I did find "Microsoft DAO 3.6 Object Library" and
"Microsoft Data Access Components Installed Version". Do one or both of
these have to be checked? Do I really have to manually tell Access that
there is such a thing as DAO?

Could the problem be that the code might not be located in a Public Module??
But the code is part of the form, isn't it?

 

[Mr. B]

Hi, oldblindpew

I am not Allen Browne and I am sure that he may also respond, however, I can
tell you that you just need to add a reference to the Microsoft DAO 3.6
Object Library

That will allow you to access your data using DAO.

Good luck.

 

[oldblindpew]

Thanks, Mr. B
I'm using Access 2007, but my database is a .mdb file developed under
previous versions going back to 1997. From what I read, .mdb files are
already supposed to have a reference to DAO 3.6! I assume I'm looking in the
right place for the References list in Access, i.e., under Database Tools/
Visual Basic/ Tools/ References. Are these settings supposed to change
automatically based upon the file extension of the current database? If so,
they haven't in my case. If not, why does my Access 2007 VBA reference book
say that in Access 2007, MDB files have a (default) reference to DAO 3.6?

 

[Mr. B]

I just did a little testing and you are correct that if you try to declare a
variable as a DAO recordset, Access 2007 will reconize DAO and allow you to
defind a recordset. However, I did notice that there is not referenct in the
Database Tools/

Visual Basic/ Tools/ References for the DAO. I would suggest that you add this reverence to the list of references and see if this will correct the problem.

Not knowing the history of the frontend file, I would not know if that would
have any bearing on the issue, but it can't hurt to try adding the reference.

Byron

 

[Allen Browne]

The correct library for an MDB in Access 2000 or later is:
Microsoft DAO 3.6 Object Library

It's different for an ACCDB file.

There's a table towards the bottom of this page explaining which libraries
to use in each version of Access:
http://allenbrowne.com/ser-38.html

 

[Douglas J. Steele]

"Default" refers to when you're creating a brand new database, not when
you're converting from a previous version.

Yes, there's a difference between using the .mdb and .accdb formats. The mdb
format uses Microsoft DAO 3.6 Object Library, the accdb format uses
Microsoft Office 12.0 Access database engine Object Library. Everything that
was in the DAO library is in the Microsoft Office 12.0 Access database
engine Object Library.