VBA Compile error when converting to Access 2007

  • Thread starter Thread starter Nigel D
  • Start date Start date
N

Nigel D

I have converted a database from Access 2003 to 2007, but the VBA project
will not compile/run. The problem seems to be with the DAO, but when I go
into Tools References and add the MS DAO 3.6 object library back in I get an
error saying 'Name conflicts with existing module, project, or object
library'. The code it falls over on is:

Dim MyIndex As Index
Set MyIndex = MyTable.CreateIndex("PrimaryKey")
With MyIndex
.Primary = True 'falls over on this section
.Required = True
.Unique = True
End With

The message I get is 'Method or Data Member not found'. Does anyone have any
idea how to resolve this?
Many thanks
 
Nigel D said:
I have converted a database from Access 2003 to 2007, but the VBA project
will not compile/run. The problem seems to be with the DAO, but when I go
into Tools References and add the MS DAO 3.6 object library back in I get
an
error saying 'Name conflicts with existing module, project, or object
library'. The code it falls over on is:

Dim MyIndex As Index
Set MyIndex = MyTable.CreateIndex("PrimaryKey")
With MyIndex
.Primary = True 'falls over on this section
.Required = True
.Unique = True
End With

The message I get is 'Method or Data Member not found'. Does anyone have
any
idea how to resolve this?
Click to expand...

It could be an ambiguous reference. If you're using DAO, then disambiguate
the declaration as:
Dim MyIndex as DAO.Index

DAO and ADODB have similar objects, and the one you get if you don't specify
depends on the listing order of the references.

If that's not it, look at the properties of MyIndex in the debugger, after
commenting out any lines that prevent compilation.
 
Paul`s given you a good answer. I just thought I`d add in that if you're
using the new ACCDB file format in Access 2007, you must use the Microsoft
Office 12.0 Access database engine Object library, not the Microsoft DAO 3.6
Object library. You can only use the DAO object library if you've left the
database in the older MDB file format.

However, everything that's in the DAO object library is also in the Access
database engine Object library
 
Nigel D said:
I have converted a database from Access 2003 to 2007, but the VBA project
will not compile/run. The problem seems to be with the DAO, but when I go
into Tools References and add the MS DAO 3.6 object library back in I get
an
error saying 'Name conflicts with existing module, project, or object
library'. The code it falls over on is:

Dim MyIndex As Index
Set MyIndex = MyTable.CreateIndex("PrimaryKey")
With MyIndex
.Primary = True 'falls over on this section
.Required = True
.Unique = True
End With

The message I get is 'Method or Data Member not found'. Does anyone have
any
idea how to resolve this?
Many thanks
Click to expand...
 
k:!jbb

Douglas J. Steele said:
Paul`s given you a good answer. I just thought I`d add in that if you're
using the new ACCDB file format in Access 2007, you must use the Microsoft
Office 12.0 Access database engine Object library, not the Microsoft DAO
3.6 Object library. You can only use the DAO object library if you've left
the database in the older MDB file format.

However, everything that's in the DAO object library is also in the Access
database engine Object library
Click to expand...
 
Back
Top