Problem with records affected

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Markm-s - 13 Sep 2007 16:18 GMT

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

Reply to this Message

Allen Browne - 13 Sep 2007 16:24 GMT

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 ...

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm inserting into a database and need to know the number of records that
> where entered and the number of errors, using this
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

Pieter Wijnen - 13 Sep 2007 16:45 GMT

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

Pieter

> Suggestions:
> 1. Use a Database variable, so you can set it to Nothing again at the end.
[quoted text clipped - 29 lines]
>>
>> Thanks

Reply to this Message

Markm-s - 13 Sep 2007 16:50 GMT

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

> Suggestions:
> 1. Use a Database variable, so you can set it to Nothing again at the end.
[quoted text clipped - 29 lines]
> >
> > Thanks

Reply to this Message

Allen Browne - 13 Sep 2007 17:28 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Sadly I now get a data type mismatch in the db.execute line.
>
[quoted text clipped - 31 lines]
>> > End With
>> > But the recordaffected comes out with 0.

Reply to this Message