Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO / Access (VB 6 / Access 97)

    Since I do a lot of my database development using MS Access as the backend, I've pretty much always used DAO and I'm regularly getting stuck using ADO! This is probably something simple...

    I've written a function that creates a new record in a table named 'tblGroups'. tblGroups has an autonumber field named 'Group_ID' and once the record has been created, I want the function to return the 'Group_ID' of the new record.

    Using DAO, as soon as you call the 'AddNew' method of the recordset object, the 'Group_ID' value is available to you. However, using ADO I can't seem to get the value of the field. After calling the AddNew method, the AutoNumber field is simply NULL and after I call 'update', the field is set to Zero? All the other fields are fine, I can read the values no problem, it's just the autonumber field...

    Have you got to open a particular type of recordset to be able to read values from AutoNumber fields?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO / Access (VB 6 / Access 97)

    Post the code and perhaps someone will spot the problem.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO / Access (VB 6 / Access 97)

    This is the main section of the code...'CreateRecordset' is just a generic function that sets the active connection and opens a recordset using the passed parameters.

    Public Function UpdateGroup() As Long

    Dim RS_Groups As ADODB.Recordset

    Set RS_Groups = clsConnect.CreateRecordset("tblGroups", adOpenDynamic, adUseClient, adLockOptimistic)

    If (mvarGroupID = 0) Then '// We are creating a new group
    RS_Groups.AddNew
    End If

    '// Update using class properties
    With RS_Groups
    !Group_Name = mvarGroupName
    !Parent_Group_ID = mvarParentGroup
    !Active = mvarActive
    .Update
    UpdateGroup = !Group_ID '// The value of Group_ID is zero at this point
    End With

    '// Destroy objects
    RS_Groups.Close
    Set RS_Groups = Nothing

    End Function

    At some point after calling the 'AddNew', I would expect Group_ID to be the next number from the table. Yet, it remains NULL until calling update, where it changes to ZERO.

    I'm using Microsoft.Jet.OLEDB.4.0 as the provider for my connection.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO / Access (VB 6 / Access 97)

    If I were you, I would take a look at the code that creates the recordset and the version of ADO you're using to do it. I can create an optimistic, recordset using the adOpenDynamic cursor type and add a record to an Access 2000 table using ADO 2.5 and get the PK back from the record immediately after the update. The version of ADO is going to have a lot to do with the result you get, and if I remember correctly, the version that came with VB 6 was one of the buggy ones. If you haven't downloaded and installed any of the updates, that may be your problem.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •