Problem with records affected

[Guest]

I'm inserting into a database and need to know the number of records that
where entered and the number of errors, using this
With CurrentDb
.Execute strSQL_insert
If Line_Number = 80 Then
MsgBox (.RecordsAffected)
End If
If .RecordsAffected = 1 Then
Good_Records = Good_Records + 1
Else
Bad_Records = Bad_Records + 1
End If
End With
But the recordaffected comes out with 0.

Thanks

 

[Allen Browne]

Suggestions:
1. Use a Database variable, so you can set it to Nothing again at the end.

2. Use dbFailOnError so you can see if something goes wrong.

3. Assign RecordsAffected to a variable in case some other process is
involved.

This kind of thing:
Dim db As DAO.Database
Dim lngCount as Long
Set db = CurrentDb()
db.Execute strSql, dbFailOnError
lngCount = db.RecordsAffected
Set db = Nothing
If lngCount = 1 Then ...

 

[Pieter Wijnen]

Also if dealing with SQLServer (doesn't hurt anyhow)
db.Execute strSql, dbFailOnError + dbSeeChanges

Pieter

 

[Guest]

Sadly I now get a data type mismatch in the db.execute line.

 

[Allen Browne]

If you actually get the mismatch on the Dim db line, you need to set a
reference to the DAO library:
http://allenbrowne.com/ser-38.html

If you actually get it on the Execute line, something else is wrong. I've
assumed you have declared:
Dim strSql As String
and set strSql to a valid action query statement.