TSQL programming with MS-Access

Pros

Fast

Doesn’t corrupt

Can handle a lot of data (unless you are using MDBE)

Some DDL chores are easier in access than enterprise manager

Gives a MS-Access front end to SQL6.5

TSQL is a more expressive SQL dialect than JET SQL

Cons

Hard to use VBScript functions, VB functions can’t be called from inside a stored procedure.

Impossible to figure out how to change connection timeout

Impossible to figure out how to globally turn off the ‘rowcount 10000′ feature.

On account of the previous 2 problems I run almost all of my queries in Query Analyzer

Requires extra step to graphically update joined tables

Screws up the formatting of TSQL– default formatting isn’t very readable

Can’t graphically view common constructs like CASE/WHEN/THEN

Defaults to creating a table valued function instead of a stored procedure

Have to split data accross lots of tables if you are using MSDE

Heterogenous joins between local and linked tables have lousy performance (as compared to joining local tables to linked tables in an Access .mdb)

Linked tables have cumbersome names, like SERVER_DATABASE_DBO_TABLENAME, which is a pain when the “SERVER_DATABASE_DBO_” part never changes! Why couldn’t they have used a shorter alias?

Can’t see date/time a table or procedure was created (although the data can be seen in enterprise manager!)

Tricks

To graphically update the join of 2 tables, create a view and then update the view.