Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: Getting the record count from an Oracle table in Access

    I have an application in Access that accesses records in an Oracle table. I need to create a unique field id for each record in the Oracle table. I tried to using the following statements:

    rst.Open "SELECT * from afm_refrig_usage"
    intRefrigID = rst.RecordCount + 1

    Unfortunately Access cannot recognize the recordcount and returns a -1.

    How can I take the last record number or the last number in the Refrig_ID field, add 1 to it and store it in the record that is being inserted?

    Thanks,

    Sherry

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Is the Refrig_ID an AutoNumber field? If not, maybe you should change it, then you wouldn't have to worry about increasing the ID field by one every time you add a new record.

    Otherwise, open your recordset with ADO using the following parameters and put a ; at the end of your SQL statement:

    rst.Open "SELECT * from afm_refrig_usage;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    Recordcount should work with that...it does for me.

  3. #3
    Join Date
    Sep 2004
    Posts
    161
    you must try a movelast on the recordset before test he recordcount

  4. #4
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Thanks, the

    rst.Open "SELECT * from afm_refrig_usage;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    worked great!

    Sherry

Posting Permissions

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