Retrieving Next AutoIncrement Number Using Jet


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Retrieving Next AutoIncrement Number Using Jet

  1. #1
    Randall A. Maxey Guest

    Retrieving Next AutoIncrement Number Using Jet

    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



  2. #2
    Paul Clement Guest

    Re: Retrieving Next AutoIncrement Number Using Jet

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center