ACC: RecordsAffected Property Incorrect for SQL Pass-Through
ID: Q163703
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
When you use the RecordsAffected property of a Database or QueryDef object
to determine the number of records affected by the Execute method, the
value is incorrect with SQL pass-through queries.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
CAUSE
The RecordsAffected property is not set when you execute a pass-through
query.
STATUS
This behavior is by design.
MORE INFORMATION
When you view the RecordsAffected property, it reflects the number of
records affected by the most recent Execute method on an object that uses
the Microsoft Jet database engine. Pass-through queries bypass the Jet
database engine and interact directly with the back-end database through
the ODBC driver.
The following example shows a correct RecordsAffected value because the
Execute method operates on a table in the sample database Northwind.mdb:
- Start Microsoft Access and open the sample database Northwind.mdb.
- Create a module and type the following procedure:
Sub ViewRecs()
Dim db As DATABASE
' Substitute the correct path to Northwind.mdb on your computer.
Set db = DBEngine.Workspaces(0).OpenDatabase _
("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
db.Execute "Update employees set country = 'United States' " _
& "where country = 'USA';"
Msgbox db.RecordsAffected
End Sub
- To test this procedure, type the following line in the Debug window,
and then press ENTER:
ViewRecs
Note that a message box opens and displays the number of records
affected by the update. The number is 5 in an unaltered copy of
Northwind.mdb.
Steps to Reproduce Behavior
The following example uses an ODBC connection to the Pubs database in
Microsoft SQL Server. Substitute the correct parameters for your ODBC
database in the OpenDatabase method.
NOTE: If the number of records affected by the most recent Execute method
in your current instance of Microsoft Access happens to be 2, the
RecordsAffected property will return the correct number of records. Quit
and restart Microsoft Access to ensure that you reproduce the desired
results from this example.
- Start Microsoft Access and open the sample database Northwind.mdb.
- Create a module and type the following procedure:
Sub WrongNum()
Dim db As DATABASE
Dim SPTErr As Error
On Error GoTo WrongNum_err
' Substitute your own ODBC connection parameters.
Set db = OpenDatabase("", False, False, _
"ODBC;DSN=Pubs1;DATABASE=pubs;UID=sa;PWD=")
' Create a table in SQL Server and create a unique index.
db.Execute "create table testrecs (f1 int)", dbSQLPassThrough
db.Execute "create unique index idx on testrecs (f1)", _
dbSQLPassThrough
' Insert two records.
db.Execute "Insert into testrecs values(1)", dbSQLPassThrough
db.Execute "Insert into testrecs values(2)", dbSQLPassThrough
' This message box returns 0 records.
Msgbox db.RecordsAffected & " Records Affected."
' Delete the testrecs table.
db.Execute "drop table testrecs", dbSQLPassThrough
' This message box returns 0 records.
Msgbox db.RecordsAffected & " Records Affected."
Exit Sub
WrongNum_err:
For Each SPTErr In DBEngine.Errors
With SPTErr
Msgbox .Number & vbcr & .Description & vbcr & .Source
End With
Next SPTErr
End Sub
- To test this procedure, type the following line in the Debug window,
and then press ENTER.
WrongNum
Note that a message box opens twice and displays the number 0 instead
of the actual number of records affected, which is 2.
REFERENCES
For more information about the RecordsAffected property, search the Help
Index for "RecordsAffected property."
Additional query words:
Keywords : kbusage AccCon MdlQry QryPass
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbprb