访问VBA：声明数据库对象的类型时出错：未定义用户定义的类型

In Access 2010 VBA, if I run this sub:

在Access 2010 VBA中,如果我运行此子:

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

所以它工作正常,所有库都安装正确(例如ADO)。

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:

好的,现在我想通过使用显示的类型(“Database”和“Recordset2”)明确声明变量类型,所以我用这种方式修改sub:

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":

当我运行它时,我在“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:

编辑:我刚刚发现Access还通过调用“CurrentProject.Connection”为当前数据库提供ADODB.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

that shows:

db:  Connection
rst: Recordset

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

所以我会用它,因为ADO比DAO更现代。

2 个解决方案

#1

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

尝试检查“引用” - 您将看到没有DAO库。您的第一个示例有效,因为db具有Variant类型。和任务

Set db = CurrentDb()

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

将COM对象DAO.Database置于db中,稍后TypeName确认这是Database。如果你想使用

Dim db   as DAO.Database

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

您必须引用适当的库(例如Microsoft DAO)

#2

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

您可以将“db”声明为对象,其他所有内容都相同。即Dim db As Object

#1