Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34

    Execute append query returning 0 records affected

    Hello,



    I am using Execute some SQL to append some records to a table. It works as intended, but I want a message box to confirm how many records were added. RecordsAffected is always giving 0, no matter how many records were actually affected. Does anyone know what I'm doing wrong, please?

    Code:
    Private Sub cmdOK_Click()
        Dim dbs As DAO.Database     'This database
        Dim strSQL As String           'The append query
        Dim lngRowInsrt As Long             'Number of rows copied
       
        On Error GoTo Error_Handler
    
    
        Set dbs = CurrentDb
    
    
        strSQL = "INSERT INTO tblSession " & _ 'Lots more SQL here
    
    
        If Me.Dirty Then Me.Dirty = False
        dbs.Execute strSQL, dbFailOnError
        lngRowInsrt = dbs.RecordsAffected 'This always gives 0 even though records have been added.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you please try this:
    Code:
    Private Sub cmdOK_Click()
        Dim dbs As DAO.Database     'This database
        Dim strSQL As String           'The append query
        Dim lngRowInsrt As Long             'Number of rows copied
        
        Dim qdfTemp As QueryDef 'NEW
    
    
        On Error GoTo Error_Handler
    
    
        Set dbs = CurrentDb
    
    
        strSQL = "INSERT INTO tblSession " & _ 'Lots more SQL here
    
    
        If Me.Dirty Then Me.Dirty = False
       ' dbs.Execute strSQL, dbFailOnError
       'lngRowInsrt = dbs.RecordsAffected 'This always gives 0 even though records have been added.
    
    
        With dbs
     	.Execute strSQL, dbFailOnError
       	lngRowInsrt = .RecordsAffected
        End With
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would have been nice to see the full VBA, but try this example.

    It copies 9 records from Table1 to Table using SQL in code or a saved query.

    The SQL of the saved query is
    Code:
    INSERT INTO Table2 ( LEI_Date, Step )
    SELECT Table1.LEI_Date, Table1.Step
    FROM Table1;
    (same as the SQL in code)
    Attached Files Attached Files

  4. #4
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    Thank you for the suggestion, Gicu.
    The append query still works but it still says 0 rows added.
    I noticed that you declare qdfTemp as a variable but don't use it?

    ssanfu, it works with your SQL, and gives the correct number of rows added.
    Thanks for that.
    Might I be having trouble with mine because it is a parameter query?
    Here is my VBA in full.

    Code:
        Dim dbs             As DAO.Database     'This database
        Dim dtmWCOld        As Date             'Original week commencing date
        Dim dtmWCNew        As Date             'New week commencing
        Dim lngNumWks       As Long             'Copy to this many weeks in the future
        Dim lngUserID       As Long             'Staff primary key
        Dim strSQL          As String           'The append query
        Dim lngRowInsrt     As Long             'Number of rows copied
        Dim strMsg          As String           'Message box text
        
    'Copies the current week's sessions to a new week.
    
    
        On Error GoTo Error_Handler
    
    
        Set dbs = CurrentDb
        dtmWCOld = Forms.frmSessions.txtWC
        lngNumWks = Me.txtCopyNoWeeks.Value
        dtmWCNew = DateAdd("ww", lngNumWks, dtmWCOld)
        lngUserID = Forms.frmMenu.txtUserID
        
    'Copying sessions correctly but Records Affected given as 0.
        strSQL = "INSERT INTO tblSession " & _
            "(AppointmentID, SessionDate, StartHours, StartMinutes, EndHours, EndMinutes, Online, Typed, HoursTyped, Comments, AttType) " & _
            "SELECT tblSession.[AppointmentID], DateAdd('d'," & 7 * lngNumWks & ", tblSession.[SessionDate]), " & _
            "tblSession.[StartHours], tblSession.[StartMinutes], tblSession.[EndHours], tblSession.[EndMinutes], tblSession.[Online], tblSession.[Typed], " & _
            "-1 * tblSession.[Typed] *((tblSession.[EndHours] + tblSession.[EndMinutes] / 60) - (tblSession.[StartHours] + tblSession.[StartMinutes] / 60)), " & _
            "tblSession.[Comments], " & 1 & _
            " FROM tlkpScheme " & _
            "INNER JOIN ((tblAppointment LEFT JOIN tblStudent ON tblAppointment.[StudentID] = tblStudent.[StudentPK]) " & _
            "INNER JOIN tblSession ON tblAppointment.[AppointmentID] = tblSession.[AppointmentID]) " & _
            "ON tlkpScheme.[SchemePK] = tblAppointment.[WorkScheme] " & _
            "WHERE tblAppointment.[StaffID] = " & lngUserID & _
            " AND DatePart('ww', tblSession.[SessionDate], 2) = " & DatePart("ww", dtmWCOld) & _
            " AND Year(tblSession.[SessionDate]) = " & Year(dtmWCOld) & ";"
            
        'Save any updates to Copy number of weeks.
        If Me.Dirty Then Me.Dirty = False
        'Perform the append query.
        With dbs
            .Execute strSQL, dbFailOnError
            lngRowInsrt = .RecordsAffected
        End With
        Forms.frmSessions.Requery
        'Close the dialogue
        DoCmd.Close acForm, "fdlgCopySessions", acSavePrompt
        'Confirm
        strMsg = lngRowInsrt & " sessions copied to w/c " & dtmWCNew & "."
        MsgBox strMsg, vbInformation, "Copy confirmation"

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    RasterImage, yes, I added the qdfTemp variable to use the QueryDef execute instead of the string; you might still want ot do that and see what you get. If you can't get it to work maybe create a select query similar to the append one and use dCount to get the number of records or even easier get the tblSession counts before and after insert:
    Code:
    Dim dbs             As DAO.Database     'This database
        Dim dtmWCOld        As Date             'Original week commencing date
        Dim dtmWCNew        As Date             'New week commencing
        Dim lngNumWks       As Long             'Copy to this many weeks in the future
        Dim lngUserID       As Long             'Staff primary key
        Dim strSQL          As String           'The append query
        Dim lngRowInsrt     As Long             'Number of rows copied
        Dim strMsg          As String           'Message box text
        
    'Copies the current week's sessions to a new week.
    
    
    Dim lCountBefore as long,lCountAfter as Long  'NEW
    
    
        On Error GoTo Error_Handler
    
    
    
    
        Set dbs = CurrentDb
        dtmWCOld = Forms.frmSessions.txtWC
        lngNumWks = Me.txtCopyNoWeeks.Value
        dtmWCNew = DateAdd("ww", lngNumWks, dtmWCOld)
        lngUserID = Forms.frmMenu.txtUserID
        
    'Copying sessions correctly but Records Affected given as 0.
        strSQL = "INSERT INTO tblSession " & _
            "(AppointmentID, SessionDate, StartHours, StartMinutes, EndHours, EndMinutes, Online, Typed, HoursTyped, Comments, AttType) " & _
            "SELECT tblSession.[AppointmentID], DateAdd('d'," & 7 * lngNumWks & ", tblSession.[SessionDate]), " & _
            "tblSession.[StartHours], tblSession.[StartMinutes], tblSession.[EndHours], tblSession.[EndMinutes], tblSession.[Online], tblSession.[Typed], " & _
            "-1 * tblSession.[Typed] *((tblSession.[EndHours] + tblSession.[EndMinutes] / 60) - (tblSession.[StartHours] + tblSession.[StartMinutes] / 60)), " & _
            "tblSession.[Comments], " & 1 & _
            " FROM tlkpScheme " & _
            "INNER JOIN ((tblAppointment LEFT JOIN tblStudent ON tblAppointment.[StudentID] = tblStudent.[StudentPK]) " & _
            "INNER JOIN tblSession ON tblAppointment.[AppointmentID] = tblSession.[AppointmentID]) " & _
            "ON tlkpScheme.[SchemePK] = tblAppointment.[WorkScheme] " & _
            "WHERE tblAppointment.[StaffID] = " & lngUserID & _
            " AND DatePart('ww', tblSession.[SessionDate], 2) = " & DatePart("ww", dtmWCOld) & _
            " AND Year(tblSession.[SessionDate]) = " & Year(dtmWCOld) & ";"
            
        'Save any updates to Copy number of weeks.
        If Me.Dirty Then Me.Dirty = False
        'Perform the append query.
        lCountBefore = dCount("*","tblSession") 'get count BEFORE append
        With dbs
            .Execute strSQL, dbFailOnError
            'lngRowInsrt = .RecordsAffected
            lCountAfter = dCount("*","tblSession") 'get count AFTER append
            lngRowInsrt=lCountAfter - lCountBefore 
       End With
        Forms.frmSessions.Requery
        'Close the dialogue
        DoCmd.Close acForm, "fdlgCopySessions", acSavePrompt
        'Confirm
        strMsg = lngRowInsrt & " sessions copied to w/c " & dtmWCNew & "."
        MsgBox strMsg, vbInformation, "Copy confirmation"
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you try to fetch the value as a property of the db, I think you need to enclose it in a With block, otherwise use a different type of object, such as a querydef and Execute that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The OP did enclose it in With in the last post and apparently still doesn't work .
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I always do a select first to see if I get data as I expect, then change to an update/append/make table whatever ?
    I also debug.print the strSQL string.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, missed that.
    Well, CurrentDb is not an object, it is a method that returns an object, so maybe try

    Dim db as database (perhaps DAO.Database would be better)
    db.Execute (something), dbfailonerror
    variable = db.recordsaffected

    EDIT - I forgot the SET part, and I think you need to open the db since you're not using a method to return it. So

    Dim db as database
    Set db = OpenDatabase("nameOfDatabase")
    db.Execute (something), dbfailonerror
    variable = db.recordsaffected

    That is more or less how it's done in M$ code for RecordsAffected. However, it's not clear if they intended the code sample to be run in the same db.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    That's what OP has (but dbs)
    Dim dbs As DAO.Database 'This database Dim dtmWCOld As Date 'Original week commencing date
    Dim dtmWCNew As Date 'New week commencing
    Dim lngNumWks As Long 'Copy to this many week
    Set dbs=CurrentDB
    With dbs
    .Execute strSQL, dbFailOnError
    lngRowInsrt = .RecordsAffected
    End With
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    this works for me
    Code:
    Sub testOpenDb()
    Dim db As DAO.Database '<<< also works with Dim db As Database
    
    Set db = CurrentDb
    With db
       .Execute ("query2"), dbFailOnError
       MsgBox "records affected: " & .RecordsAffected
    End With
    
    Set db = Nothing
    End Sub
    this does too (the sql is a copy and paste from query2)
    Code:
    Sub testRecsAffected2()
    Dim db As Database
    Dim sql As String
    
    Set db = CurrentDb
    sql = "UPDATE [Attendance Log] SET [Attendance Log].DateV = #1/2/2024# WHERE ((([Attendance Log].DateV) =#1/2/2022#));"
    
    With db
       .Execute (sql), dbFailOnError
       MsgBox "records affected: " & db.RecordsAffected
    End With
    
    End Sub
    If I run the query, the 2nd warning about the number of records to be updated is the same. Something weird going on. Testing now to see what happens when code returns 0 - are any records actually being updated for me?

    EDIT - there was an error in my dates, so the correct sql string DOES work for me. Edited post above to reflect this.
    Can only think that OP thinks table is updating but perhaps it's not because because the .Dirty line is doing something first. Perhaps it's inserting the same record(s) that the .Execute is supposed to be doing. In that case, the count would be zero yet the record(s) would appear to have been added by it. I would comment that out and test. It doesn't seem necessary, but then we can't see much of what comes before it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I agree, the OP mentioned the fact that the append query has parameters but that shouldn't affect this; I think getting the count of the target table before and after would provide a quick way to see if any records get indeed appended.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I agree, the OP mentioned the fact that the append query has parameters
    I never said anything about parameters. Did you see my edited post re: Dirty?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I never said you said something about parameters , I said the OP thinks that it might be the cause of it not working (in post #4
    Might I be having trouble with mine because it is a parameter query?
    )
    And yes I did see it, maybe the OP can let us know if commenting out that line changes the output.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, I read your post incorrectly.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  3. Replies: 5
    Last Post: 08-10-2015, 02:07 PM
  4. Replies: 9
    Last Post: 07-06-2015, 11:51 AM
  5. Replies: 2
    Last Post: 03-07-2014, 09:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums