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
therefore, it works, and all libraries are installed correctly (for example, ADO).
So now I want to explicitly declare variable types using the mapped types ("Database" and "Recordset2"), so I change the sub 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 in the line "Dim db as Database":
Compilation error:
User defined type is not defined
So, I understand that the type "Database" is not defined (!). Why?
Note. I 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 the same error with all of them. How is this possible? Why does this work if I do not declare the type?
. , Access ADODB.Connection , "CurrentProject.Connection". :
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
:
db: Connection
rst: Recordset
, ADO , DAO.