Retrieving Next AutoIncrement Number Using Jet

[Randall A. Maxey]

Using ADO 2.5 to on an Access 97 database. Using .Update on the newly-added
recordset then getting the value of the key field always returns zero.

It works fine when the database is converted to Access 2000. Unfortunately,
that's not an option yet.

Very strange....

Here's the code:

strSql = "SELECT idnProject, lngClientID, lngEngineerID, " & _
"strProjectNumber, strTitle, strSite, strDesign, datCreatedOn, " & _
"strOwner, strDesignNumber FROM tblProject WHERE 0=1"

Debug.Print dePJF.Connections(1).ConnectionString

With rsProj
Set .ActiveConnection = dePJF.Connections(1)
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = strSql
.Open
.AddNew
.Fields("strProjectNumber") = xlProjectData.Cells(4, 1)
.Fields("lngClientID") = idnClient
.Fields("lngEngineerID") = 1
.Fields("strTitle") = xlProjectData.Cells(11, 1)
.Fields("strSite") = xlProjectData.Cells(8, 1)
.Fields("strDesign") = xlProjectData.Cells(7, 1)
.Fields("datCreatedOn") = xlProjectData.Cells(5, 1)
.Fields("strOwner") = xlProjectData.Cells(9, 1)
.Fields("strDesignNumber") = xlProjectData.Cells(3, 1)
.Update
End With

idnProject = rsProj.Fields("idnProject") ' THIS RETURNS ZERO IN ACCESS
97

[Paul Clement]

On Mon, 16 Oct 2000 19:27:11 -0400, "Randall A. Maxey" <rmaxey@cadassociates.com> wrote:

¤ Using ADO 2.5 to on an Access 97 database. Using .Update on the newly-added
¤ recordset then getting the value of the key field always returns zero.
¤
¤ It works fine when the database is converted to Access 2000. Unfortunately,
¤ that's not an option yet.
¤
¤ Very strange....
¤
¤ Here's the code:
¤
¤ strSql = "SELECT idnProject, lngClientID, lngEngineerID, " & _
¤ "strProjectNumber, strTitle, strSite, strDesign, datCreatedOn, " & _
¤ "strOwner, strDesignNumber FROM tblProject WHERE 0=1"
¤
¤ Debug.Print dePJF.Connections(1).ConnectionString
¤
¤ With rsProj
¤ Set .ActiveConnection = dePJF.Connections(1)
¤ .CursorLocation = adUseServer
¤ .CursorType = adOpenKeyset
¤ .LockType = adLockOptimistic
¤ .Source = strSql
¤ .Open
¤ .AddNew
¤ .Fields("strProjectNumber") = xlProjectData.Cells(4, 1)
¤ .Fields("lngClientID") = idnClient
¤ .Fields("lngEngineerID") = 1
¤ .Fields("strTitle") = xlProjectData.Cells(11, 1)
¤ .Fields("strSite") = xlProjectData.Cells(8, 1)
¤ .Fields("strDesign") = xlProjectData.Cells(7, 1)
¤ .Fields("datCreatedOn") = xlProjectData.Cells(5, 1)
¤ .Fields("strOwner") = xlProjectData.Cells(9, 1)
¤ .Fields("strDesignNumber") = xlProjectData.Cells(3, 1)
¤ .Update
¤ End With
¤
¤ idnProject = rsProj.Fields("idnProject") ' THIS RETURNS ZERO IN ACCESS
¤ 97

Try checking the value after the AddNew instead of the Update.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)