Form RecordSource - Table, Query or SQL?

[DavidSemon]

Which is "generally accepted" as better: using a table, query or VBA SQL statement as the RecordSource for a form? Working on a project that uses all three and wondering if it worth the effort to make them all the same.

Thanks.

 

[boblarson]

1. If you use a saved query, it is better because Access can pre-compile it and optimize its run.

2. VBA SQL for most occasions should not be much different than #1, with the exception that it can't be pre-compiled and has to do so at runtime.

3. A table should not be used, but can be although you don't get any sorting abilities and have to "take it as you get it." A sort is not guaranteed through here, so if you think you are getting records in any particular order, it really isn't guaranteed that they will be in the same order every time.

 

[missinglinq]

Well stated, Bob! And if you have a form that only needs one table, make a simple query using all the fields of the table, then use the query for your record source! You have what you need, plus the advantages of the query, and it takes less than a minutes to do!

 

[DavidSemon]

Thanks for the superb explanation and information. If I may, two related questions:

1. If I change the recordsource of a form programmatically when it is open, do the benefits of using a saved query still hold true?
2. If I will basically have a query for "every" table, should relationships be established among tables or queries?

Again, thanks for sharing your knowledge.

 

[boblarson]

1. Yes, but for a lot of them it probably doesn't matter if it is a saved query or an "on-the-fly" query.
2. relationships should be established for your tables regardless of having a query for every table. Having them can help you establish referential integrity so that data that isn't supposed to be there won't get there and, if you use cascade updates/deletes, if a parent record is deleted it will delete the child (or update - but not ADD mind you), the related records so that you don't wind up with "orphan" records.

 

[DavidSemon]

Again, thanks. Great information.