Results 1 to 4 of 4
  1. #1
    EvanScamman
    Guest

    Opening a ADO Recordset (A2k)

    I am trying to use an ADO Recordset to edit data in a linked table. I can't find an example of the code for this anywhere. Could anyone point me in the right direction?

    The code I am using in the attached file, does not update properly.
    Open the form and double-click on a color. it should move from one list box to the other.
    However, the refresh does not refresh. if you wait 5 seconds and then refresh it does update.

    Please can someone tell me what code i should use that will not have this delay, but will still work across a split database.

    Thank you,
    Evan

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

    Re: Opening a ADO Recordset (A2k)

    You're doing it the hard way. Opening a recordset, adding a record and closing the recordset for each selection is the slowest possible way to do it. It would make more sense to wait until all the choices had been made and then open a recordset once and write all the records by looping through the list. The fastest way to do it would be by creating the SQL for the append and running it the same way you do the delete in the List2_DblClick event. You could also use the Execute method of the connection to run the SQL, but then you would need to error trap for ADO errors.

    If you really want to do it a record at a time using ADO, the following code works. Note that I changed your object variable names. You can call them anything you like, but I tend to use short names for recordset objects because I'm lazy. I also declared the object variables at the module level (The "m" in the names simply shows their module level scope). That allows you to set them once and then reuse the same active objects instead of destroying and recreating them over and over.

    <pre>Option Compare Database
    Option Explicit

    Private mrst As ADODB.Recordset
    Private mcnn As ADODB.Connection

    Private Sub Form_Close()
    <font color=448800>'destroy the object variables </font color=448800>
    On Error Resume Next
    Set mrst = Nothing
    Set mcnn = Nothing
    End Sub <font color=448800>'Form_Close()</font color=448800>

    Private Sub List0_DblClick(Cancel As Integer)
    If mcnn Is Nothing Then
    <font color=448800>'since your tables are linked, this
    'is all you need to set the connection</font color=448800>
    Set mcnn = CurrentProject.Connection
    End If <font color=448800>'mcnn Is Nothing</font color=448800>
    If mrst Is Nothing Then
    Set mrst = New ADODB.Recordset
    With mrst
    .ActiveConnection = mcnn
    .Source = "tblSelectedColors"
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    <font color=448800>'adLockPessimistic will work as well</font color=448800>
    .LockType = adLockOptimistic
    <font color=448800>'adCmdTableDirect will work as well</font color=448800>
    .Open Options:=adCmdTable
    End With <font color=448800>'mrst</font color=448800>
    End If <font color=448800>'mrst Is Nothing</font color=448800>
    mrst.AddNew
    mrst!colorid = List0.Value
    mrst.Update
    <font color=448800>'leave the recordset open for the next change</font color=448800>
    List0.Requery
    List2.Requery
    End Sub <font color=448800>'List0_DblClick(Cancel As Integer)</font color=448800>

    Private Sub List2_DblClick(Cancel As Integer)
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Delete * from tblSelectedColors where ColorID = " & List2.Value)
    DoCmd.SetWarnings True
    List0.Requery
    List2.Requery
    End Sub <font color=448800>'List2_DblClick(Cancel As Integer)</font color=448800></pre>

    Charlotte

  3. #3
    EvanScamman
    Guest

    Re: Opening a ADO Recordset (A2k)

    Charlotte, thankyou so much! Your code worked perfectly and solved a problem that I have had for over a year with my code. The list boxes actually refresh properly now. I think the line that actually made the refresh work was: Set mcnn = CurrentProject.Connection ----- connecting through the current database, rather than the backdoor like I was doing. Also, thanks for showing me how to only create the object variables once. Definately much neater code. Thanks again!

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

    Re: Opening a ADO Recordset (A2k)

    Glad it helped. One of the biggest difficulties with ADO is that there are so many ways you *can* do things, that it's sometimes hard to determine which way is best for a given situation.
    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
  •