Update query all fields

[diegomarino]

Hi
i know i can't use "*" in and update query, but is there a way to update every field with an update query?
i have a code that iterate through every table so the query structure must be the same for every table even if they have different field number

[Welshgasman]

You cannot.?
How is Access meant to know what updates what?

[diegomarino]

i thought it was smarter. However in append query i can, is there a workaroud? or maybe i should run a piece of code for every field in a table counting the number of fields?

[RayMilhon]

i thought it was smarter. However in append query i can, is there a workaroud? or maybe i should run a piece of code for every field in a table counting the number of fields?

So you're updating every field to contain the same data???? You need to explain your data structure and more detail on what you're trying to do. There's too much room for misinterpretation here.

[kd2017]

Agreed with Ray. It would probably be best explain what the end goal is.

As for a work around you could build a vba function that loops through the fields in a given table and dynamically builds your query.

https://stackoverflow.com/questions/...t-column-names

Here is an example that you will need to edit to suit your needs.

Public Sub example()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qry As String
    Dim tbl As String
    Dim new_value As String
    
    'this is the name of the table in question
    tbl = "[TABLE_NAME_HERE]"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(tbl)
    
    'build an update query string
    qry = "UPDATE " & tbl & " SET "
    For Each fld In rs.Fields
        new_value = "0" '<--- what ever you're trying to set the field value to. may need to consider data types, eg dates need to be surrounded by # or strings need quotation marks
        qry = qry & "[" & fld.Name & "] = " & new_value & ", "
    Next
    
    'remove the last comma and space from last loop
    qry = Left(qry, Len(qry) - 2)
    
    
    'close the recordset. we don't need it anymore
    rs.Close
    
    'print the string to the immediate debug window and make sure it looks correct (CTRL-G)
    Debug.Print qry
    
    'execute the qry (uncomment the following line when output from debug.print line above is built correctly)
    'db.Execute qry, dbFailOnError
    
    
ExitHandler:
    'clean up
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    Debug.Print "*** Error #" & Err.Number & " - " & Err.Description
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

[diegomarino]

yes that's the one, i was building something similar but this will save me some time. thx

[diegomarino]

that's my solution, it append and update from excel files, maybe i'll add delete too but it's easy. I made just one test but it seems to work. Naturally if you have autonumber field there will be changes to make

Public Function ImportFromExcel() ' controlla se i file excel prodotti da powerapps sono più aggiornati delle tabelle access e le aggiorna   Dim db As DAO.Database, Rst As DAO.Recordset, RstApp As DAO.Recordset, Tbf As DAO.TableDef, FldNum As Integer
   Dim TbEx As String, FldName As String, ColName As String
   
   Set db = CurrentDb
   'DoCmd.SetWarnings False
   For Each Tbf In db.TableDefs
      TbEx = Tbf.Name & "ex"
      'Debug.Print (Tbf.Name)
      If ifTableExists(TbEx) Then
         FldName = GetIndex(Tbf)
         DoCmd.RunSQL ("INSERT INTO [" & Tbf.Name & "] SELECT [" & TbEx & "].* FROM [" & TbEx & "] LEFT JOIN " & vbCrLf & _
                      "[" & Tbf.Name & "] ON [" & TbEx & "].[" & FldName & "] = [" & Tbf.Name & "].[" & FldName & "] " & vbCrLf & _
                      "WHERE ((([" & Tbf.Name & "].[" & FldName & "]) Is Null) AND (([" & TbEx & "].app)=""pw""));")
         DoCmd.RunSQL (Updatequery(Tbf))
      End If
   
   Next Tbf
   'DoCmd.SetWarnings True
End Function

Public Function Updatequery(Tb As DAO.TableDef) As StringOn Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qry As String
    Dim Tbl As String
    Dim new_value As String
    Dim IdxName As String


    'this is the name of the table in question
    Tbl = Tb.Name
    Set db = CurrentDb
    
    Set rs = db.OpenRecordset(Tbl)
    IdxName = GetIndex(Tb)
    'build an update query string
    qry = "UPDATE [" & Tbl & "ex] INNER JOIN [" & Tbl & "] ON [" & Tbl & "ex].[" & IdxName & "] = [" & Tbl & "].[" & IdxName & "] SET"
    For Each fld In rs.Fields
        new_value = Tbl & "Ex" & ".[" & fld.Name & "]"
        qry = qry & "[" & Tbl & "].[" & fld.Name & "] = " & new_value & ", "
    Next
    
    'remove the last comma and space from last loop
    qry = Left(qry, Len(qry) - 2)
    qry = qry & " WHERE ((([" & Tbl & "ex].[Data/ora modifica])>[" & Tbl & "].[Data/ora modifica]));"


    Debug.Print (qry)
    Updatequery = qry
    
    'close the recordset. we don't need it anymore
    rs.Close
    
    'print the string to the immediate debug window and make sure it looks correct (CTRL-G)


    
    'execute the qry (uncomment the following line when output from debug.print line above is built correctly)
    'db.Execute qry, dbFailOnError
    
    
ExitHandler:
    'clean up
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
ErrHandler:
    Debug.Print "*** Error #" & Err.Number & " - " & Err.Description
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Function

Public Function GetIndex(Tbf As DAO.TableDef) As String   
   Dim Idx As Index
   For Each Idx In Tbf.Indexes
      On Error Resume Next
      If Idx.Primary Then GetIndex = Replace(Idx.Fields, "+", "")
   Next Idx
End Function

[kd2017]

Thank you for sharing!