RE: Append query won't complete
Tom Wickerath
You haven't given us a lot to go on (the SQL statement would be helpful).
How are you running your append query--through the user interface or using
VBA code? If you are running it using the user interface, and you have Set
Warnings set to False, then you will not receive any message indicating an
error. This can easily happen if you used a macro with an action that set
warnings to false, followed by another action to run the query if the query
failed. That's one of the big reasons that you should avoid using
macros--once the macro errors on a line, it will never finish out with an
action that turns warnings back on.
If you were running your action query via VBA code, did you include the
optional dbFailOnError parameter? If not, the query can silently fail. Here
are (5) different methods of running an action query in code. Note that the
first two methods involve flawed logic:
' -----------Begin Code---------------------
Option Compare Database
Option Explicit
Sub Test1()
' Will produce a warning if the user's setting, under Tools > Options
' on the Edit/Find tab includes confirming Action Queries
DoCmd.RunSQL "UPDATE tblEmployees SET tblEmployees.City = ""Renton"""
End Sub
Sub Test2()
' Includes turning off warnings and then immediately turning warnings
' back on. Note: Warnings will not be turned on if the DoCmd.RunSQL
' statement fails for any reason, such as a table being renamed!
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblEmployees SET tblEmployees.City = ""Seattle"""
DoCmd.SetWarnings True
End Sub
Sub Test3()
' Same as above, but warnings will always be turned back on in the error
' handling code. This is an example of how error handling procedures can
' help prevent problems.
On Error GoTo ProcError
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblEmployees SET tblEmployees.City = ""Tukwila"""
DoCmd.SetWarnings True
' Additional code can be inserted here after re-enabling warnings
ExitProc:
DoCmd.SetWarnings True
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
vbInformation, "Error in Test3 Procedure..."
Resume ExitProc
End Sub
Sub Test4()
' This method uses DAO. It requires a reference set to the
' Microsoft DAO 3.x Object Library (Access 97 --> 3.51, Access 2000+ --> 3.6).
On Error GoTo ProcError
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "UPDATE tblEmployees SET tblEmployees.City = ""Lynnwood"""
db.Execute strSQL, dbFailOnError
ExitProc:
' Cleanup
On Error Resume Next
db.Close
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
vbInformation, "Error in Test4 Procedure..."
Resume ExitProc
End Sub
Sub Test5()
' This method uses ADO. It requires a reference set to the Microsoft ActiveX
' Data Objects 2.x Library (2.1 is the default library for Access 2000+).
On Error GoTo ProcError
Dim strSQL As String
Dim lngRecordsAffected As Long
strSQL = "UPDATE tblEmployees SET tblEmployees.City = ""Bellevue"""
CurrentProject.Connection.Execute strSQL, lngRecordsAffected, adCmdText
MsgBox lngRecordsAffected & " records were successfully updated.",
vbInformation
ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description, _
vbInformation, "Error in Test5 Procedure..."
Resume ExitProc
End Sub
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
"TomD" wrote:
I'm attempting to run a simple append query, adding fields from one table to
another. The query status bar (I'm not sure if that's the right term, I mean
the row of little blue boxes in the lower left corner of the screen) quickly
fills to the right, but then Access becomes unresponsive. The query never
finishes and I have to close Access with the Task Manager. Can anyone tell me
what might be causing the problem?
Thanks a lot,
TomD