Hi,
Therefore, always use the dbFailOnError option when using the Execute
method to run an update or delete query. This option generates a run-time
error and rolls back all successful changes if any of the records affected
are locked and can't be updated or deleted.
Try (source VBA help topic):
==============================
RecordsAffected Property Example
This example uses the RecordsAffected property with action queries executed
from a Database object and from a QueryDef object. The
RecordsAffectedOutput function is required for this procedure to run.
Sub RecordsAffectedX()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim strSQLChange As String
Dim strSQLRestore As String
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
With dbsNorthwind
' Print report of contents of the Employees
' table.
Debug.Print _
"Number of records in Employees table: " & _
.TableDefs!Employees.RecordCount
RecordsAffectedOutput dbsNorthwind
' Define and execute an action query.
strSQLChange = "UPDATE Employees " & _
"SET Country = 'United States' " & _
"WHERE Country = 'USA'"
.Execute strSQLChange
' Print report of contents of the Employees
' table.
Debug.Print _
"RecordsAffected after executing query " & _
"from Database: " & .RecordsAffected
RecordsAffectedOutput dbsNorthwind
' Define and run another action query.
strSQLRestore = "UPDATE Employees " & _
"SET Country = 'USA' " & _
"WHERE Country = 'United States'"
Set qdfTemp = .CreateQueryDef("", strSQLRestore)
qdfTemp.Execute
' Print report of contents of the Employees
' table.
Debug.Print _
"RecordsAffected after executing query " & _
"from QueryDef: " & qdfTemp.RecordsAffected
RecordsAffectedOutput dbsNorthwind
.Close
End With
End Sub
Function RecordsAffectedOutput(dbsNorthwind As Database)
Dim rstEmployees As Recordset
' Open a Recordset object from the Employees table.
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")
With rstEmployees
' Enumerate Recordset.
.MoveFirst
Do While Not .EOF
Debug.Print " " & !LastName & ", " & !Country
.MoveNext
Loop
.Close
End With
End Function
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <
http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
This posting is provided "AS IS" with no warranties, and confers no rights
--------------------
| Date: Tue, 27 Apr 2004 16:47:34 -0400
| From: zippy <
[email protected]>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4)
Gecko/20030624 Netscape/7.1 (ax)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Problem Genetating Errors
| Content-Type: text/plain; charset=us-ascii; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <
[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: adsl-218-10-102.mia.bellsouth.net 68.218.10.102
| Lines: 1
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.queries:198767
| X-Tomcat-NG: microsoft.public.access.queries
|
| Below is some code for doing and insert into one of my tables. The
| inserts do not work because a duplicate key exists, which I want to
| happen. The problem is, I cannot get access to return an error for this
| condition.
|
| What can I do to get access to return an error on my query?
|
|
| Thanks
|
| -------------------------------
| CODE
| -------------------------------
|
| On Error GoTo sql_error_err
|
| Set Mydb = CurrentDb
| Mydb.Execute sql, dbFailOnError
|
|
|
| sql_error_exit:
| Exit Sub
|
| sql_error_err:
| MsgBox Err.Number
| MsgBox Err.Description
| Debug.Print Err.Description
| Debug.Print Err.Description
| Debug.Print Err.Source
| Resume sql_error_exit
|
|
