Arthur Fuller
artful at rogers.com
Tue Nov 2 12:40:38 CST 2004
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