Converting A97 to A2000

E

Ed

I'm trying to convert an Access 97 database to Access
2000. At the end of the conversion process I get the
error message,

"There were compilation errors during the conversion or
enabling of this database. The database has not been
saved in a compiled state. The performance of this
database will be impaired because Microsoft Access will
need to recompile the database for each session."

I select OK and try to open the database. The VB editor
then appears with the error message, "Compile error: can't
find project or library."

I then changed my references from DAO 3.5 to 3.6 and tried
to compile the database using "Debug > Compile x.mdb" At
this point I come up with the error message, "Compile
error: Method or data member not found" and it highlights
the following line of code:

tblTo.[LAST NAME] = Forms!form1![LAST NAME]

Any ideas on how I can fix this mess?
 
B

Bruce M. Thompson

Check out the following page at Doug Steele's web site relative to references
issues:

http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<

Ed said:
I'm trying to convert an Access 97 database to Access
2000. At the end of the conversion process I get the
error message,

"There were compilation errors during the conversion or
enabling of this database. The database has not been
saved in a compiled state. The performance of this
database will be impaired because Microsoft Access will
need to recompile the database for each session."

I select OK and try to open the database. The VB editor
then appears with the error message, "Compile error: can't
find project or library."

I then changed my references from DAO 3.5 to 3.6 and tried
to compile the database using "Debug > Compile x.mdb" At
this point I come up with the error message, "Compile
error: Method or data member not found" and it highlights
the following line of code:

tblTo.[LAST NAME] = Forms!form1![LAST NAME]

Any ideas on how I can fix this mess?
Click to expand...
 
G

Guest

-----Original Message-----
I'm trying to convert an Access 97 database to Access
2000. At the end of the conversion process I get the
error message,

"There were compilation errors during the conversion or
enabling of this database. The database has not been
saved in a compiled state. The performance of this
database will be impaired because Microsoft Access will
need to recompile the database for each session."

I select OK and try to open the database. The VB editor
then appears with the error message, "Compile error: can't
find project or library."

I then changed my references from DAO 3.5 to 3.6 and tried
to compile the database using "Debug > Compile x.mdb" At
this point I come up with the error message, "Compile
error: Method or data member not found" and it highlights
the following line of code:

tblTo.[LAST NAME] = Forms!form1![LAST NAME]

Any ideas on how I can fix this mess?
.
Click to expand...

Oly...

I would comment out the line by placing a '
in front of the line and re-complile.
 
V

Van T. Dinh

OK... you have to tell us what the "tblTo" is. It is not a Object Variable
referring to a Table since you cannot assign Table Field values that way.

Post the relevant code for the whole Sub / Function including the
Declaration of "tblTo".
 
E

Ed

Here's the code from start to finish ... Thanks.

Option Compare Database 'Use database order for string
comparisons

Function aAdds()
On Error GoTo erraa
Dim Mydb As Database, db As Database, tblfrom As
Recordset, tblTo As Recordset
Dim tblx As Recordset
Set Mydb = DBEngine.Workspaces(0).Databases(0)
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)
Set tblTo = Mydb.OpenRecordset("tblRPCN", DB_OPEN_TABLE)

Set db = CurrentDb()
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)
Dim y As Control

Set y = Forms!form1!rpcn
Dim F As Form
Set F = Forms!form1
Dim y1 As Field
tblfrom.Index = "primarykey"
tblfrom.Seek "=", y
'''MsgBox "y: " & y
tblTo.AddNew
'tblTo.rpcn = Forms!form1!PCN
tblTo.[LAST NAME] = Forms!form1![LAST NAME]
tblTo.[FIRST NAME] = Forms!form1![FIRST NAME]
tblTo.Title = Forms!form1!Title
tblTo.CC = Forms!form1!CC
tblTo.[JOB COD] = Forms!form1![JOB COD]
tblTo.GRP = Forms!form1!GRP
tblTo.[B/F] = Forms!form1![B/F]
tblTo.PCN = Forms!form1!PCN
tblTo.UPDATE

Do Until tblfrom.NoMatch
tblTo.AddNew
'tblTo.rpcn = tblfrom.PCN
tblTo.[LAST NAME] = tblfrom.[LAST NAME]
tblTo.[FIRST NAME] = tblfrom.[FIRST NAME]
tblTo.Title = tblfrom.Title
tblTo.CC = tblfrom.CC
tblTo.[JOB COD] = tblfrom.[JOB COD]
tblTo.GRP = tblfrom.GRP
tblTo.[B/F] = tblfrom.[B/F]
tblTo.PCN = tblfrom.PCN
tblTo.UPDATE

Set y1 = tblfrom.[rpcn]
tblfrom.Seek "=", y1
Loop
Beep
tblTo.Close
Forms!form1.Refresh

Set tblx = Mydb.OpenRecordset("XAUTH2", DB_OPEN_TABLE)
Set x = Forms!form1!CC
tblx.Index = "primarykey"
tblx.Seek "=", x
If Not tblx.NoMatch Then
MsgBox "Please See Form xAuth2form"
End If

Exit Function
erraa:
'MsgBox "in aAdd()"
Beep
Resume Next
End Function

-----Original Message-----
OK... you have to tell us what the "tblTo" is. It is not a Object Variable
referring to a Table since you cannot assign Table Field values that way.

Post the relevant code for the whole Sub / Function including the
Declaration of "tblTo".

--
HTH
Van T. Dinh
MVP (Access)



I'm trying to convert an Access 97 database to Access
2000. At the end of the conversion process I get the
error message,

"There were compilation errors during the conversion or
enabling of this database. The database has not been
saved in a compiled state. The performance of this
database will be impaired because Microsoft Access will
need to recompile the database for each session."

I select OK and try to open the database. The VB editor
then appears with the error message, "Compile error: can't
find project or library."

I then changed my references from DAO 3.5 to 3.6 and tried
to compile the database using "Debug > Compile x.mdb" At
this point I come up with the error message, "Compile
error: Method or data member not found" and it highlights
the following line of code:

tblTo.[LAST NAME] = Forms!form1![LAST NAME]

Any ideas on how I can fix this mess?
Click to expand...


.
Click to expand...
 
V

Van T. Dinh

OK.

1. I assumed you have remove ADO Library from the References. If not, it
is safer to use the declaration:

Dim Mydb As Database
Dim db As Database
Dim tblfrom As DAO.Recordset
Dim tblTo As DAO.Recordset

(it is clearer to have only 1 Variable per Dim statement).

2. The coding:

tblTo.[LAST NAME]

referring to the Field (value) seems to work fine in A97 but not A2K or AXP.
I prefer to use the full reference like:

tblTo.Fields("LAST NAME")

I am sure the abbreviation

tblTo![LAST NAME]

also works since Fields is the default Property of Recordset.

Note that you have heaps of Field references using the old coding in the
posted code so you will need to change the Field references to one of the 2
methods above. I put a * in your posted code where you need to change.
 
E

Ed

After making the changes, it compiled all the way through -
however ... when I try to run it, everything locks up.
-----Original Message-----
OK.

1. I assumed you have remove ADO Library from the References. If not, it
is safer to use the declaration:

Dim Mydb As Database
Dim db As Database
Dim tblfrom As DAO.Recordset
Dim tblTo As DAO.Recordset

(it is clearer to have only 1 Variable per Dim statement).

2. The coding:

tblTo.[LAST NAME]

referring to the Field (value) seems to work fine in A97 but not A2K or AXP.
I prefer to use the full reference like:

tblTo.Fields("LAST NAME")

I am sure the abbreviation

tblTo![LAST NAME]

also works since Fields is the default Property of Recordset.

Note that you have heaps of Field references using the old coding in the
posted code so you will need to change the Field references to one of the 2
methods above. I put a * in your posted code where you need to change.

--
HTH
Van T. Dinh
MVP (Access)



Here's the code from start to finish ... Thanks.

Option Compare Database 'Use database order for string
comparisons

Function aAdds()
On Error GoTo erraa
Dim Mydb As Database, db As Database, tblfrom As
Recordset, tblTo As Recordset
Dim tblx As Recordset
Set Mydb = DBEngine.Workspaces(0).Databases(0)
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)
Set tblTo = Mydb.OpenRecordset("tblRPCN", DB_OPEN_TABLE)

Set db = CurrentDb()
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)
Dim y As Control

Set y = Forms!form1!rpcn
Dim F As Form
Set F = Forms!form1
Dim y1 As Field
tblfrom.Index = "primarykey"
tblfrom.Seek "=", y
'''MsgBox "y: " & y
tblTo.AddNew
'tblTo.*rpcn = Forms!form1!PCN
tblTo.*[LAST NAME] = Forms!form1![LAST NAME]
tblTo.*[FIRST NAME] = Forms!form1![FIRST NAME]
tblTo.*Title = Forms!form1!Title
tblTo.*CC = Forms!form1!CC
tblTo.*[JOB COD] = Forms!form1![JOB COD]
tblTo.*GRP = Forms!form1!GRP
tblTo.*[B/F] = Forms!form1![B/F]
tblTo.*PCN = Forms!form1!PCN
tblTo.UPDATE

Do Until tblfrom.NoMatch
tblTo.AddNew
'tblTo.*rpcn = tblfrom.*PCN
tblTo.*[LAST NAME] = tblfrom.*[LAST NAME]
tblTo.*[FIRST NAME] = tblfrom.*[FIRST NAME]
tblTo.*Title = tblfrom.*Title
tblTo.*CC = tblfrom.*CC
tblTo.*[JOB COD] = tblfrom.*[JOB COD]
tblTo.*GRP = tblfrom.*GRP
tblTo.*[B/F] = tblfrom.*[B/F]
tblTo.*PCN = tblfrom.*PCN
tblTo.UPDATE

Set y1 = tblfrom.*[rpcn]
tblfrom.Seek "=", y1
Loop
Beep
tblTo.Close
Forms!form1.Refresh

Set tblx = Mydb.OpenRecordset("XAUTH2", DB_OPEN_TABLE)
Set x = Forms!form1!CC
tblx.Index = "primarykey"
tblx.Seek "=", x
If Not tblx.NoMatch Then
MsgBox "Please See Form xAuth2form"
End If

Exit Function
erraa:
'MsgBox "in aAdd()"
Beep
Resume Next
End Function
Click to expand...


.
Click to expand...
 
D

david epsom dot com dot au

tblTo.[LAST NAME]
referring to the Field (value) seems to work fine in A97
Click to expand...

Only if you reference the DAO 2.5-3.5 compatibility library.

(david)


Van T. Dinh said:
OK.

1. I assumed you have remove ADO Library from the References. If not, it
is safer to use the declaration:

Dim Mydb As Database
Dim db As Database
Dim tblfrom As DAO.Recordset
Dim tblTo As DAO.Recordset

(it is clearer to have only 1 Variable per Dim statement).

2. The coding:

tblTo.[LAST NAME]

referring to the Field (value) seems to work fine in A97 but not A2K or AXP.
I prefer to use the full reference like:

tblTo.Fields("LAST NAME")

I am sure the abbreviation

tblTo![LAST NAME]

also works since Fields is the default Property of Recordset.

Note that you have heaps of Field references using the old coding in the
posted code so you will need to change the Field references to one of the 2
methods above. I put a * in your posted code where you need to change.

--
HTH
Van T. Dinh
MVP (Access)



Ed said:
Here's the code from start to finish ... Thanks.

Option Compare Database 'Use database order for string
comparisons

Function aAdds()
On Error GoTo erraa
Dim Mydb As Database, db As Database, tblfrom As
Recordset, tblTo As Recordset
Dim tblx As Recordset
Set Mydb = DBEngine.Workspaces(0).Databases(0)
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)
Set tblTo = Mydb.OpenRecordset("tblRPCN", DB_OPEN_TABLE)

Set db = CurrentDb()
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)
Dim y As Control

Set y = Forms!form1!rpcn
Dim F As Form
Set F = Forms!form1
Dim y1 As Field
tblfrom.Index = "primarykey"
tblfrom.Seek "=", y
'''MsgBox "y: " & y
tblTo.AddNew
'tblTo.*rpcn = Forms!form1!PCN
tblTo.*[LAST NAME] = Forms!form1![LAST NAME]
tblTo.*[FIRST NAME] = Forms!form1![FIRST NAME]
tblTo.*Title = Forms!form1!Title
tblTo.*CC = Forms!form1!CC
tblTo.*[JOB COD] = Forms!form1![JOB COD]
tblTo.*GRP = Forms!form1!GRP
tblTo.*[B/F] = Forms!form1![B/F]
tblTo.*PCN = Forms!form1!PCN
tblTo.UPDATE

Do Until tblfrom.NoMatch
tblTo.AddNew
'tblTo.*rpcn = tblfrom.*PCN
tblTo.*[LAST NAME] = tblfrom.*[LAST NAME]
tblTo.*[FIRST NAME] = tblfrom.*[FIRST NAME]
tblTo.*Title = tblfrom.*Title
tblTo.*CC = tblfrom.*CC
tblTo.*[JOB COD] = tblfrom.*[JOB COD]
tblTo.*GRP = tblfrom.*GRP
tblTo.*[B/F] = tblfrom.*[B/F]
tblTo.*PCN = tblfrom.*PCN
tblTo.UPDATE

Set y1 = tblfrom.*[rpcn]
tblfrom.Seek "=", y1
Loop
Beep
tblTo.Close
Forms!form1.Refresh

Set tblx = Mydb.OpenRecordset("XAUTH2", DB_OPEN_TABLE)
Set x = Forms!form1!CC
tblx.Index = "primarykey"
tblx.Seek "=", x
If Not tblx.NoMatch Then
MsgBox "Please See Form xAuth2form"
End If

Exit Function
erraa:
'MsgBox "in aAdd()"
Beep
Resume Next
End Function
Click to expand...
Click to expand...
 
V

Van T. Dinh

The problem may be somewhere else but try deleting the following 2
statements from your code:

Set db = CurrentDb()
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)

tblFrom was created previously and re-creating it without closing the first
one may create problems.

--
HTH
Van T. Dinh
MVP (Access)



Ed said:
After making the changes, it compiled all the way through -
however ... when I try to run it, everything locks up.
-----Original Message-----
OK.

1. I assumed you have remove ADO Library from the References. If not, it
is safer to use the declaration:

Dim Mydb As Database
Dim db As Database
Dim tblfrom As DAO.Recordset
Dim tblTo As DAO.Recordset

(it is clearer to have only 1 Variable per Dim statement).

2. The coding:

tblTo.[LAST NAME]

referring to the Field (value) seems to work fine in A97 but not A2K or AXP.
I prefer to use the full reference like:

tblTo.Fields("LAST NAME")

I am sure the abbreviation

tblTo![LAST NAME]

also works since Fields is the default Property of Recordset.

Note that you have heaps of Field references using the old coding in the
posted code so you will need to change the Field references to one of the 2
methods above. I put a * in your posted code where you need to change.
Click to expand...
Click to expand...
 
E

Ed

Same result. The progress bar in the bottom left corner
says "Running query" and reaches the end, but then
everything locks up.
-----Original Message-----
The problem may be somewhere else but try deleting the following 2
statements from your code:

Set db = CurrentDb()
Set tblfrom = Mydb.OpenRecordset("auth", DB_OPEN_TABLE)

tblFrom was created previously and re-creating it without closing the first
one may create problems.

--
HTH
Van T. Dinh
MVP (Access)



After making the changes, it compiled all the way through -
however ... when I try to run it, everything locks up.
-----Original Message-----
OK.

1. I assumed you have remove ADO Library from the References. If not, it
is safer to use the declaration:

Dim Mydb As Database
Dim db As Database
Dim tblfrom As DAO.Recordset
Dim tblTo As DAO.Recordset

(it is clearer to have only 1 Variable per Dim statement).

2. The coding:

tblTo.[LAST NAME]

referring to the Field (value) seems to work fine in
Click to expand...
A97
but not A2K or AXP.
I prefer to use the full reference like:

tblTo.Fields("LAST NAME")

I am sure the abbreviation

tblTo![LAST NAME]

also works since Fields is the default Property of Recordset.

Note that you have heaps of Field references using the old coding in the
posted code so you will need to change the Field references to one of the 2
methods above. I put a * in your posted code where you need to change.
Click to expand...
Click to expand...


.
Click to expand...
 
V

Van T. Dinh

The problem is NOT in the posted code since the posted code doesn't run any
Query.

You will have to find out which Query is running and see if you can fix the
Query. It sound like a Query that takes too long as with most Queries, I
don't even see the progress bar when I run them.


HTH
Van T. Dinh
MVP (Access)
 
E

Ed

OK, I'll look into that. Thanks for your help.
-----Original Message-----
The problem is NOT in the posted code since the posted code doesn't run any
Query.

You will have to find out which Query is running and see if you can fix the
Query. It sound like a Query that takes too long as with most Queries, I
don't even see the progress bar when I run them.


HTH
Van T. Dinh
MVP (Access)






.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Compile error in table level validation expression " after format 0
Error when converting Access 97 Front End to Access 2003 2
Access97 conversion issues 10
VBA Compile error when converting to Access 2007 4
Office 12 Access Database Engine conflicts with DAO 3.6 library 14
Compile Database Code Problem 9
How can I to try to recover .mdb database size 800mb? 0
Converting database from Access 97 to 2003: problems 1

Top