Why RecordsAffected returns 0 for this query

K

KitaYama

Well-known member
Local time
Tomorrow, 07:14
Joined
Jan 6, 2022
Messages
2,131
I'm executing this sql.
But AffectedRecords returns 0.
The record is successfully added to the table.

Why is that?

Thanks.

Code: 
sql = "INSERT INTO tblManuProcess ("
sql = sql & "ManuProcessManuFK, ManuProcessProcessFK, ManuProcessUserFK, "
sql = sql & "ManuProcessDate, ManuProcessCount, ManuProcessInspector"
sql = sql & ") VALUES ("
sql = sql & "P1,P2,P3,P4,P5,P6"
sql = sql & ")"

With CurrentDb.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    .Close
    If CurrentDb.RecordsAffected > 0 Then
       fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
     Else
        MsgBox "Failed"
        Exit Sub
     End If
End With
 
Last edited:
Sort by date Sort by votes
Million thanks to all who participated.
Since the correct code was a mix of all suggested advices, Unfortunately I can't accept only one of the replies as the correct answer.
Sorry to leave the thread as Open.

Thanks again.
Case solved.
 
Upvote 0 Downvote
KitaYama said:
Million thanks to all who participated.
Since the correct code was a mix of all suggested advices, Unfortunately I can't accept only one of the replies as the correct answer.
Sorry to leave the thread as Open.

Thanks again.
Case solved.
Click to expand...
Would you mind posting the final code? Thanks!
 
Upvote 0 Downvote
theDBguy said:
Would you mind posting the final code? Thanks!
Click to expand...
Sure:
Seems that having .Close or removing it has no effect, I preferred to keep it.
It can be removed anyway.

Code: 
sql = "INSERT INTO tblManuProcess ("
sql = sql & "ManuProcessManuFK, ManuProcessProcessFK, ManuProcessUserFK, "
sql = sql & "ManuProcessDate, ManuProcessCount, ManuProcessInspector"
sql = sql & ") VALUES ("
sql = sql & "P1,P2,P3,P4,P5,P6"
sql = sql & ")"
    
With CurrentDb.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    .Close
    If .RecordsAffected > 0 Then
        fltr = fltr & "," & CurrentDb.OpenRecordset("select @@identity")(0)
    Else
        MsgBox "Failed"
        Exit Sub
    End If
End With
 
Upvote 0 Downvote
KitaYama said:
Sure:
Seems that having .Close or removing it has no effect, I preferred to keep it.
It can be removed anyway.

Code: 
sql = "INSERT INTO tblManuProcess ("
sql = sql & "ManuProcessManuFK, ManuProcessProcessFK, ManuProcessUserFK, "
sql = sql & "ManuProcessDate, ManuProcessCount, ManuProcessInspector"
sql = sql & ") VALUES ("
sql = sql & "P1,P2,P3,P4,P5,P6"
sql = sql & ")"
    
With CurrentDb.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    .Close
    If .RecordsAffected > 0 Then
        fltr = fltr & "," & CurrentDb.OpenRecordset("select @@identity")(0)
    Else
        MsgBox "Failed"
        Exit Sub
    End If
End With
Click to expand...
Thanks. If this is for a multi user database, I wonder if the OpenRecordset line will always do what you're expecting it to do.
 
Upvote 0 Downvote
theDBguy said:
Thanks. If this is for a multi user database, I wonder if the OpenRecordset line will always do what you're expecting it to do.
Click to expand...
Yes, it's for a FE to be used on a multi user database.
I was wondering to go for this method or do a DMax for the PK of the target table.
I was doing some test on both method.

Thank you.
 
Upvote 0 Downvote
KitaYama said:
Yes, it's for a FE to be used on a multi user database.
I was wondering to go for this method or do a DMax for the PK of the target table.
I was doing some test on both method.

Thank you.
Click to expand...
Going to bed now; but if you're interested, I can try to post a modified code tomorrow.

Sent from phone...
 
Upvote 0 Downvote
Code: 
Set db = CurrentDb      ' only one reference
With db.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    ' ...
    .Execute dbFailOnError
    'Debug.Print .RecordsAffected
    If .RecordsAffected > 0 Then
        fltr = fltr & "," & db.OpenRecordset("select @@identity")(0)
    Else
        MsgBox "Failed"
        Exit Sub
    End If
End With
RecordsAffected is available after the Execute. If necessary, you explicitly use the QueryDef object via an object variable.

Openrecordset on the append query will not work. Here you should orientate yourself on the db reference. CurrentDb causes a new instantiation of the current database. Therefore, one should use an instance fixed in a variable.
 
Last edited:
Upvote 0 Downvote
your code on Post #1 will always return 1 (if it succeed, or 0 in case you have index on the same fields with no duplicate).
instead of using Querydef you can simply use the Recordset of the table.

or you can just count the records prior to appending and count it again after:

Dim rec_count as long
rec_count = dcount("1", "tblManuProcess")
' do you append/insert query
debug.print dcount("1", "tblManuProcess") - rec_count
 
Upvote 0 Downvote
I would just use a variable to grab the RecordsAffected. Something like:
Code: 
' ...
Dim lAffected As Long
' ...
With CurrentDb
  With .CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    lAffected = .RecordsAffected
  End With
  If lAffected > 0 Then
    With .OpenRecordset("select @@identity")
      fltr = fltr & "," & .Fields(0)
      .Close
    End With
  Else
    MsgBox "Failed"
    Exit Sub
  End If
End With
This way you don't have to bother with object variables and remembering to clean them up after yourself.

Many ways to skin a cat! (y)
 
Upvote 0 Downvote
I think my approach to this would have been to avoid the query def entirely and instead use string concatenation to build an SQL string. I don't see any difference between that and a parametrized .CreateQueryDef, particularly since by omitting the name, you don't actually STORE the query you just defined anyway. Then create a DAO variable for the database. Use a DB.EXECUTE SQL, dbFailOnError, after which test DB.RecordsAffected. That approach has never yet failed me with regard to .RecordsAffected. Of course, I DO have to build the query string correctly, but that isn't very hard anyway, and it certainly is not a harder approach than building a querydef that you didn't want to save.
 
Upvote 0 Downvote
OK, I just tested my code in Post #31 and it worked fine without error.

(It is slightly different from previously posted code in earlier posts - are you sure you tested with that specific code?)
 
Upvote 0 Downvote
The_Doc_Man said:
I don't see any difference between that and a parametrized .CreateQueryDef
Click to expand...
The main benefit is that you don't have to worry about correctly delimiting your inputs - you can pass them directly from controls on a form and forget about quoting strings and octothorpes for dates.

It's a little used trick. I think @Minty or someone else here is a big fan (forgive me if I've mis-remembered who it is)
 
Upvote 0 Downvote
cheekybuddha said:
OK, I just tested my code in Post #31 and it worked fine without error.

(It is slightly different from previously posted code in earlier posts - are you sure you tested with that specific code?)
Click to expand...
Yes. I tested both your code in post #31 and also various earlier versions as a check. All gave the same error if I omitted the prefix before OpenRecordset
I'm using A365. What version dd you use for testing?

Similar to @The_Doc_Man, I rarely use this approach preferring to build SQL with appropriate delimiters rather than using a temp query def.
I understand the benefits but its not how I prefer to code.
For that reason, I wanted to be absolutely sure I hadn't made any mistakes when the error was triggered.
 
Upvote 0 Downvote
isladogs said:
I'm using A365. What version dd you use for testing?
Click to expand...
I used A2K7.

With the following code:
Code: 
Function QDefInsert() As Boolean

  Dim sql As String
  Dim lAffected As Long
 
  sql = "INSERT INTO tblChapters (BookFK, ChapterNo, ChapterTitle,TestDate) VALUES (P1, P2, P3, P4);"
  With CurrentDb
    With .CreateQueryDef("", sql)
      .Parameters("P1") = 1
      .Parameters("P2") = 13
      .Parameters("P3") = "Thirteenth"
      .Parameters("P4") = Date
'      .Parameters("P5") = txtManuCount
'      .Parameters("P6") = cmbInspector
      .Execute dbFailOnError
      lAffected = .RecordsAffected
    End With
    If lAffected > 0 Then
      With .OpenRecordset("select @@identity")
'        fltr = fltr & "," & .Fields(0)
        Debug.Print "ChapterID", .Fields(0)
        .Close
      End With
    Else
      MsgBox "Failed"
      Exit Function
    End If
  End With
  QDefInsert = Err = 0

End Function

In the Immediate Window:
Code: 
?QDefInsert
ChapterID      25
True

It's curious if it's a version thing.
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

M
Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs
Replies
1
Views
3,813
mdlueck
M
M
Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects
Replies
3
Views
117,241
mdlueck
M
E
Return a value
Replies
1
Views
705
Rich
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom