I was asked the following question:
You made a comment in 'Worse Than Failure' The Sharon System and i was wondering if you could elaborate on it. You said
"Access works very well with controls and forms bound to queries/views/whatever against SQL Serer, etc. Of course you'd never want to base any kind of form directly against a table. "
I work a lot in Access Front End against SQL Server using unbound forms and I was curious why you would never want to base any forms directly against a table.
You'd never want to work directly against a table whether it resides in Access, SQL Server or elsewhere.
There are basically about three types of tables. (Of course I'm generalizing.) Master aka lookup tables holding data such as transaction types, provinces or states, activity codes, departments, locations, etc, etc with typically dozens or a few hundred records. Then there are tables holding data such as customers, units, employees and such with hundreds, thousands or tens of thousands of records. Finally transaction tables with tens or hundreds of thousands or more records.
You would always want to sort lookup by something such as alphabetically or by province/state or frequently by a sequence number. So these would need a query to at least sort by this value. A sequence number would put the more commonly used items at the top of the list or combo box. For example in a list of payroll transaction types RT - Regular Time, OT - Over Time and DT - Double Time would come first and then other values. However typically you'd use a continuous form and display all the records. I also would sort inactive records to the bottom.
A customer, employee or equipment table with a few thousand records you might or might not use a continuous form with some sort of lookup fields at the top to allow you to quickly shorten the number of records. Or you may display the form with no records in the initial view but as soon as the user puts in a lookup value you'd display a limited set of records. The Like operator in a query becomes very useful.
What is especially interesting is how quickly a few characters of the beginning of the first and last name narrows down the records. In a sample database of 10,000 names even just J for the first name and S for the last name gets the number of records down to a few dozen. Jo and Sm narrows it down to three or four records.
Finally the transaction tables. Here you really would want to start limiting your data by a date range. For example to the start of the last pay period if hourly transactions. Then allow for more filters on employee name, job number or other.