[dba-SQLServer] Arthur's article

Djabarov, Robert Robert.Djabarov at usaa.com
Wed Mar 10 16:45:01 CST 2004

I thought I would just ignore this discussion, but when I saw "seasoned DBAs" reference, and I could not resist!

First of all, seasoned DBAs would not use MS Access for database/server administration purposes, NEVER!!!  Very rarely we use Enterprise Manager, OK?  I have stopped using EM on a daily basis since 6.5, needless to say in 7.0 or 2K environment.  How can a "seasoned DBA" even mention MS Access as a tool of choice, unless he/she is not really ... "seasoned"...???

Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator (SEASONED)
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Steven W. Erbach
Sent: Wednesday, March 10, 2004 3:43 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Arthur's article


>> Are you kidding me?  Access 2003 is sans ADP's!!!! :O. <<

OK, let me in on the joke. How else am I supposed to interpret this article by Arthur? (from builder.com newsletter, 9-Mar-2004):

Enhance Enterprise Manager with Access 

Seasoned DBAs know that one of the best development environments available is virtually free, and it blows away the classic SQL Enterprise Manager. (Note: It's only free if you have a license for Microsoft Office 2000 or XP; it doesn't work for Office 2003.) 

Most companies that use SQL Server also use Office, and most of those firms have licensed the version that includes Access. That means that you can create a Microsoft Access project (ADP) file, which provides direct hooks into SQL Server. 

Once you create an ADP file, you can do almost everything that you can from Enterprise Manager, including create tables, views, stored procedures, and user-defined functions. You cannot create and execute DTS packages, set up logins and roles, etc., because Access is not meant to replace Enterprise Manager for these tasks. 

Follow these steps to set up this tool (this only works in Access 2000 or Access XP): 

1. Create an ADP that points to the SQL database of your choice. Make sure that it connects successfully; this will depend upon several factors, such as integrated or separate login. 

2. Look at the database window in Access. If you're using Access 2000, you'll see separate tabs for Queries and Stored Procedures. If you're using Access 2002 (XP), these two tabs have been rolled into one. 

3. Create a new stored procedure or view. You'll discover that you have a wizard and a graphical environment in which you can drag and drop, double-click various columns from various tables, automatically join tables, and view what you're building as SQL rather than as a graphic. This is especially useful when building constructs such as SELECT TOP 10 .... Nobody said that graphical interfaces could do everything, but the beauty is that you can have it both ways. 

4. Create either a scalar or a table function. 

5. Follow the prompts and build something. Even if it's simple, it'll help you get a feel for the tool. 

Enterprise Manager is intended more for maintenance than for development. Several other companies offer alternatives, but at a substantial cost. Even if you can get a sign-off to license one of these third-party products, you should investigate Access ADP files before doing so. Chances are that you'll get almost all the functionality you want--and virtually for free. 

The real question is: Why did Microsoft kill ADP files in Access 2003? Possibly because they were so good that they made Enterprise Manager look foolish by comparison; or perhaps because Yukon is such a radical change that it would demand a complete rewrite of the relevant code. Your guess is as good as mine. 

Arthur Fuller has been developing database applications for 20 years. His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET. 


Steve Erbach
Scientific Marketing
Neenah, WI

Security and Virus information:

dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list