about Docmd.Runsql

[AL]

Hi,

I have a MS Access application which includes a module to insert records
into the tables. The following code:

Docmd.Runsql "iinsert into table A select * from table B....."

Docmd.Runsql "delete from table B....."


Sometimes (but not always), I found that some records are missing in table
B, I cannot find those records in table A too....Is it possible that the
delete statement commit before the insert statement?? If so, how to get rid
of this??

thanks in advance!

 

[Marshall Barton]

I have a MS Access application which includes a module to insert records
into the tables. The following code:

Docmd.Runsql "iinsert into table A select * from table B....."

Docmd.Runsql "delete from table B....."


Sometimes (but not always), I found that some records are missing in table
B, I cannot find those records in table A too....Is it possible that the
delete statement commit before the insert statement??

Yes, the RunSQL method runs the queries asynchronously so
you don't know which one will complete first.

You should be using the Execute method (check VBA Help for
Execute DAO). Here's some winging it air code:

Dim db As Database
Dim CopyRecs As Long
Dim DelRecs As Long
Set db = CurrentDb()
On Error GoTo HandleError
db.Execute "INSERT INTO . . .", dbFailOnError
CopyRecs = db.RecordsAffected
db.Execute "DELETE * FROM . . ."
DelRecs = db.RecordsAffected
MsgBox CopyRecs & " records were copied and" & _
vbCrLf & DelRecs & " were deleted"
AllDone:
Exit Sub
HandleError:
Select Case Err.Number
Case ???
MsgBox "Unable to copy some/all records"
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume AllDone
End Sub

 

[AL]

Thanks Marshall, I will try the execute method.

If I need to pirnt a report in between, can I use docmd.openReport ?? That is,

db.Execute "insert into table B select * from table A..."

dbcmd.openReport "..." (print a report from table B)

db Execute "delete ....."


Thanks in advance!

 

[Marshall Barton]

Yes, since the Execute method is synchronous, you can rely
on one completing before the next statement starts. The
Delete query is not connected to the other actions so that
will have no effect on earlier statements.