Hi All,
I'm having a little pickle with some queries;
I have a few queries (11) in an Access front end, activated from a button on a form, these worked fine with an Access BE. They are basically importing new items from linked tables, updating some fields, nothing too major.
Having moved the BE data across to SQL the queries fail, I can only guess it's the way SQL handles them, because if I step through them one at a time it all works fine. :rolleyes:
Is there a command I could use like a wait or refresh command that might work in this situation?

Thanks
Matt

Have tried the use of DoEvents between the queries, although this doesn't seem to be doing the job...

I think there's more to it than what you're telling us. For one thing, I don't know what specific error you are getting with the queries or if there's no error message, at least a description of behavior (e.g. form is locked and nonupdatable).

Maybe if you elaborate a bit more what actually happened, we can offer suggestions.

I think there's more to it than what you're telling us. For one thing, I don't know what specific error you are getting with the queries or if there's no error message, at least a description of behavior (e.g. form is locked and nonupdatable).

Maybe if you elaborate a bit more what actually happened, we can offer suggestions.

I agree and would suggest that in addition to the Error codes, messages, description of the failures that are suggested here by Banana, posting the SQL Code and the Table Structures would also be a good place to start.

Apologies for not being more detailed. I didn't post the code because it's just a list of queries, but here it is anyway, and this runs fine with an Access 2003 BE. But moving to SQL Express it doesn't seem to handle them in the same way.
Private Sub cmdAdd_LL_Click()
DoCmd.SetWarnings False

If IsNull([Co Name]) Then
MsgBox "Please resolve 'Unknown' before creating new Case File", vbOKOnly, "Invalid Data"
GoTo Add_Exit
End If

DoCmd.OpenQuery "qryLP-Add-LL-Case"
DoEvents
DoCmd.OpenQuery "qryLP-Update-Folio"
DoCmd.OpenQuery "qryLP-Update-LLCaseId"
DoCmd.OpenQuery "qryLP-Case-Created-Note"
DoCmd.OpenQuery "qryLP-Add-LL-Client"
DoCmd.OpenQuery "qryLP-Add-LL-Notes"
DoCmd.OpenQuery "qryLP-Add-LL-GI"
DoCmd.OpenQuery "qryLP-Add-LL-FS"
DoCmd.OpenQuery "qryLP-Add-LL-Mtg"
DoCmd.OpenQuery "qryLP-Add-LL-Mtg-Fin"
DoCmd.OpenQuery "qryLP-Create-Dep"
DoCmd.SetWarnings True
MsgBox "A new case has been created for " & [Mail Name] & " using Case Id - " & [CaseID], vbOKOnly, "Import"
Forms![frmLP-View-Unmatched].Requery
Forms![frmLP-View-Unmatched].Refresh
Add_Exit:
End Sub

If you step through each query it runs ok with no errors, but leaving it to run automatically it runs through with no errors, but doesn't complete some of the queries. (The 2nd and 3rd queries are updates using a Case Id created in query 1.)

A bit more coding but you might be better off if you copied the sql from the first query into vba and use the .Execute dbFailOnError method.

Also by using the DoEvents this renders Access to the command completion.

David

I can only apologise for being so completly stupid. When the tables were exported into SQL one of our major lookup tables failed to move across, so that was it, I copied the table data and ran the above again and all is well.
I hope others might check this first before posting in haste on here...