Conversion Problem Access 2002/2003 to 2007

[Vic]

I have a fairly large database (65 meg) that I'm converting to an accDB in
Access 2007. One of the routines in the app is to selectively merge tables
from 2 different databases into a new database. I'm using a routine from
Microsoft Help & Support titled "How To Copy a DAO TableDef Including
User-Defined Properties" which doesn't work (tables don't get copied) in the
accDB database while it does work in Access 2007 as an MDB. Here is a link
to the routine: http://support.microsoft.com/kb/217011


The first problem is that it gets the error "Method or Data Member Not
Found" on the statement "Set p = f.CreateProperty(SP.Name, SP.Type)" for
CreateProperty in the "Copy Access/User Field Properties" routine. Since I
don't have any user-defined properties I've tried commenting out this
for-next loop but the tables still don't get created. When it tries to copy
fields it gets a "Type Missmatch" error on the statement Set SF =
SourceTableDef.Fields(f1) in the Copy Fields routine.

Is there a replacement routine for copying tables documented somewhere for
Access 2007 (accDB) databases.

Thanks for any help received!


Vic

 

[Paul Shapiro]

I didn't look at the code KB article you referenced, but check the VBA
references. If it's an older article, it probably used DAO to do the
copying. Your accDB may not have a reference to DAO, or ADO might have
higher priortiy in the reference list. Both DAO and ADO have similar
objects, but some of the properties are different and the behaviors are
definitely different. Safest is to disambiguate all Dim statements, so
instead of:
Dim rst as RecordSet
try:
Dim rst as DAO.RecordSet

 

[Vic]

Thanks for the reply Paul. Actually, 1 of the things that does seem to be a
problem with the accdb vs the mdb is the recordset and database diminension
statements need to be qualified with DAO or ADODB. I insured that all of
these dimension statements are now correctly qualified in both the MDB and
accdb versions. As I mentioned it works fine with the MDB version so is all
I'm doing is running it through the conversion process without any other
changes and it fails when I run the accdb version.


Vic

 

[Paul Shapiro]

I tested this by creating a new blank accdb in Access 2007 format. I
modified the first 4 lines of code from your referenced kb article by
specifying DAO. for the declarations:

Public Function CopyTableDef(SourceTableDef As DAO.TableDef, TargetDB As
DAO.Database, TargetName As String) As Integer
Dim SI As DAO.Index, SF As DAO.Field, SP As DAO.Property
Dim T As DAO.TableDef, I As DAO.Index, F As DAO.Field, P As DAO.Property
Dim I1 As Integer, f1 As Integer, P1 As Integer

I wrote a test function to copy a table def from another db (which is in
Access 2003 format):

Public Function TestCode() As Boolean
Dim dbSource As DAO.Database
Dim tdfSource As DAO.TableDef
Dim dbTarget As DAO.Database

Set dbSource =
Application.DBEngine.OpenDatabase(Name:="c:\temp\absData.mdb",
ReadOnly:=True)
Set tdfSource = dbSource.TableDefs("Abstract")
Set dbTarget = CurrentDb()

TestCode = CopyTableDef(SourceTableDef:=tdfSource, TargetDB:=dbTarget,
TargetName:="CopiedTable")
End Function

The test procedure compiled and ran without errors, and the specified table
definition was copied correctly.

 

[Vic]

Paul,

You are the man!

That's it, not only do recordsets and database dimension statements need to
be qualified but so does everything else! I made the same changes and my
code now works correctly. Hmm wonder what else is lurking in this database.

Thanks Paul, your assistance is greatly appreciated!


Vic

 

[Marshall Barton]

That's it, not only do recordsets and database dimension statements need to
be qualified but so does everything else! I made the same changes and my
code now works correctly. Hmm wonder what else is lurking in this database.

While that is a good thing to do regardless, it also points
out that you are referencing both libraries. If you did not
intend to do that, you should definitely remove the
reference you are not using.

A quick way to check if you need a library is to uncheck its
reference and compile your project. If it compiles without
error, the project is not using the library.

 

[Vic]

Marshall,

Yes I am using both references. The reason it needed to be qualified is in
the MDB version the DAO library (Microsoft DAO 3.6 object library) is my #3
reference and MS ActiveX Data Object Library is my #6 reference. I had all
of my ADODB references qualified but not the DAO references.

However, in the accdb version Microsoft has done away with the DAO 3.6
object library and replaced it with MS Office 12.0 Access Database Engine
Object. This is now the last reference and is now behind the ADO library.
I see now I could have moved this reference in front of the ADO reference
and that also should have solved the problem,

Vic