Test for # rows appended in VB

[Guest]

I have a problem which means I need to test for when a query appends more
than 0 rows - ie it matched the criteria successfully.
If after several queries have run appending to various history tables the
query did not append any rows then a different action is required, but I need
to trap the data that would normally display in the warning in the VB code to
test for this. Any ideas please?

Thanks in advance for any assistance

 

[Van T. Dinh]

If you use DAO code to run the Append Query, you can use the RecordsAffected
Property.

Check Access VB Help / DAO Help on the RecordsAffected Property.

 

[Guest]

Unfortunately my VB Help file returns an error when I try to query it on
RecordsAffected. I am on Access 2000.

Can you post any examples of how to use this code or what objects are
missing from my Library to get this error?

 

[Van T. Dinh]

Check your computer for the file DAO360.CHM. Double-click this file to
open it independently from Access (VB) Help.

 

[Guest]

I don't have that file on my PC, but some of these things are dealt with by
our Newtork guys

 

[Van T. Dinh]

OK. Here the example from Help

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****You need to get your computer fixed for Access Helpif you
want to get the most out of Access