By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,666 Members | 1,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,666 IT Pros & Developers. It's quick & easy.

update in multiple tables through VBA

P: 4
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
May 21 '07 #1
Share this Question
Share on Google+
2 Replies

Rabbit
Expert Mod 10K+
P: 12,441
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.
May 21 '07 #2

P: 4
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
May 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.