Solved How to get affected records count of an update query? (1 Viewer)

K

KitaYama

Well-known member
Local time
Tomorrow, 00:06
Joined
Jan 6, 2022
Messages
1,893
The following code returns 0 Affected records.
How can I get how many records were updated?
The table is updated successfully.

SQL: 
Public Function test()

    Dim db As DAO.Database
    Dim sql As String
  
    Set db = CurrentDb
    sql = "Update tbl Set field1=p0 WHERE PK=1"
  
    With db.CreateQueryDef("", sql)
        .Parameters(0) = 12
        .Execute dbSeeChanges
    End With
  
    Debug.Print db.RecordsAffected
  
End Function

Thanks.
 
I would do this...
Code: 
Public Function test()
    Const SQL_UPDATE as string = _
        "UPDATE tbl SET field1 = p0 WHERE PK = 1"
 
    With CurrentDb.CreateQueryDef("", SQL_UPDATE)
        .Parameters(0) = 12
        .Execute dbFailOnError
        Debug.Print .RecordsAffected
    End With
End Function
The QueryDef itself exposes a .RecordsAffected property.
 
MarkK said:
I would do this...
Code: 
Public Function test()
    Const SQL_UPDATE as string = _
        "UPDATE tbl SET field1 = p0 WHERE PK = 1"
 
    With CurrentDb.CreateQueryDef("", SQL_UPDATE)
        .Parameters(0) = 12
        .Execute dbFailOnError
        Debug.Print .RecordsAffected
    End With
End Function
The QueryDef itself exposes a .RecordsAffected property.
Click to expand...
Million thanks.

Where would I be without you guys.
 
So the .RecordsAffected is exposed by the object that performs the .Execute

If you had run the query directly, then it would have been retrieved from the Database object:
Code: 
Public Function test()

    Dim sql As String
 
    sql = "Update tbl Set field1=12 WHERE PK=1"
 
    With CurrentDb
      .Execute sql, dbSeeChanges + dbFailOnError
      Debug.Print .RecordsAffected
    End With
 
End Function
 
NauticalGent said:
I really should drink my coffee before posting...
Click to expand...
If you had your coffee, Here's what I'm doing.
That code was just a test to show my problem.
I have a function for updating data in different tables:

SQL: 
Public Function ParamUpdate(tbl As String, _
                            TheFields As String, _
                            Params As Variant, _
                            ByVal Filtr As String) As Long

    Dim Counter As Integer
    Dim sql As String
    Dim Flds As Variant

    Flds = Split(TheFields, ",")

    sql = "Update " & tbl & " Set "
    For Counter = 0 To UBound(Flds)
        sql = sql & Flds(Counter) & "=p" & Counter & ","
    Next
    sql = Left(sql, Len(sql) - 1) & " WHERE " & Filtr
 
    With CurrentDb.CreateQueryDef("", sql)
        For Counter = 0 To UBound(Params)
            .Parameters(Counter) = Params(Counter)
        Next
        .Execute dbSeeChanges
        ParamUpdate = .RecordsAffected
    End With
 
End Function

Then call this function like this:
SQL: 
Flds = "UpdatedOn, NcVersion"
Vals = Array(Now(), DMax("NcVersion", "tblNcVersion"))
ParamUpdate "tblProducts", Flds, Vals, "ProductPK=" & ProductPK


OR

fltr=.......
Filds = "Deleted, DeletedRemarks, DeletedOn, DeletedBy, Ref"
Vals = Array(True, Remarks, Now, tempVars("User"),MyFunction("RefPK","tblRef"))
ParamUpdate "tblOrders", Filds, Vals, fltr

I don't need to take care of the number of parameters, Data type, using single quotes or # or .....
Access takes care of that.
I only pass any number of fields with any number of values. The values can be calculated, or even another function.
I have another function for Insert query too.
 
Last edited:
KitaYama said:
Code: 
Public Function ParamUpdate(tbl As String, _
                            TheFields As String, _
                            Params As Variant, _
                            ByVal Filtr As String) As Long

' ...
Click to expand...

I'm curious why you pass the fields as a concatenated string when you pass the values as an array.

Why not pass both fields and values as arrays, then no need to split before looping in the function?

(Not that it's a problem, just wondered about the mixing of methods)
 
cheekybuddha said:
I'm curious why you pass the fields as a concatenated string when you pass the values as an array.

Why not pass both fields and values as arrays, then no need to split before looping in the function?

(Not that it's a problem, just wondered about the mixing of methods)
Click to expand...
Not a specific reason. There was several changes during the last few years, and the function changed a little by little. It's the remaining of the previous versions. I had noticed what you mentioned, but changing the Filds to an array, saves only one line of code (Split line is not necessary). But I have to go through several hundered of modules and form classes to correct them. So I left it how it is.
Maybe the next time I have a little time on my hand, I'll change that too.

Thanks.
 
If you use ADO you gain some advantages.
1) You don't need to number your parameter names, you can just use " = ?, " rather than " = p<n>, ". This allows you to simply Join() your Split() fields.
2) You can pass an array of parameter values directly to the ADODB.Command.Execute method. This saves you enumerating your parameters array.
3) RecordsAffected can be assigned directly to the return value of the function in ADODB.Command.Execute.

Bringing it all together, ADO code could look like...
Code: 
Public Function ParamUpdate(table As String, fields As String, vParams, filter As String) As Long
    Const DELIMIT_FIELD As String = " = ?, "

    fields = Join(Split(fields, ","), DELIMIT_FIELD) & " = ? "

    With New ADODB.Command
        .CommandText = "UPDATE " & table & " SET " & fields & "WHERE " & filter
        Set .ActiveConnection = CurrentProject.AccessConnection
        .Execute ParamUpdate, vParams, adExecuteNoRecords
    End With
End Function
 
@MarkK, thanks that was a great help.
Is it possible to use the same method for Insert queries too?

I couldn't come up with the .commandtext structure.
Thanks.

Edit: It's what I have at present :
SQL: 
Public Function ParamInsert(tbl As String, _
                            TheFields As String, _
                            Params As Variant) As String

    Dim Counter As Integer
    Dim sql As String
    
    sql = "INSERT INTO " & tbl & "(" & TheFields & ") VALUES ("
    For Counter = 0 To UBound(Params)
        sql = sql & "p" & Counter & ", "
    Next
    
    sql = Left(sql, Len(sql) - 2) & ");"
    
    With CurrentDb.CreateQueryDef("", sql)
        For Counter = 0 To UBound(Params)
            .Parameters(Counter) = Params(Counter)
        Next
        .Execute
    End With
    
End Function
 
Last edited:
You could do something like this...
Code: 
Function ParamInsert(table As String, fields As String, vParams) As Long
    Const SQL_TEMPLATE As String = "INSERT INTO {0} ( {1} ) VALUES ( {2} )"
   
    With New ADODB.Command
        .CommandText = MyReplace(SQL_TEMPLATE, table, fields, GetInsertParams(vParams))
        Set .ActiveConnection = CurrentProject.AccessConnection
        .Execute ParamInsert, vParams, adExecuteNoRecords
    End With
End Function

Private Function GetInsertParams(vParams) As String
    Dim tmp As String

    tmp = String(UBound(vParams), ".")
    GetInsertParams = Replace(tmp, ".", "?, ") & "?"
End Function

Function MyReplace(Base As String, ParamArray Values()) As String
    Dim i As Integer

    For i = 0 To UBound(Values)
        Base = VBA.Replace(Base, "{" & i & "}", Values(i))
    Next
    MyReplace = Base
End Function
I have that MyReplace() function lying around because it makes it so much easier to insert ordered elements into a string template.
You could also write function GetInsertParams() like this if it makes more sense...
Code: 
Private Function GetInsertParams2(vParams) As String
    Dim i As Integer
    Dim tmp As String
   
    For i = 0 To UBound(vParams)
        tmp = tmp & ", ?"
    Next
    GetInsertParams2 = Mid(tmp, 3)
End Function
They both produce the same output, but the first one is maybe a bit less clear about how it works. In this case ADO does not provide as much of an advantage, because you still need to construct your VALUES ( ) parameter list somewhat laboriously.
hth
 
MarkK said:
You could do something like this...
Code: 
Function ParamInsert(table As String, fields As String, vParams) As Long
    Const SQL_TEMPLATE As String = "INSERT INTO {0} ( {1} ) VALUES ( {2} )"
  
    With New ADODB.Command
        .CommandText = MyReplace(SQL_TEMPLATE, table, fields, GetInsertParams(vParams))
        Set .ActiveConnection = CurrentProject.AccessConnection
        .Execute ParamInsert, vParams, adExecuteNoRecords
    End With
End Function

Private Function GetInsertParams(vParams) As String
    Dim tmp As String

    tmp = String(UBound(vParams), ".")
    GetInsertParams = Replace(tmp, ".", "?, ") & "?"
End Function

Function MyReplace(Base As String, ParamArray Values()) As String
    Dim i As Integer

    For i = 0 To UBound(Values)
        Base = VBA.Replace(Base, "{" & i & "}", Values(i))
    Next
    MyReplace = Base
End Function
I have that MyReplace() function lying around because it makes it so much easier to insert ordered elements into a string template.
You could also write function GetInsertParams() like this if it makes more sense...
Code: 
Private Function GetInsertParams2(vParams) As String
    Dim i As Integer
    Dim tmp As String
  
    For i = 0 To UBound(vParams)
        tmp = tmp & ", ?"
    Next
    GetInsertParams2 = Mid(tmp, 3)
End Function
They both produce the same output, but the first one is maybe a bit less clear about how it works. In this case ADO does not provide as much of an advantage, because you still need to construct your VALUES ( ) parameter list somewhat laboriously.
hth
Click to expand...
Thanks.
I sincerely appreciate your help.
 
Note: I always see the effort to put together an SQL statement using code.
I personally prefer to pass a complete SQL statement as an argument, and also the name of a saved query. For me it makes more sense to write a query the way it should look, rather than trying to put it together somehow and subjecting myself to restrictions.

On the one hand, this allows me to be more variable, e.g. to use subqueries as a filter, and things like JOIN and grouping can also occur. On the other hand, the query will then contain a real parameter list at the beginning, so that I can address the parameters by name and thus specifically and assign values.
When passing parameters, you have to pay attention to the necessary data types very quickly. Passing them in sequence can quickly become confusing.

For an action query it would look something like this:
Code: 
Public Function ExecuteParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
                                  ParamArray QueryParams() As Variant) As Long
    Dim qd As DAO.QueryDef
    Dim i As Long

    If UBound(QueryParams) Mod 2 = 1 Then             ' Paare Parameterfeld-Wert vorhanden
        If QueryExists(MyDB, AnyQuery) Then
            ' gespeicherte Abfrage
            Set qd = MyDB.QueryDefs(AnyQuery)
        Else
            ' SQL-Anweisung
            Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
        End If
        For i = 0 To UBound(QueryParams) Step 2
            qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
        Next
        qd.Execute dbFailOnError
        ExecuteParamQuery = qd.RecordsAffected
        qd.Close
        Set qd = Nothing
    End If
End Function

Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
    Dim qd As DAO.QueryDef
    For Each qd In MyDB.QueryDefs
        If qd.Name = QueryName Then
            QueryExists = True
            Exit For
        End If
    Next
End Function
Code: 
    Dim sSQL As String
    sSQL = "PARAMETERS parLong Long, parDate Date;" & _
           "UPDATE tblData SET F_Boolean = True, F_Date = parDate WHERE F_Long <= parLong"
    ' sSQL = "NameSavedQuery"
    Debug.Print ExecuteParamQuery(db, sSQL, "parDate", Now, "parLong", 2)
 
there is a udf on sample database called AnySQL(), you can modify it on your own.
you passed the SQL string and the parameters.

arnelgp

Thread 'fnAnySQL() parameter query'

i am now shelving my FixSql() function, for the time being to give way to a new function someone shared on this forum (you know who you are).

I modified the function so it can be generic and be easily ported, at least, to any SQL statement.

Code: 
Option Compare Database
Option Explicit

Public Function fnAnySQL(ByVal strSQL As String, ParamArray p() As Variant)
'
' strSQL must be an Any SQL statement (Select, Insert, Update, Delete)
' parameters should be in format @1, @2, ... to be
' consistent
'
' DO NOT use parameter name which has same name as your fieldname.
' using this will...
  • Like
 
Yeah, you bet, but notice especially the good sense in breaking problems up into tiny chunks. One block of code should do ONE thing. If your block of code needs a list constructed, get that list from a subroutine (which does only that one thing). If you need to replace elements in a string, do it in a subroutine. If your loop has an inner loop, perform the inner loop in a subroutine. If you write a procedure longer than 8 or 10 lines, start to look for units of work you can delegate to subroutines.

For instance, if I had multiple calls to create an ADODB.Command, as may be the case for you, I would expect to see a routines like this...
Code: 
Public Function ParamUpdate(table As String, fields As String, vParams, filter As String) As Long
    Const SQL_BASE As String = "UPDATE {0} SET {1} WHERE {2}"
    Const DELIMIT_FIELD As String = " = ?, "
    Dim sql As String

    fields = Join(Split(fields, ","), DELIMIT_FIELD) & " = ? "
    sql = MyReplace(SQL_BASE, table, fields, filter)
    ParamUpdate = RunADOCommand(sql, vParams)
End Function

Function ParamInsert(table As String, fields As String, vParams) As Long
    Const SQL_BASE As String = "INSERT INTO {0} ( {1} ) VALUES ( {2} )"
    Dim sql As String
    
    sql = MyReplace(SQL_BASE, table, fields, GetInsertParams(vParams))
    ParamInsert = RunADOCommand(sql, vParams)
End Function
    
Function RunADOCommand(sql As String, vParams) As Long
    With New ADODB.Command
        .CommandText = sql
        Set .ActiveConnection = CurrentProject.AccessConnection
        .Execute RunADOCommand, vParams, adExecuteNoRecords
    End With
End Function

Private Function GetInsertParams(vParams) As String
    Dim tmp As String
    
    tmp = String(UBound(vParams), ".")
    GetInsertParams = Replace(tmp, ".", "?, ") & "?"
End Function

Private Function MyReplace(Base As String, ParamArray Values()) As String
    Dim i As Integer
    
    For i = 0 To UBound(Values)
        Base = VBA.Replace(Base, "{" & i & "}", Values(i))
    Next
    MyReplace = Base
End Function
See how the call to create and execute the command is abstracted out into a subroutine?

Happy coding,
Mark
 
Which can be refactored as follows...
Code: 
Function ExecuteParamQuery(ByVal MyDB As DAO.Database, AnyQuery, ParamArray params()) As Long
    If UBound(params) Mod 2 = 1 Then             ' Paare Parameterfeld-Wert vorhanden
        With GetQuery(MyDB, AnyQuery)
            SetParams .Parameters, params
            .Execute dbFailOnError
            ExecuteParamQuery = .RecordsAffected
            .Close
        End With
    End If
End Function

Private Sub SetParams(Parameters As DAO.Parameters, params)
    Dim i As Integer
    
    For i = 0 To UBound(params) Step 2
        Parameters(params(i)) = params(i + 1)
    Next
End Sub

Private Function GetQuery(dbs As DAO.Database, name As String) As DAO.QueryDef
    If QueryExists(MyDB, AnyQuery) Then
        Set GetQuery = dbs.QueryDefs(AnyQuery)
    Else
        Set GetQuery = dbs.CreateQueryDef("", AnyQuery)
    End If
End Function

Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
    Const SQL_SELECT As String = _
        "SELECT Count(*) FROM MSysObjects WHERE Name = p0 And Type = 5"
    
    With MyDB.CreateQueryDef("", sql)
        .Parameters(0) = QueryName
        QueryExists = .OpenRecordset.fields(0).Value
    End With
End Function
Each unit of work is clearly abstracted out. Each unit does a single task, and each unit is now available for other consumers.
 
ebs17 said:
Note: I always see the effort to put together an SQL statement using code.
I personally prefer to pass a complete SQL statement as an argument, and also the name of a saved query. For me it makes more sense to write a query the way it should look, rather than trying to put it together somehow and subjecting myself to restrictions.

On the one hand, this allows me to be more variable, e.g. to use subqueries as a filter, and things like JOIN and grouping can also occur. On the other hand, the query will then contain a real parameter list at the beginning, so that I can address the parameters by name and thus specifically and assign values.
When passing parameters, you have to pay attention to the necessary data types very quickly. Passing them in sequence can quickly become confusing.

For an action query it would look something like this:
Code: 
Public Function ExecuteParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
                                  ParamArray QueryParams() As Variant) As Long
    Dim qd As DAO.QueryDef
    Dim i As Long

    If UBound(QueryParams) Mod 2 = 1 Then             ' Paare Parameterfeld-Wert vorhanden
        If QueryExists(MyDB, AnyQuery) Then
            ' gespeicherte Abfrage
            Set qd = MyDB.QueryDefs(AnyQuery)
        Else
            ' SQL-Anweisung
            Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
        End If
        For i = 0 To UBound(QueryParams) Step 2
            qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
        Next
        qd.Execute dbFailOnError
        ExecuteParamQuery = qd.RecordsAffected
        qd.Close
        Set qd = Nothing
    End If
End Function

Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
    Dim qd As DAO.QueryDef
    For Each qd In MyDB.QueryDefs
        If qd.Name = QueryName Then
            QueryExists = True
            Exit For
        End If
    Next
End Function
Code: 
    Dim sSQL As String
    sSQL = "PARAMETERS parLong Long, parDate Date;" & _
           "UPDATE tblData SET F_Boolean = True, F_Date = parDate WHERE F_Long <= parLong"
    ' sSQL = "NameSavedQuery"
    Debug.Print ExecuteParamQuery(db, sSQL, "parDate", Now, "parLong", 2)
Click to expand...
@ebs17 thanks for sharing your thoughts.
It's interesting to see how different developers do the same task in different ways.

Just for the sake of those who later try your code, you may want to correct the last piece of code where you showed how you test it.
The passed db should be CurrentDB in the list of parameters passed to the function. (or pre defined as currentdb)

For my tests, I deleted it, because I never work outside of currentDB.

Thanks again.
 
MarkK said:
Each unit does a single task, and each unit is now available for other consumers.
.....
If you write a procedure longer than 8 or 10 lines, start to look for units of work you can delegate to subroutines.
Click to expand...
While I understand your point, but it has always made me think too hard when it's time to move a part of a function to a subroutine.
When a database grows to hundreds of forms and module, and you chunk your function to that tiny pieces, I think after a while, you loose track of what is what. You chunk a task into several smaller functions, but after 10 or 20 years, when you start to add a new utility to your database, do you still remember that there's one tiny functions somewhere buried in thousands of functions you wrote long time ago and can be used now?

Again, I understand your point and I've always tried to do so, but cutting down every function to less than 10 lines of code, seems too strict to me.
 
MarkK said:
Code: 
Private Function GetQuery(dbs As DAO.Database, name As String) As DAO.QueryDef
    If QueryExists(MyDB, AnyQuery) Then
        Set GetQuery = dbs.QueryDefs(AnyQuery)
    Else
        Set GetQuery = dbs.CreateQueryDef("", AnyQuery)
    End If
End Function
Click to expand...

I haven't tested, but this function seems a bit off.

Where is parameter name used?

How is AnyQuery declared and passed?

Also dbs vs MyDB
 
You must log in or register to reply here.

Similar threads

adhoustonj
How many db's do I really have open?
Replies
5
Views
343
Pat Hartman
P
A
Restrictions Update Message
Replies
17
Views
309
Gasman
Gasman
N
How to ensure the open recordset include also any new record entered
Replies
9
Views
278
nector
N
A
Match two numbers and then update
Replies
10
Views
222
Gasman
Gasman
L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
286
lbcurrier
L

Users who are viewing this thread

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