Update Queries not running.

  • Thread starter Thread starter Shiller
  • Start date Start date
S

Shiller

Experts,

I created a function to run multiple update queries in my
application... it worked fine with 2 update queries, however it
stopped responding when I try running it with 4 update queries. Is
there a limitation on how many update queries an application may have:

Function RunUpdateQueries() As Boolean

On Error GoTo ErrorHandler

Dim strQuery As String

strQuery = "qryUpdateSUM1"
DoCmd.OpenQuery strQuery
DoCmd.Close

strQuery = "qryUpdateSUM2"
DoCmd.OpenQuery strQuery
DoCmd.Close

strQuery = "qryUpdateSUM3"
DoCmd.OpenQuery strQuery
DoCmd.Close

strQuery = "qryUpdateSUM4"
DoCmd.OpenQuery strQuery
DoCmd.Close

ExitHere:
Exit Function

ErrorHandler:

MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly +
vbCritical, "Microsoft Access Error"
RunUpdateQueries = False
Resume ExitHere

End Function
 
hi,
strQuery = "qryUpdateSUM1"
DoCmd.OpenQuery strQuery
DoCmd.Close
Click to expand...
Don't use DoCmd. Try this:

Dim db As DAO.Database

Set db = CurrentDb

db.Execute "qryUpdateSUM1"
db.Execute "qryUpdateSUM2"
...

Set db = Nothing


mfG
--> stefan <--
 
Use dbFailOnError, so you know if it succeeded or not.
Then test dbRecordsAffected, so you can tell if it did anything.

Example:
Dim db As DAO.Database

Set db = CurrentDb
db.Execute "qryUpdateSUM1", dbFailOnError
Debug.Print "qryUpdateSUM1 result: " & db.RecordsAffected

db.Execute "qryUpdateSUM2", dbFailOnError
Debug.Print "qryUpdateSUM2 result: " & db.RecordsAffected

...

Set db = Nothing
 
Back
Top