Steven W. Erbach
serbach at new.rr.com
Wed Mar 10 15:43:10 CST 2004
Francisco, >> 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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Regards, Steve Erbach Scientific Marketing Neenah, WI 920-969-0504 Security and Virus information: http://www.swerbach.com/security/virusinfo.htm