Don't exactly know what to do, Please help

[mdbBound]

create series of queries and apply to diff tables identified using a variablehelp

I have about 16 queries composed of SELECT, UPDATE, MakeTAble and APPEND. The 16 queries make up a process of what we call Classification of Accounts. I have to run these 16 queries in a click of a button. I have 5 diff tables (data from 5 sources: mainframe, dept. input, and so on) where I have to apply the process called Classification of Accounts to these 5 tables.

I think it is cumbersome to create 16 queries and attached them 5 times to my 5 different tables. Is there a better way? Combining them,? it is a requirement to have them stay in their own tables.

Please help. I thought of some function, but functions just return a value and I don't know how to create one but would like to learn with this.

Thank you

 

[pbaldy]

One way would be to create 5 sets of the 16 queries, 1 for each table. Saved queries execute faster than queries built in code. Alternatively, it would probably be possible to build/execute all the queries in code, using a variable for the table name. Might look roughly like this:

get recordset of table names

begin loop

currentdb.execute "DELETE * FROM " & rs!TableName & " WHERE ..."

currentdb.execute "UPDATE " & rs!TableName & " SET Blah..."
...

loop

 

[mdbBound]

Alternatively, it would probably be possible to build/execute all the queries in code, using a variable for the table name. Might look roughly like this:
get recordset of table names
begin loop
currentdb.execute "DELETE * FROM " & rs!TableName & " WHERE ..."

currentdb.execute "UPDATE " & rs!TableName & " SET Blah..."
...
loop

Thanks for replying, I will try to come up with the code and post it so you or anybody can help me expand this VBA structure, I am very new at VBA area.
Where do I declare the variable rs!TableName. I thought of using a combo box to select my table and click the button to process it. Anybody? Please.

 

[Thecherub]

why not use a macro

One way would be to create 5 sets of the 16 queries, 1 for each table. Saved queries execute faster than queries built in code. Alternatively, it would probably be possible to build/execute all the queries in code, using a variable for the table name. Might look roughly like this:

get recordset of table names

begin loop

currentdb.execute "DELETE * FROM " & rs!TableName & " WHERE ..."

currentdb.execute "UPDATE " & rs!TableName & " SET Blah..."
...

loop

Why not use a macro to run the Querys in Order. You could also set a Form with a combo box for selecting the Table that you want the querys to run on and feed that info into the querys.

I hope that is not too confusing.

 

[pbaldy]

Why not use a macro to run the Querys in Order. You could also set a Form with a combo box for selecting the Table that you want the querys to run on and feed that info into the querys.

Running queries in order is the easy part. Running the same query using different tables as the source is the tricky part. I'd love to see a sample of a form with a combo box for tables, and have a query that can use that form reference as the table in it's FROM clause (without VBA).

 

[mdbBound]

Why not use a macro to run the Querys in Order.

Hi,

Thank you for your thoughts. This part, I know how to do. Remember, that when you do a query you have to point it to a particular table. My goal is to create a generic 16 queries (process) and apply it to a chosen table name, like in a variable. Just don't know how to start it yet. I'll be greatful if you have some similar code I could start with.

 

[mdbBound]

Running queries in order is the easy part. Running the same query using different tables as the source is the tricky part. I'd love to see a sample of a form with a combo box for tables, and have a query that can use that form reference as the table in it's FROM clause (without VBA).

pbaldy, that is exactly what I am trying to say. I hope we can get contribution for this thread. Let's hope that Pat, Jon, Doc Man, WayneRyan can share about this. I'll let you know when i come up with something ASAP.

 

[pbaldy]

The method I outlined would work, unless there's something unusual in the queries. The folks you mentioned are certainly smarter than I am, and may post a better way, but I think mine would work. I don't think the form idea will work, but I'm always willing to learn.

 

[mdbBound]

Yes, I know that the method you mentioned will work. That is where I got the idea of "variable" and I think I know how to put the value of a combo box to a variable. The problem is the FROM clause.

I hope you were not offended when I mentioned their names, I did not mean that they were smarter, it's just that I have not seen a similar situation in this forum and therefore don't know how these other guys will tackle this.

 

[pbaldy]

I'm certainly not offended by your mentioning them, and I have no problem acknowledging that there are people who know more about a subject than I do, and those folks qualify! I’m looking forward to seeing if there’s a better solution myself. It's not something I've needed, but I like learning new things.