Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Question Unanswered: Manipulating a RecordSet

    I have a recordset whose results I want to manipulate then I want to append it to a table. I was wondering if anyone had a procedure to do this? Can I use two (2) or more recordsets at the same time?



    Thank you!!!

    Mark

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Yes on both counts, what exactly are you trying to do?
    Because in most circumstances I find sql is better than recordsets but not all ways
    Last edited by m.timoney; 05-20-03 at 11:00.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Cool Recordset

    I am working with an Access Front End and a MS SQL Backend. I need to append records to a table (MailBox Table) but before I append the data I need to get the next sequential number from that table (MailBox Table) that I am appending to. So I created an append query that manipluates the data in the needed manner then it appends it to a table (tblCourtAlert Table). Now the problem I need to add the next sequential number to each record in the tblCourtAlert Table that I previously got from the MailBox Table.

    I Created two (2) recordsets that counts the number of records in the tblCourtAlert Table and the other gets the last sequential number. I created a third recordset where I attempted to update each record but I am getting an error Message.

    Here is the Code:

    Set con = Application.CurrentProject.Connection
    stSql = "Select AIQ_NUM as AIQNum "
    stSql = stSql & "FROM tblCourtAlert;"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1 ' 1 = adOpenKeyset


    If (rs.EOF) Then
    MsgBox "There Court Alerts"
    Else
    For a = 1 To CourtAlertCnt
    LastAIQ_Num = LastAIQ_Num + 1
    rs![AIQNum] = LastAIQ_Num
    rs.Update
    rs.MoveNext
    Next a

    I need to update the AIQNum Field in the tblCourtAlert table with the next sequential number.


    Thank you,

    Mark

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    you look to be missing the modification type

    ie
    rs.Edit
    rs![AIQNum] = LastAIQ_Num
    .Update

    or

    rs.addnew
    rs![AIQNum] = LastAIQ_Num
    .Update
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Talking Thank you!!

    Thank you very much. Your reply worked great!!! One additional question, I will be appending this data to a SQL table, how can I lock the sql table so that new records will not be entered while I am appending the new records. I want to insure that no other users attempt to use one of the sequential numbers that I have selected for the append data.



    Mark
    Last edited by Mark Gambo; 05-20-03 at 19:35.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'm glad it helped because i've just realised i was talking DAO and your doing ADO, as for you second question you need to add one of the following to the open statement (i think, i don't do alot of ADO)

    AdoEnums.LockType.OPTIMISTIC
    AdoEnums.LockType.PESSIMISTIC
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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