unbound form SQL query question

[hooks]

I have a unbound form with unlinked tables ADO.

My question is about sql and combo boxes.

Which is better

Using a table as the source of the table
rs.Open ("SELECT * FROM tCustomers)

Or using a stored query off of the same table
rs.Open ("SELECT * FROM qCustomers)

I would assume that using the table as the query source would be faster. Although after reading a whole lot in this forum I gather that queries are faster and save the database some size.

How would i do the equivilent of

Dim db AS DAO.Database
Dim rs AS DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("qCustomer")

in ADO with unlinked tables

Thanks

 

[Pat Hartman]

If you're really worried about efficiency, link the tables and use a bound form.

If your tables are Jet, you are better off using DAO since DAO is optimized for Jet. You should also use store querydefs rather than SQL strings. If your tables are ODBC, then ADO may be better and it doesn't matter if your queries are stored or strings since they are passed through to the server in both cases.

 

[hooks]

I don't understand how bound forms would be more efficienct then unbound forms. I am in the process a creating a rather large (for me anyway) database for a lumber company that will have 7 users logged in at once. There will not be a whole lot of traffic so i figured access would handle it. I am just trying to be as perfect as it can be.

 

[Pat Hartman]

I don't understand how bound forms would be more efficienct then unbound forms.

Well they are. The code that Access uses to populate the form and manage the update process is optimized for that purpose. By using unbound forms you are duplicating everything (if you do it properly) that Access already does!

Linking tables allows Access to cache information about the table so that Jet doesn't need to get this information at runtime EACH time you open a recordset based on that table.

7 users shouldn't be any problem.

 

[hooks]

Thanks Pat.

That makes a lot of sense
So Pat do you always use bound controls?
What version of Access do you use?

 

[Pat Hartman]

I always use bound controls unless there is a real reason not to. Occassionally users get really hung up on having a spreadsheet like interface. Since that is not possible with properly normalized tables, an unbound form would be necessary. But first I make sure that the paying customer understands the cost differential. Most see the light.

I am using 2003.