Updating a record using DAO

  • Thread starter Thread starter David C. Holley
  • Start date Start date
D

David C. Holley

Below are the key lines of code in a procedure that I've written. With
out going into detail, the question at hand that I have is wether or not
its possible to use the .EDIT and .UPDATE methods of a recordset that
was derived from a QueryDef. I'm using the QueryDef to select records to
be imported and then once imported, need to update a field in the source
table. The records being imported are one the many-side of a one to many
relationship. As such, for each record on the one-side that's import, I
need to select the corresponding records on the many-side for update.

Will have to do this the nasy way and loop through the many-side table
looking for the records to update using a RecordSet object on the table
itself bypassing the query?

Set sourceQDF_ledger =
CurrentDb.QueryDefs("qrySelectLedgerEntriesForImport")
sourceQDF_ledger.Parameters(0) =
sourceRS_transports.Fields("lngTransportId")
Set sourceRS_ledger = sourceQDF_ledger.OpenRecordset(dbOpenForwardOnly)
sourceRS_ledger.Edit
sourceRS_ledger.Fields("dblImportBatchNumber") = dblBatchId
sourceRS_ledger.Update
sourceRS_ledger.MoveNext

David H
 
You might be able to avoid the loop to update each record by building the
SQL statement dynamically.

The end result will be something like this:
Dim strSQL as String
strSQL = "INSERT INTO Table1 (F1, F2, F3) SELECT " & dblBatchID & " AS
F1, ...
dbEngine(0)(0).Execute strSQL, dbFailOnError

Presumably you can open qrySelectLedgerEntriesForImport, and switch it to
SQL View (View menu, from query design) to see an example of the kind of
string you need to create.
 
Actually, I went with an UPDATE query in place of the .AddNew/.Update
bit. Not the way I wanted it to work, but it works nonetheless.

David H
 
Back
Top