Access VBA: Error when declaring the type of a Database object: User defined type is not defined

Question

In Access 2010 VBA, if I run this sub:

sub test

    Dim db
    Dim rst

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("select * from mytable")

    Debug.Print "db:  " & TypeName(db)
    Debug.Print "rst: " & TypeName(rst)

end sub

then it shows (in the "Inmediate" panel):

db:  Database
rst: Recordset2

so it works, and all libraries are installed correctly (ADO for example).

Ok, now I want to declare explicitly the variable types, by using the types that were shown ("Database" and "Recordset2"), so I modify the sub in this way:

sub test

    Dim db   as Database        ' explicitly
    Dim rst  as Recordset2      ' explicitly

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("select * from mytable")

    Debug.Print "db:  " & TypeName(db)
    Debug.Print "rst: " & TypeName(rst)

end sub

and when I run it, I get the following error at line "Dim db as Database":

Compilation error:
User defined type is not defined

So I understand that type "Database" is not defined (!). Why?

Note: I have also tried:

Dim db   as ADO.Database        ' explicitly
Dim rst  as ADO.Recordset2      ' explicitly

and:

Dim db   as ADODB.Database      ' explicitly
Dim rst  as ADODB.Recordset2    ' explicitly

and:

Dim db   as DAO.Database        ' explicitly
Dim rst  as DAO.Recordset2      ' explicitly

and got same error with all of them. How is it possible? Why does it work if I don't declare the type?

---

Edit: I have just discovered that Access also offers an ADODB.Connection object for the current database, by calling to "CurrentProject.Connection". So I can explicitly declare:

sub test

    Dim db As ADODB.Connection
    Set db = CurrentProject.Connection      ' Access gives an ADODB object too!

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "select * from mytable", db

    Debug.Print "db:  " & TypeName(db)
    Debug.Print "rst: " & TypeName(rst)

end sub

that shows:

db:  Connection
rst: Recordset

So I will use it, since ADO is more modern than DAO.

Glennaglennie

Answer 1

Try to check "References" - you will see there is no DAO library. Your first example works because db has Variant type. And assignment

Set db = CurrentDb() 

puts COM object DAO.Database in db, and later TypeName confirms this is Database. If you want to use

Dim db   as DAO.Database 

You have to Reference appropriate library (Microsoft DAO for example)

Update by @John Shaw : now you can use library 'Microsoft Office 1X.0 Access database engine Object Library' as fully compaible replacement for 'Microsoft DAO'.

Oujda

Right, "Microsoft ADO 3.6 Object Library" is not enabled. If I enable it and press the "Accept" button, then it says "Error when loading DLL library". Probably the cause is that the dll file doesn't exists. That takes me to another question: If the ADO library is not loaded, how are ADO objects created and handled? How can Access work with ADO objects, if ADO library is not loaded? – Glennaglennie

"Enabled" is wrong word here, "Referenced" is better. You can use that COM library as COM library - CreateObject, GetObject or receive Object as return parameter. But if you want to use inside VBA - its' definition must be loaded into VBA, that called here "reference". – Oujda

Hmm, I see, it is a bit tricky :-) Thank you for your information. Anyway I will use the ADODB alternative I found, as I explained at the "Edit:" section of the question. – Glennaglennie

Answer 2

@4dmonster is correct:

Dim db is a variant, not a declared database object.

Tools > References > click on Microsoft Office 16.0 Access database entgin Object Library to effectuate definition type recognition for ADO & ADODB specific objects.

Ceroplastic

Answer 3

Probably your project will be called "Database" which is the default name, changed and you can declare "Dim db as Database"

Cordie

I upvoted this (also noting that it was downvoted by someone). The answer describes what is often an immediate cause for the problem. When you create a new database from "right click -> New -> Microsoft Access Database" in the explorer in Windows, you typically get a database file called something like "New Microsoft Access Database.accdb" but the VBA project inside that file is (nowadays) called "Database", and this causes the name conflict. If you simply rename the project to something else then the problem is solved. It is always a good idea though to be explicit and write DAO.Database. – Blancmange

Answer 4

You can declare "db" as an object and everything else is the same. i.e. Dim db As Object

Misgive