Forum Moderators: open
Many responses were very good and I have had over 10 years experience with ASP/COM/ADO/IIS/Access/SQL and would like to firm up the issues and suggestions:
1. use OLEDB/COM versus ODBC/ADO/COM. OLEDB is native and usually 65% faster than ODBC. There are usually a few glitches a programmer has to overcome when switching an IIS web application from ODBC (which uses the server registry) versus OLEDB which does not.
2. do performance analysis of the database design, understanding reads, updates, versus inserts.
3. Indexing is critical. Indexes should and IMHO must exist on columns on both sides of a JOIN or comparison in the WHERE clause, and yes, even the ORDER BY column. A Primary Key is just another version of an Index. In SQL the ALTER TABLE .. PRIMARY KEY UNIQUE.. versus just an INDEX NO DUPLICATES is good enough for beginners to intermediate. Database level constraints start to play a role with PK and FKs. Some may write their constraints into the application (level) versus at the database level (e.g. the same applies to security -- at the application, inet server, database, operating system, router, wires, etc.)
4. Use SQL (pass-through) in Access instead of QBE, and test..test..test. Use T-SQL Help as your guide, and have a developer or academic version of SQL Server on your desktop for testing. Nothing wrong with being a SQL Server expert and doing access sql-passthrough programming.
5. Do not use Access only functionality especially when planning growth since items like PIVOT, TOP where not in SQL 6.5 unless you set ROWCOUNT, etc. Now SQL 7.0 and SQL 2000 and Access 2000/2002 are headed in the same direction, their cross-functionality is starting to show promise.
good luck and keep up the faith.
Be sure to read Marcia`s WebmasterWorld Welcome and Guide to the Basics [webmasterworld.com] post.
Thanks for your information on Access. It´s always good to have another (not that I know anything about it myself) knowledgeable person on board. I´m looking forward to hear more from you.
Andreas
Welcome to the forum, good post
