The Linux Page

MS-Access variable types that do not exist?!

Problem

I worked on a project with many functions that access the database. These functions will generally have a Recordset variable. Yet, when I tried to run a script with the recordset declaration, it would fail with the error Undefined variable type.

  Dim rcd As Recordset

Since that definition was not required for the software to work, I ignored the error for a long time.

Now, there are also functions you can use. The type you can skip having it, but a function such as OpenDatabase() you just cannot skip having that definition... That's when I decided to look for the reason and...

So, what's missing?

The fact is that Recordset is defined in a library which by default is NOT referenced in MS-Access. Why it isn't when that system is quite related to databases, I'm not too sure. There could be a good reason such as the fact that there are two types of realms:

ADOActiveX Data Object

DAOData Access Object

They both do the same thing, but they do not offer the same functions and types. If both were defined, then you'd have to always preceed your references with ADO.<name> or DAO.<name>. If you have a software that requires both, you'll have to do that anyway.

According to most people, the DAO is best for MS-Access (it was apparently written for it). So if you are creating a new project, choose that one.

How do you select those libraries?!

Ah! Yes... In MS-Access 2007 (at least) you want to go to your Visual Basic window. There, one of the menu is the Tools menu. The first entry is References. Select that entry.

Now you have a long list of libraries... At the top, you have those that are currently selected. Scroll down and see all of those libraries available to you! Wow!

The ADO and DAO are called something like Microsoft ADO ... and Microsoft DAO ... so scroll down to Microsoft, then search for the library you need. Select it or them, then click Ok or Save.

Voilà! Now you have the Recordset type defined!


Special thanks to Allen Browne for his expert knownledge on the subject.