Table or query as form data-source? (1 Viewer)

[JohnGo]

Regarding 'locking' in a multi-user environment I was wondering: Is it better to use a query as a data-source or a table??
If people scroll through rows with a table as data-source does it lock the total table? or does Access only lock the table when you add a record?

Factually it looks like access generates a query itself when you use the form wizard and select tables within that wizard...

Your ideas on this matter please

 

[maxmangion]

Ideally you should base your forms on queries rather than tables.

 

[quest4]

JohnGo, I like the table much better than the queries. Actually it depends on what you are trying to do. I personally use queries mostly for reports, dates and calculations stuff. If you need it you can always build a query in the datasource query builder. Just a little something to think about. hth.

 

[JohnGo]

JohnGo, I like the table much better than the queries. Actually it depends on what you are trying to do. I personally use queries mostly for reports, dates and calculations stuff. If you need it you can always build a query in the datasource query builder. Just a little something to think about. hth.

Quest4, I didn't use the tool Access for a long time but reading the first answer I remember Tables will be locked when be used in forms directly. If a user opens two forms, both referring to the same table the results are very bad. One form will show the pointed record of another form.

i guess using queries is a lot better.

 

[Mile-O]

You should bind queries to forms.

• The more records in a table means a form (bound to a table) would take longer to load as its recordcount grew.
• When you bind a form with a table then you are calling every field into a form when you most likely would not need every field at one time.
• With a query you can load a single record into a form ensuring a faster loading time and limit the query to the fields you need and nothing more.
• You can't sort a table the way you can with a query.
• You can perform calculations in a query and use them on the form.
• Using queries can reduce the risk of record locking problems.

The same applies to objects where you should use a query as their RowSource and not a table.

 

[Pat Hartman]

I'd like to add to the list.

• Queries allow you to join to your lookup tables rather than relying on poorer techniques like DLookup()s
• ALL access to tables is via recordsets built by queries even if you are just opening a table in datasheet view so you might as well take some control over the activity.
• If your datasource is an ODBC table, a query with selection criteria is the ONLY way to populate a form. Linking directly to the table simply opens a pipeline to the backend database and Jet will keep pumping data accross the LAN until it is all local. This really upsets DBA's (for good reason).