Updating a recordset from another recordset

[XLEAccessGuru]

Hello all,

Need some help. I have TABLE A that contains a set of records with the TICKET # as the primary key. I have an identical table - TABLE B - in another DB in which I need to update each ticket with new values from each record with the same TICKET number in TABLE A. I am unable to use an update query or a hard-coded VBA SQL string because there is a memo field in each record that is being truncated when it's updated. I'd like to use DAO recordsets to perform these updates, but I'm somewhat unfamiliar how to use multiple recordsets and update one with the values of another one record at a time. Any ideas?

Any help is appreciated!

 

[WayneRyan]

XLE,

Without going into specifics like why not use a "linked table", etc.

Hope this gets you started:

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset

Set rst1 = "Select * From LocalDbTable"

While Not rst1.EOF And Not rst1.BOF
   Set rst2 = "Select * " & _
              "From   TheOtherTable In 'C:\SomeDB.MDB' " & _
              "Where  TicketNumber = " & rst1!TicketNumber
   rst2.Edit
   rst2.SomeField = rst1.SomeField
   rst2.SomeOtherField = rst2.SomeOtherField
   rst2.Update
   rst2.Close
   rst1.MoveNext
   Wend

Wayne

 

[XLEAccessGuru]

Thanks WayneRyan! To answer your question, I have 2 dbs that need to update each other on each coast of the US (two different network drives). I have been at this for several days writing vba sql statements, update queries and several other tried and true methods to update my tables in Tacoma with current data from my location and vice versa. However, whatever method I tried, the Tacoma table keeps returning an error that says "You and another user are attempting to edit the same data at the same time". Hours of debugging, checking properties, indexes and links, repairing, manipulating, testing, and so on, have turned up absolutely no reason why the Tacoma table should be returning that error when the updates run. And it only happens on the Tacoma side, not mine - and the tables have identical structure. I have literally tried everything, except this method, to find out why it returns that error and there is no reason for it. No one is editing data because I've even kicked all my users out of the Tacoma database entirely before and while running the update. Seems to be one of those mysterious Access gremlins causing trouble.

SOOO I figured I'd take one last shot and try to update one record at a time using the VBA recordset with loop method. If it doesn't work, it's time to take a bow and admit defeat... unless, that is, someone out there has had this same problem and found a solution.

Anyone???

Thanks again Wayne. I do appreciate your help as I'm not overly famililar with using recordsets just yet. I'm praying that this works!!! I'll keep you posted.