VB code for MS Access queries?

[escudolm]

Hello. I have someone wanting me to try and clean up some of the queries that have been made. She has a master roster query running off other queries. She wants me to put VB code in place of the queries running in the master roster query, so she can delete those queries running off of the master roster one. It is just to clean it up and not have some many of them. So, can i put VB code in place of the queries, so I can delete them and just have the master roster query running of code? Thanks.

[salvelinus]

You can do it, but it might be a long, tedious process. Post an example.

[escudolm]

This is gonna sound dumb but, post an example of what? The query code?

[nagasrikanth]

Hi,

If the queries are "UPDATE" queries (Like Make table query,Update Query etc..It should not be a select query) then In a form under button_click() event use

docmd.runsql (sql_statement)

where "sql_statement" is one of u r Query's SQL Statement..

Its very easy..Try it.. All the best

Thanx & Regards
Srikanth

[salvelinus]

My take on escudolm's request was that his master query runs a number of sub queries, and they're all saved as queries, and that he wants to eliminate all the saved queries but one, running the rest in code. Is that correct, or do you just want to run individual queries, in which case Srikanth's method is good?

[escudolm]

Well, i want to put a lot of data into one table. Certain data isn't in the master table, so i will be moving it into one big one that has everything. So, i guess i got to get the data from some other tables. how can i move the data into one table before i delete the queries?

[salvelinus]

Well, that's a different question from your original one. You could run a series of INSERT queries. Is this something you'll be repeating, or a one time deal?

[escudolm]

Well, when she looks up a file, she has to open a few other tables to see all the data she wants to see. She has queries for everything. Queries running off of queries inside of queries. She wants to be able to open a table and have all the info in that table, instead of looking through multiple tables. Does this make sense? She wants to be able to delete some of the queries holding all this other data to clean it up. Is this feasible?

[escudolm]

So, im guessing all these queries are just pulling the info that she wants. i got all the queries listed out. how can i put code in place of the queries? i know what to pull out of the tables based upon the queries, but where would i start? don't know sql at that well. could i do it in vb maybe?

[BillBoeBaggins]

The tables and queries seem like seperate issues. Why not create joined queries and link together the data from all the tables and make one table using that, and yes you will need to know SQL for this. Not sure what you can do in VB that would help the situation. But if you build the joined queries that displays the information as she needs it you don't really need to make another table.

There is no benefit to moving the SQL Queries to VB since all they will be will be embedded SQL inside of VB. So your just moving your messy queries to the inside of a code page instead of the Query List Pane. Which makes them harder to fix, diagnose and change at will especially if you rely on the designer view. You will not have an easy time of displaying the data to Her/or User in VB without the use of Query objects in Access.

Questions:
Are all the little queries she wants gone standalone?
Is the big master query dependent on these little queries?
What do you expect to gain in VB that can't be done with Access objects?

[salvelinus]

BillBoe,

I'm usually in favor of using SQL in code vs. saved queries. I guess stored procedures are better yet, but that doesn't seem to be an issue here.
Esculdom, why does your boss care how many queries there are?
If I understand correctly (never a sure thing), she's viewing partial data somewhere, then has to view other data separately, and wants to see it all at once instead?
SQL could probably (depending on what she wants and how the db is set up) give this to her, but if you have a lot of tables, this could get difficult and unwieldy. A lot depends on the db setup. Are tables related? It may be that the db design needs to be revamped.
We really need more info from you. How many tables involved? What is their structure? How are they related? What is it she wants to view?

[BillBoeBaggins]

Salvelinus,
Not to turn this into a debate, but I like to stick my time tested queries SQL into code "embedded" but things in progress I just access the object model and grab the SQL. I guess it just depends on how fluent you are with SQL vs How dependent on the designer you are. Its just annoying breaking up SQL strings in code and inserting line breaks for something that might change again soon. But each method has a whole list of caveats.

[salvelinus]

No, BillBoe, your way is fine, just not mine. I'm more used to coding, think it gives me more flexibilty and error checking abilities. And knowing SQL is always a good thing, not that I'm an expert.
Using code will also help you learn SQL, which will be necessary if you move beyond Access.
Not saying you don't know, or aren't advocating learning, SQL. But a saved query is dependent on fixed or user supplied parameters, without, AFAIK, validity checking on the parameters.

[escudolm]

Yes, she is viewing data on one table and then looking at some other data in another table. There are a lot of queries(40+) and a lot of tables(30+). Well, to me its a lot. Ok, sorry, there is a master TABLE that has all the information right now, pulled from 22 different queries. Ranging from name, address, etc. I got the filters that she wants for the table. So basically, the master table's info is from all the queries she has going. She wants me to, I guess, just pull the data from the tables holding the info, instead of having all the queries running. I really dont know why she wants to do this. Something about, to get rid of a lot of queries and to speed it up. Would I create another query to do this or what would be the best way, if any?

[gigemboy]

well if she is using one master table, then it is possible to put everything inside of one query using SQL, using lots of JOIN statements.... granted, it would be a very long statement...

[salvelinus]

It sounds like it would be better to redesign the db. I can't think of any situation where it would be appropriate to pull data from 30 tables into a query or another table. 4 or 5 is the max I'd want to handle.