Changing the source table of a query via VBA

mtairhead

Registered User.
Local time
Today, 03:52
Joined
Oct 17, 2003
Messages
138
If I have several tables with the same fields, but different data. Can I use VBA to change the table source of a query?

Thanks
 
You could if you built the SQL string with VBA. However, this would be inefficient. It is better to have separate queries for each table. Then if you want to use the same form's RecordSource, you can swap queries in the form's RecordSource. Doing it this way will save processing time since you can use stored querydefs which are pre-compiled. Queries created as strings in VBA must be compiled each time they run. This slows them down and contributes to database bloat.

The real problem though, is your database structure. You should not have multiple tables with the same columns. You are using some piece of "data" to segregate other data in individual tables. This violates standard practice. It is better to have a single table with a column that includes the data you need to selectively pull sets of data from the single table. Using this method, you have 1 query, 1 form, 1 report and you can use parameters to control which set of data you want to work with.
 

Users who are viewing this thread

Back
Top Bottom