Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    I have an ADODB recordset populated by a process in a VB6 subroutine and need to compare those values to a table in a MS Access 2002 database. I don't think Access has stored procedures, but the data set it small enough that I can just create a query in Access to compare the data and return the results. I'm trying to speed up the processing and was wondering if there is a convenient way to pass the recordset I created in code to the database without creating a looping INSERT INTO statement?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Persisting an ADODB recordset to Access Databa

    There might be a bulk-add ShoveItInAllAtOnce() method or tool out there (comparable to DTS for MS SQL Server), but if you need to compare the data, a loop seems better.

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

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    Access definitely does not have stored procedures, and I don't really understand if you want to compare data or insert data or both. Are you trying to compare in VB or Access and is the data stored anywhere other than the recordset?
    Charlotte

  4. #4
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    Hi Charlotte,

    I'm using VB to mine data from user objects in Active Directory and I'm storing the results in an adodb.recordset. I then need to compare the data to a database table in Access 2K2; specifically, I need to generate a resulting list that shows the people in the recordset that are not in the database.

    Mike

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

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    Then I still don't understand why you would be passing anything to the database, assuming you mean the Access database. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  6. #6
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    Charlotte, I was looking for a more efficient way to compare the recordsets - and it is entirely possible I am not thinking along the right lines. But I have code that spins through Active Directory containers, grabs attributes, and assembles this into a recordset in memory. I then want to take that recordset and compare it to data residing in an Access DB table with the goal of eliminating any records that exist in one but not the other. The way I currently have it structured is I take the recordset I built in code, pull in another recordset from the data in the database, then do a outter loop/sub loop thing to compare this data. If there is a match I remove the entry from the recordset in the outter loop (I think I'm making this sound a lot more complicated than it really is).

    So for each record in the recordset in the outter loop, I have to spin through each record in the inner loop (data from the database) to do this comparison. This results in about 17,000 iterations through these elements. If I could load this recordset I created in memory into the database where I'm pulling the other data for the compare, I could do all this with SQL rather rapidly and I'm guessing more efficiently.

    I am also open to other methods if you have a better way to accomplish what I'm trying to do. This seems so simple and basic but I realize I've never encountered having to do this in a business process before. I guess I've been lucky that all the data I've been working with to date has always been in a DB of some type from the start.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    If there is a unique identifier for each record in the ADODB recordset and the Access 2002 table, and they are consistent, then you could use SQL outer joins to find the unmatched records in each set and then edit the data accordingly. Of course you would want indexes on the indentifiers as well, but SQL processing is much faster than the outer/inner loop process you described.
    Wendell

  8. #8
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    I suspected it would be more efficient. But both recordsets have to be in the database before I can run an SQL query, am I right?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    Now that I think about it, yes you would need to do an insert into the Access database - which would cause it to bloat over time. The other option would be to open two recordsets but sort them on the unique identifier, and then step through them - but are you comparing all the fields, or just the identifier? If you are comparing all the fields, it does get to be a rather tedious process.
    Wendell

  10. #10
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persisting an ADODB recordset to Access Database (Access 2K2, VB6)

    Yes, both recordsets have a unique identifier that I am using.

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Persisting an ADODB recordset to Access Databa

    I don't know whether this would be efficient, but let's say that the AD recordset always is a superset of the Access recordset. If you created a collection keyed on the unique ID of all records in the AD recordset, and then deleted all members of the collection using the unique ID from the Access recordset, you should be left with a collection that contains only the non-duplicate elements.

    (I can't test this as I have a lunch appointment, but I hope it works.)

Posting Permissions

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