RecordsAffected is zero when it should not be.

[kentdahlgren]

Hi,

I have a rather advanced MS Access 2002 solution where I use the RecordsAffected property of the Execute method for a Querydef. I’m using it all over the solution and it has worked as expected for years. Suddenly it does not in parts of the code, since it returns zero even when it should not. In the example below the record is there in table as it should after the execute and I expect RecordsAffected to be one. It is zero. Can anyone explain why this could happen? Is there a work around (other than do a select query that checks that the expected record is there)?

qryInsert.Execute
If qryInsert.RecordsAffected = 0 Then
MsgBox "Failed to insert the person!"
Exit Sub
End If

Thanks!

[ADezii]

Hi,

I have a rather advanced MS Access 2002 solution where I use the RecordsAffected property of the Execute method for a Querydef. I’m using it all over the solution and it has worked as expected for years. Suddenly it does not in parts of the code, since it returns zero even when it should not. In the example below the record is there in table as it should after the execute and I expect RecordsAffected to be one. It is zero. Can anyone explain why this could happen? Is there a work around (other than do a select query that checks that the expected record is there)?

qryInsert.Execute
If qryInsert.RecordsAffected = 0 Then
MsgBox "Failed to insert the person!"
Exit Sub
End If

Thanks!

This is a rather strange result. Are you using the correct Syntax?

Dim strSQL As String, qdfTemp As DAO.QueryDef
 
strSQL = "<SQL Statement here>;"
 
'Create a Tempory Query
Set qdfTemp = CurrentDb.CreateQueryDef("", strSQL)
 
qdfTemp.Execute
 
Debug.Print "Records Affected after Executing Query from QueryDef: " & qdfTemp.RecordsAffected

[ADezii]

Hi,

I have a rather advanced MS Access 2002 solution where I use the RecordsAffected property of the Execute method for a Querydef. I’m using it all over the solution and it has worked as expected for years. Suddenly it does not in parts of the code, since it returns zero even when it should not. In the example below the record is there in table as it should after the execute and I expect RecordsAffected to be one. It is zero. Can anyone explain why this could happen? Is there a work around (other than do a select query that checks that the expected record is there)?

qryInsert.Execute
If qryInsert.RecordsAffected = 0 Then
MsgBox "Failed to insert the person!"
Exit Sub
End If

Thanks!

As an afterthought, does the following code work in your situation?

Dim lngRecsPreAppend As Long, lngRecsPostAppend As Long
Dim lngRecordsAffected As Long
 
lngRecsPreAppend = DCount("*", "<Your Table Name>")
  'perform Action (APPEND) Query here
lngRecsPostAppend = DCount("*", "<Your Table Name>")
 
lngRecordsAffected = (lngRecsPostAppend - lngRecsPreAppend)
Debug.Print "Records Affected by Append Query: " & lngRecordsAffected

[kentdahlgren]

Hi Adezii,

Thanks for your suggestions. I don’t there is a syntax or program error I my code, but a bug in MS Access. The reason I think that is that the code has worked for years and I have not changed this procedure neither the query involved.

Here is the code for this procedure, with some not relevant parts removed (marked with …)

If you think my naming are strange that because I’m from Sweden.

Private Sub cmdAnmäl_Click()
Dim datKursDeltAnmDatum As Date
Dim strKursDeltFritext As String
Dim dbDenna As Database
Dim qryInsertKursDeltagare As QueryDef
Dim qryKursDeltagare As QueryDef
Dim datKursDeltRegTid As Date
Dim blnVäntlista As Boolean
….
Set dbDenna = DBEngine.Workspaces(0).Databases(0)

Set qryInsertKursDeltagare = dbDenna.QueryDefs("qryInsertKursDeltagare")
……
qryInsertKursDeltagare.Parameters("lngPersonId") = lngPersonID
qryInsertKursDeltagare.Parameters("lngKursId") = lngKursid
qryInsertKursDeltagare.Parameters("strKursDeltFrit ext") = strKursDeltFritext
qryInsertKursDeltagare.Parameters("datKursDeltAnmD atum") = datKursDeltAnmDatum
qryInsertKursDeltagare.Parameters("datKursDeltRegT id") = datKursDeltRegTid
qryInsertKursDeltagare.Parameters("blnKursVäntlist a") = blnVäntlista
qryInsertKursDeltagare.Parameters("strAnledning") = strAnledning
qryInsertKursDeltagare.Parameters("strOCR") = strOCR

qryInsertKursDeltagare.Execute
' RecordsAffected returns zero even when a record was inserted into the table.

Debug.Print qryInsertKursDeltagare.RecordsAffected
If qryInsertKursDeltagare.RecordsAffected = 0 Then
MsgBox "(1) Det gick inte att registrera!", vbExclamation, strFöreningsnamn
Exit Sub
Else
End If
……
End Sub

Best regards

Kent Dahlgren

[ADezii]

Hi Adezii,

Thanks for your suggestions. I don’t there is a syntax or program error I my code, but a bug in MS Access. The reason I think that is that the code has worked for years and I have not changed this procedure neither the query involved.

Here is the code for this procedure, with some not relevant parts removed (marked with …)

If you think my naming are strange that because I’m from Sweden.

Private Sub cmdAnmäl_Click()
Dim datKursDeltAnmDatum As Date
Dim strKursDeltFritext As String
Dim dbDenna As Database
Dim qryInsertKursDeltagare As QueryDef
Dim qryKursDeltagare As QueryDef
Dim datKursDeltRegTid As Date
Dim blnVäntlista As Boolean
….
Set dbDenna = DBEngine.Workspaces(0).Databases(0)

Set qryInsertKursDeltagare = dbDenna.QueryDefs("qryInsertKursDeltagare")
……
qryInsertKursDeltagare.Parameters("lngPersonId") = lngPersonID
qryInsertKursDeltagare.Parameters("lngKursId") = lngKursid
qryInsertKursDeltagare.Parameters("strKursDeltFrit ext") = strKursDeltFritext
qryInsertKursDeltagare.Parameters("datKursDeltAnmD atum") = datKursDeltAnmDatum
qryInsertKursDeltagare.Parameters("datKursDeltRegT id") = datKursDeltRegTid
qryInsertKursDeltagare.Parameters("blnKursVäntlist a") = blnVäntlista
qryInsertKursDeltagare.Parameters("strAnledning") = strAnledning
qryInsertKursDeltagare.Parameters("strOCR") = strOCR

qryInsertKursDeltagare.Execute
' RecordsAffected returns zero even when a record was inserted into the table.

Debug.Print qryInsertKursDeltagare.RecordsAffected
If qryInsertKursDeltagare.RecordsAffected = 0 Then
MsgBox "(1) Det gick inte att registrera!", vbExclamation, strFöreningsnamn
Exit Sub
Else
End If
……
End Sub

Best regards

Kent Dahlgren

Just for curiosity, why don't you try the simple work-a-around that I had previously listed. If it works, you know it is some kind of problem related to the RecordsAffected Property, if not, it's an Access related issue. If you do decide to test it, please let me know the outcome - you got my curiosity.

Dim lngRecsPreAppend As Long, lngRecsPostAppend As Long
Dim lngRecordsAffected As Long
 
lngRecsPreAppend = DCount("*", "<Your Table Name>")
  qryInsertKursDeltagare.Execute
lngRecsPostAppend = DCount("*", "<Your Table Name>")
 
lngRecordsAffected = (lngRecsPostAppend - lngRecsPreAppend)
 
If lngRecordsAffected = 0 Then
  MsgBox "(1) Det gick inte att registrera!", vbExclamation, strFöreningsnamn
    Exit Sub
Else
End If

[kentdahlgren]

Hi,

I've found the error! One of tables had a field that was incorrect defind. Is was defined to be numeric instead of text. Strange anyway that the record was inserted even though RecordsAffected was zero.

To explain this further, I have tables and code in different files and the tables linked to the program. One of my users had defined a field incorrect and the error occured when I executed the program on a copy of this tables.

Thanks!

Kent

[ADezii]

Hi,

I've found the error! One of tables had a field that was incorrect defind. Is was defined to be numeric instead of text. Strange anyway that the record was inserted even though RecordsAffected was zero.

To explain this further, I have tables and code in different files and the tables linked to the program. One of my users had defined a field incorrect and the error occured when I executed the program on a copy of this tables.

Thanks!

Kent

Glad 'you' figured it out because it was driving me crazy. (LOL)!