Converting an Access 97 Database to Access 2002 -

[Guest]

I am working on converting an Access 97 DB to Access 2002 and am getting
compiler errors. Some of them I've been able to fix, because I've seen them
before. The problem I'm having is a "references" problem, I think, but I
don't know what reference/library I need to include for a "Table".

The code "in part" is:
", MyTable As Table"

Error: "User-defined type not defined"

Thanks for any help!

 

[Dirk Goldgar]

I am working on converting an Access 97 DB to Access 2002 and am
getting compiler errors. Some of them I've been able to fix, because
I've seen them before. The problem I'm having is a "references"
problem, I think, but I don't know what reference/library I need to
include for a "Table".

The code "in part" is:
", MyTable As Table"

Error: "User-defined type not defined"

Thanks for any help!

That is old code -- it was old even in Access 97 -- and isn't supported
by DAO 3.6. Change it to

Dim MyTable As DAO.Recordset

Also, you may need to add a reference to the Microsoft DAO 3.6 Object
Library, if you don't already have that reference set. Your programming
life will probably be easier, too, if you remove the reference to
Microsoft ActiveX Data Objects 2.x Library, because that defines some
objects that conflict with identically-named DAO objects.

 

[Guest]

Okay, thank you. That helped. However, now I have this function with all
these methods that went with the "table" (I guess), so I'm not sure how to
replace them. Does anyone have any suggestions, or can someone tell me where
to go to get the information?

Here is the entire function I'm working on:

Function rms_ValidateNewBox(ByVal boxid As String) As Integer
' validate that new boxid is not in table
Dim mydb As Database, MyTable As DAO.Recordsets

rms_ValidateNewBox = 0
Set mydb = CurrentDb
Set MyTable = mydb.TableDefs("tblboxes")

MyTable.Index = "PrimaryKey"
MyTable.Seek "=", boxid

If MyTable.NoMatch Then rms_ValidateNewBox = -1

MyTable.Close


End Function

Thanks for any help.

 

[Dirk Goldgar]

Okay, thank you. That helped. However, now I have this function
with all these methods that went with the "table" (I guess), so I'm
not sure how to replace them. Does anyone have any suggestions, or
can someone tell me where to go to get the information?

Here is the entire function I'm working on:

Function rms_ValidateNewBox(ByVal boxid As String) As Integer
' validate that new boxid is not in table
Dim mydb As Database, MyTable As DAO.Recordsets

rms_ValidateNewBox = 0
Set mydb = CurrentDb
Set MyTable = mydb.TableDefs("tblboxes")

MyTable.Index = "PrimaryKey"
MyTable.Seek "=", boxid

If MyTable.NoMatch Then rms_ValidateNewBox = -1

MyTable.Close


End Function

Thanks for any help.

Try this version, which is a pretty close translation of what you had:

'----- start of revised code #1 -----
Function rms_ValidateNewBox(ByVal boxid As String) As Integer

' validate that new boxid is not in table

Dim mydb As Database, MyTable As DAO.Recordset

rms_ValidateNewBox = 0
Set mydb = CurrentDb
Set MyTable = mydb.OpenRecordset("tblboxes", dbOpenTable)

MyTable.Index = "PrimaryKey"
MyTable.Seek "=", boxid

If MyTable.NoMatch Then rms_ValidateNewBox = -1

MyTable.Close

Set MyTable = Nothing
Set mydb = Nothing

End Function

'----- end of revised code #1 -----

However, I wouldn't do it that way. I'd probably do it something like
this:

'----- start of revised code #2 -----
Function rms_ValidateNewBox(ByVal boxid As String) As Integer

' validate that new boxid is not in table

Dim mydb As Database, MyTable As DAO.Recordset

Set mydb = CurrentDb

Set MyTable = mydb.OpenRecordset( _
"SELECT * FROM tblboxes WHERE boxid = '" & boxid & "'", _
dbOpenSnapshot)

With MyTable
rms_ValidateNewBox = .EOF
.Close
End With

Set MyTable = Nothing
Set mydb = Nothing

End Function

'----- end of revised code #2 -----