DoCmd.RunSQL delay problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an module processes to inport records from a text file. If the record
passes certain criteria the module adds the record to a master table. The
last criteria is they cannot already be in the master table. I have keys
setup and all but the criterias are more intense then just a key.

So just before I do an install I do a DCOUNT to see if there is an existing
record. If the count is 0 then I run a DoCmd.RunSQL to insert the new record.

Everything works fine until the network is slow and the same person is in
the import file twice.
The first DoCmd Insert runs and inserts the record.
But then the next record still returns a count of 0 even thought a record
was just added. So it adds a second master record.

Is there a way to put a transaction around a DoCmd?
Or to force the table update to complete before I loop back for a next imput
record?

Would I be better off to use the CurrentDB.Execute instead of the
DoCmd.RunSQL?

Thanks,
Randy
 
Randy said:
I have an module processes to inport records from a text file. If the record
passes certain criteria the module adds the record to a master table. The
last criteria is they cannot already be in the master table. I have keys
setup and all but the criterias are more intense then just a key.

So just before I do an install I do a DCOUNT to see if there is an existing
record. If the count is 0 then I run a DoCmd.RunSQL to insert the new record.

Everything works fine until the network is slow and the same person is in
the import file twice.
The first DoCmd Insert runs and inserts the record.
But then the next record still returns a count of 0 even thought a record
was just added. So it adds a second master record.

Is there a way to put a transaction around a DoCmd?
Or to force the table update to complete before I loop back for a next imput
record?

Would I be better off to use the CurrentDB.Execute instead of the
DoCmd.RunSQL?
Click to expand...


Check Help on the Execute method. It provides more control
and is syncronous, where as RunSQL is asynchronous. OTOH,
Execute will not automatically resolve query parameters.
 
Try and add
DoEvents
After each command that you want to finish before moving to the next stage
 
Try to change your append query to be an update
query instead. Join the source table to the destination
table on the key value, and update the destination
table if the key value is null in the destination
table:

UPDATE destination LEFT JOIN source
ON destination.ID = source.ID
SET destination.CompanyName = source.companyname,
....
destination.fld20 = source.fid20
WHERE (source.ID)=" & idx & ")
AND (destination.ID Is Null);

(david)

The update does an append when the record is missing,
this sample does nothing unless the record is missing.
 
Back
Top