[AccessD] SQL Tables vs. Access Tables

Joe Hecht wrote:

>I may be picking up an assignment that sounds like an Access front end into
>SQL tables. I am waiting to hear from the project manager for the more
>details you all will ask me for.
>
>Is there any major difference between access tables and SQL tables I should
>be aware of?
>
>I think I do good table design, normalization and naming conventions.
>
>thanks
>
>Joe Hecht
>jmhla at earthlink.net
>28g
>
>
>  
>
I would suggest the following:

1. Design it in Access, where you're comfortable. Then use the upsizing 
wizard to move it to SS when you're ready. Make sure that every 
combo/list/form etc. uses a named query, NOT a select statement. To save 
yourself some issues down the road, where you would normally bind a form 
to a table, create a named query (even if it's nothing more than SELECT 
* FROM MyTable) for every form. This will save you a lot of hassle in 
the converted app.

2. Get a copy of SQL: Access to SQL Server, by our own Harkins and Reid.

3. Many of the things that we are comfortable in doing in Access are 
quite the wrong approach with SQL Server, in terms of best practices. 
This part cannot be communicated in a simple email message, but in 
general it reduces to this: the old practice of binding forms to tables 
is all wrong. Even if you substitute queries for the tables, if it's 
SELECT * it's all wrong (unless the table is very small). The number of 
times a user actually wants to see all the rows is relatively small. To 
give one example, a form showing Sales might start with a datasheet or 
continuous form that shows only the most important columns. Instead of 
showing all the sales, you might have 3 or 4 buttons on it, Sales30, 
Sales60, Sales90 and All. Each of these would change the data source to 
a stored procedure that says something like SELECT MyColumns FROM Sales 
WHERE SaleDate > GetDate() - 30. And so on. This results in lightning 
performance, which is usually demanded in a SQL Server app.

4. If all else fails, hire me. LOL.

Arthur