update in multiple tables through VBA

[Jasmine5]

I have around 10 tables (let's call them tblNEEDUPDATE) and each has around 10-15 fields (first field is Name, rest have numbers). Also, I have a table let's call it tblUSEINUPDATE (this also has the name column) and TOTAL column.

Basically for each tblNEEDUPDATE, I want to update all fields except for name), so that each column = column/tblUSEINUPDATE.TOTAL.

I know I can go hard way and create SQL which has individual table and column names, but I was wondering if I can use a loop to go through each table (this I know how to do through Alltables) and each field and use update sql statement.


Something like THIS


Sub test()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData

For Each obj In dbs.AllTables

BUT WHAT TO DO HERE
HOW DO i define for each column if column name <>"name" then have update statement
basically my question is how do I define field object in access?

End If

Next obj
End Sub


Thanks very much for
Jasmine

[Rabbit]

I have around 10 tables (let's call them tblNEEDUPDATE) and each has around 10-15 fields (first field is Name, rest have numbers). Also, I have a table let's call it tblUSEINUPDATE (this also has the name column) and TOTAL column.

Basically for each tblNEEDUPDATE, I want to update all fields except for name), so that each column = column/tblUSEINUPDATE.TOTAL.

I know I can go hard way and create SQL which has individual table and column names, but I was wondering if I can use a loop to go through each table (this I know how to do through Alltables) and each field and use update sql statement.


Something like THIS


Sub test()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData

For Each obj In dbs.AllTables

BUT WHAT TO DO HERE
HOW DO i define for each column if column name <>"name" then have update statement
basically my question is how do I define field object in access?

End If

Next obj
End Sub


Thanks very much for
Jasmine

Before you even do this, it's not a good idea to store calculated values like you're doing. Simply because you run into this very problem where you have to update all the tables whenever a value changes. It's better to leave it to a query to run just before you need it.

[Jasmine5]

Before you even do this, it's not a good idea to store calculated values like you're doing. Simply because you run into this very problem where you have to update all the tables whenever a value changes. It's better to leave it to a query to run just before you need it.

thanks for your reply, I just looked in the previous responses I found an answer to my question, so my code would be like this and it worked:

Sub FieldNames()
Dim Rst As Recordset
Dim s As Field
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData

For Each obj In dbs.AllTables

Set Rst = Application.CurrentDb.OpenRecordset(obj.Name)
For Each s In Rst.Fields

Debug.Print s.Name
'will put update statement here
Next

Rst.Close

Next obj

End Sub