db.recordsaffected after append query is always 0 (1 Viewer)

Morten

Registered User.
Local time
Today, 19:57
Joined
Sep 16, 2009
Messages
53
Hi,

We have a database which is linked to an Excel spreadsheet and two lists in SharePoint.

We use the spreadsheet to update existing contracts and append new ones to SharePoint.

We have build three queries and they work as intended, but the result from db.recordsaffected is always 0 after we have exequted the append query 'qry_ap_new_contract_to_powldata'.

What could be wrong?


Code:
Public sStatusMsg As String
Private Sub cmd_update_data_Click()

Dim db As DAO.Database
'Dim sstatusmsg As String
Dim strQry As String
sStatusMsg = Empty
DoCmd.Hourglass True

'Turns off the Access warning messages
DoCmd.SetWarnings False

Set db = CurrentDb

'Kør forespørgsel til tilføjelse af nye kontrakter i POWL data:
strAppendPowl = "qry_ap_new_contract_to_powldata"
db.Execute strAppendPowl
sStatusMsg = db.RecordsAffected & " kontrakter tilføjet"
Debug.Print db.RecordsAffected
Debug.Print sStatusMsg

Me![MyProgress].Value = "33%"
'MsgBox sStatusMsg, vbOKOnly + vbInformation, "Tilføjelse POWL data"

' Kør forespørgsel til opdatering af eksisterende POWL data
strUpdatePowl = "qry_up_existing_powldata"
db.Execute strUpdatePowl, dbFailOnError
sStatusMsg = sStatusMsg & Chr(10) & db.RecordsAffected & " kontrakter opdateret"
Me![MyProgress].Value = "66%"
'MsgBox sStatusMsg, vbOKOnly + vbInformation, "Opdatering POWL data"

' Kør forespørgsel til opdatering af opfølgningsliste
strUpdateOpfoelgning = "qry_up_powldata_in_opfoelgningsliste"
db.Execute strUpdateOpfoelgning, dbFailOnError
sStatusMsg = sStatusMsg & Chr(10) & db.RecordsAffected & " kontrakter har ændret stamdata"
Me![MyProgress].Value = "100%"
'MsgBox sStatusMsg, vbOKOnly + vbInformation, "Stamdataændring kontrakter"

DoCmd.Hourglass False
'Turns the Access warning messages back on
DoCmd.SetWarnings True
MsgBox sStatusMsg, vbOKOnly, "vba\ cmd_update_data_click"
End Sub
 

MarkK

bit cruncher
Local time
Today, 12:57
Joined
Mar 17, 2004
Messages
8,178
You could try using the .RecordsAffected property of the QueryDef object rather than the database. I'm not sure this will make a difference, but perhaps.
Here is how I would code that, FWIW...
Code:
Private Sub cmd_update_data_Click()
    Dim qdfs
    Dim msgs
    Dim i As Integer
    Dim msg As String
    
    qdfs = Array("qry_ap_new_contract_to_powldata", "qry_up_existing_powldata", "qry_up_powldata_in_opfoelgningsliste")
    msgs = Array("tilføjet", "opdateret", "har ændret stamdata")
    
    DoCmd.Hourglass True
    For i = 1 To 3
        With CurrentDb.QueryDefs(qdfs(i))
            .Execute dbFailOnError
            msg = msg & [COLOR="Blue"].RecordsAffected[/COLOR] & " kontrakter " & msgs(i) & vbCrLf
            .Close
        End With
        Me.MyProgress = Format(i / 3, "Percent")
    Next
    DoCmd.Hourglass False
    
    MsgBox msg, vbOKOnly, "vba\ cmd_update_data_click"
End Sub
So you can see this executes the queries in a loop, and uses the RecordsAffected property of the QueryDef, not the DAO.Database.
hth
Mark
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:57
Joined
Feb 28, 2001
Messages
27,001
The first question I would ask is, if you converted your action query to a SELECT query with the same identical WHERE clause, how many records do you get back?

If the answer is 0, then your problem is in your WHERE clause. Wouldn't be the first time I've seen an over-restrictive WHERE clause.
 

Morten

Registered User.
Local time
Today, 19:57
Joined
Sep 16, 2009
Messages
53
You could try using the .RecordsAffected property of the QueryDef object rather than the database. I'm not sure this will make a difference, but perhaps.
So you can see this executes the queries in a loop, and uses the RecordsAffected property of the QueryDef, not the DAO.Database.
hth
Mark

Thanks!
I tried you code but I still get the same result from the append Query, which is zero.

Best regards
Morten
 

Morten

Registered User.
Local time
Today, 19:57
Joined
Sep 16, 2009
Messages
53
The first question I would ask is, if you converted your action query to a SELECT query with the same identical WHERE clause, how many records do you get back?

If the answer is 0, then your problem is in your WHERE clause. Wouldn't be the first time I've seen an over-restrictive WHERE clause.

I added a new contract to the Excel sheet and then changed the Query from append to select. The result from the Query was 1, which was the expected result.

Best regards
Morten
 

static

Registered User.
Local time
Today, 19:57
Joined
Nov 2, 2015
Messages
823
When creating a linked table to Excel, Access says...

Changes made to the source data will be reflected in the linked table. However, the source data cannot be changed from within Access.
 

Morten

Registered User.
Local time
Today, 19:57
Joined
Sep 16, 2009
Messages
53
Changes made to the source data will be reflected in the linked table. However, the source data cannot be changed from within Access.

Could you explain what that has to do with the recordsaffected not showing the correct number for appended rows, because I have no trouble changing and updating data from Excel to Sharepoint with my three queries.

Best regards
Morten
 

static

Registered User.
Local time
Today, 19:57
Joined
Nov 2, 2015
Messages
823
Not really. I had the wrong end of the stick, thought you were appending to Excel.

I've never used sharepoint. Maybe it just doesn't send a message back.
 

Morten

Registered User.
Local time
Today, 19:57
Joined
Sep 16, 2009
Messages
53
Okay I changed the code a little bit, so I now use Dcount to Count the records in a select Query which is identical with the append Query.

Not the best solution...so I'm still interesting in a solution for the recordsaffected syntax.

Code:
Private Sub cmd_update_data_Click()
    Dim qdfs
    Dim msgs
    Dim i As Integer
    Dim msg As String
    Dim rsl
    
    RecordCount = DCount("*", "qry_select_new_contracts")
    
    qdfs = Array("qry_ap_new_contract_to_powldata", "qry_up_existing_powldata", "qry_up_powldata_in_opfoelgningsliste")
    msgs = Array("tilføjet", "opdateret", "har ændret stamdata")
    rsl = Array("33%", "66%", "100%")
    
    DoCmd.Hourglass True
    For i = 0 To 2
        With CurrentDb.QueryDefs(qdfs(i))
            .Execute dbFailOnError
            'msg = msg & .RecordsAffected & " kontrakter " & msgs(i) & vbCrLf
            msg = msg & IIf(qdfs(i) = "qry_ap_new_contract_to_powldata", RecordCount, .RecordsAffected) & " kontrakter " & msgs(i) & vbCrLf
            .Close
        End With
        Me.MyProgress = rsl(i)  'Format(i / 3, "Percent")
    Next
    DoCmd.Hourglass False
    
    MsgBox msg, vbOKOnly, "vba\ cmd_update_data_click"
End Sub

Best regards
Morten
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:57
Joined
Feb 28, 2001
Messages
27,001
I'm not up on sharepoint. However, it seems that your query actually does the append operation. At least that's what I'm getting. The only problem is the .RecordsAffected count. Is that correct?
 

Morten

Registered User.
Local time
Today, 19:57
Joined
Sep 16, 2009
Messages
53
I'm not up on sharepoint. However, it seems that your query actually does the append operation. At least that's what I'm getting. The only problem is the .RecordsAffected count. Is that correct?

Yes, that is correct.

Best regards
Morten
 

Users who are viewing this thread

Top Bottom