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.
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'.