Beginning Access 2002 VBA (Programmer to Programmer)

One of the great benefits of Access 2002 is its ability to interact with SQL Server. This process has been improved with each release of Microsoft Access, and Access 2002 continues the process. The Upsizing Wizard was introduced to Access in Version 2, but not actually included as part of the Access Menu until Access 2000. Over this period the wizard has been improved but is still limited in how it will move your database to SQL Server. In this section we look at how to move your Access file to SQL Server 2000.

Before you Start

Of course, before you begin the move to SQL Server, that is running the upsizing wizard on your MDB file, there are a few things to do:

• Make sure you backup the mdb file.

• Ensure you have administrator privileges in both Access and SQL Server in order to upsize your access database. Administrator privilege is required in SQL Server, as you will be creating many new server objects.

• Check the data in your Access tables. It is with dates that most upsizing problems occur.

• Access and SQL Server use different data ranges. For example, 01/April/100 is a valid date in Microsoft Access, but not in SQL Server. The date range in SQL Server covers the period 1 Jan 1753 to 31 Dec 9999 while Access permits date values in the range 1 Jan 100 to 31 Dec 9999. We know; you validate all the dates entered into your application. Just as well because if the Wizard hits one of these dates in a table then the table will not be upsized .

• Ensure each table has a unique index, otherwise it will be read-only when upsized. Each table should have the index set. If you have established Primary Keys, then you should be OK. This one tends to catch out many Access users moving to SQL Server for the first time.

• Make sure you have no spaces in table names. Spaces in table names result in you having to enclose the table name in square brackets when referencing them in SQL Server. Just another added hassle you can do without.

A really useful tool is Upsizing Pro which is available from: http://www.ssw.com.au/ssw/UpsizingPRO/Default.aspx . Upsizing Pro is used before you upsize and highlights any problems you may have with your mdb file before you actually upsize. This tool can save you hours of work looking through tables and code, and is well worth investing in.

The upsizing wizard is designed to move your tables to SQL Server by hook or by crook, everything else, forms, reports and queries are secondary to that main task, and the result can be a bit hit and miss as we shall see. Let's get right to it, and as we go, you will see some of the problems that can arise when moving databases.

Try It Out-Using the Upsizing Wizard

1. To run the upsizing wizard from the Access main menu select Tools Database Utilities Upsizing Wizard to begin the process.

2. The first step in the process requires you to either create a new SQL Server database or work with an existing database. On this occasion we will create a new database. Accept the default Create new database and click Next .

3. You will then be prompted to select the name of the SQL Server instance to use to store the database. In the majority of cases, you will have a single instance, so you can simply accept the default database. If not, always check that your connection is to the correct instance of SQL Server on your machine. At this point, you will also be asked to select the security model for the server data, either Windows Authentication or SQL Server security. Unless you have a good reason for using SQL Server security, you should select Use Trusted Connection . A trusted connection connects to the database as a Windows Administrator. For this example, I am logged into my machine as the Windows Administrator. You may accept the suggestion or enter a new name for the database. Click Next to proceed.

You are now required to select the tables to be upsized to SQL Server. In this case, we will select all the tables with the exception of tblPasswords . Remember in the new ADP you will be using SQL Server security, so this table is no longer required, as it is an Access security table created in this example database. Use the arrow buttons to move the required tables from the Available Tables list to the Export to SQL Server list. Once done click Next to proceed.

In addition to the data the upsizing wizard can also export several table attributes:

• Indexes

  Microsoft Access table indexes will be upsized to SQL Server Indexes. You may then be required to manually adjust the index once the process is complete, as in the case of Primary Keys, you should ensure that the index type used is an SQL Server Clustered Index. With a Clustered Index your data is stored in a predefined order, for example, by Town, assuming the index is set on the Town field. As a result, each table can have a single clustered index. This ordering of the table data leads to very fast retrieval as SQL Server has to read fewer rows when searching. A Non Clustered index simply maintains a key that points to the actual data. When retrieving data from SQL Server, must first look up the keyed indexes on your tables.

• Defaults

  If you have used default values within the table design, for example, Date() , to insert the current date, the wizard will attempt to upsize them to their SQL Server equivalent. Note that a failure to do this results in the entire table being skipped and not exported to SQL Server.

• Validation Rules

  Again the upsizing wizard will attempt to upsize any table validation rules you have set on the database tables. Validation rules are often used at the table level in Access to restrict the data that can be added. For example, with a field Order_Date , the validation rule could say that the Order_Date < =Date() . That means that you cannot enter orders for dates earlier than today. Validation rules such as this will be upsized to SQL Server constraints. If you want your validation rules upsized then accept the default choice and let the wizard take care of things.

• Table Relationships

  SQL Server 2000 can now use both Cascade update and delete options, long available with Microsoft Access. DRI, Declarative Referential Integrity, is almost the same method used by Access to enforce relationships. Again accept the default choice.

• Timestamps

  A SQL Server Timestamp field is added to your table and updated by SQL Server whenever a change to data takes place. If you accept the default, a timestamp field will be added to any table containing a single, double, or OLE data type.

5. The last option in this screen permits you to instruct the wizard to simply export the table structure and leave the actual data behind in Access. The data would then have to be manually imported into SQL Server later. On this occasion, we want the tables and the data, so accept the default and click Next to continue.

6. This next screen in the upsizing process also provides you with several options:

   Create a new Access client/server application

   This moves everything into the world of SQL Server. Tables will be moved to the server and a new SQL Server database will be created. All user objects, forms, reports and any VBA code will be migrated into a new ADP on the client side. You may also use the Browse button to select a location for the ADP file or accept the default. On this occasion, I have created a folder for the database and simply navigated to that location. If you have created a folder for the ADP, then navigate to it now.

   Link SQL Server tables to existing application

   In this case, you simply create a link to the SQL Server database tables and your file type remains the standard Access mdb file. All local tables will be retained with the prefix _local added to the table name and stored in the mdb file.

   No application changes

   Export the tables, but make no changes to the Access application. This option creates the table structure on SQL Server, but little else. No changes will be made to the mdb file, that is, links will not be made and no connection will be provided to SQL Server.

7. Click Next to proceed. The final screen in the process asks if you want to Open the new ADP file or Keep the MDB file open . Select your option, and click Finish to generate the ADP. The choice of which option to use depends on what you want to do. If you want to begin working with the new ADP file right away, then select Open the new ADP file , otherwise select Keep the MDB file open .

8. In addition to generating the ADP files Access will produce an Upsizing report. Make sure you print this file as it contains a record of all changes made during the process, including information on any objects that did not upsize. Print this out NOW . The report will be saved as an Access Snapshot file in the same folder as the ADP file. You cannot save the report using the Access Menus, so it really is a case of print it, export it, or lose it.

What's New in your Database?

From here on in, when we refer to a database we are referring to the database as a SQL Server 2000 database and an ADP file, as opposed to Microsoft Access. When you reopen the database, you will find that you have left the world of Jet behind and that there are several new things you will need to learn or relearn. The database window, while looking the same, will contain many different objects, and this is apparent when you come to create queries. Queries are now replaced by SQL Server objects, including:

• Stored ProceduresA stored procedure is precompiled SQL stored on the server, not within your ADP front-end application. Access 2002 permits you to create stored procedures using graphical tools. In addition to the procedure being precompiled, SQL Server will create an execution plan, which gives the fastest way to get at the data. Each time the stored procedure is executed, this plan is used. Thus the speed of data retrieval increases .

• ViewsA view is a window into data. Usually used to shield users from complex SQL statements and also to hide specific data; for example, you could create a view of a salary table which hides the manager's salary from other employees . A view can be treated just like a normal database table.

• User-Defined FunctionA user-defined function is simply a function created by you. SQL Server comes with hundreds of system functions such as GETDATE() . New to SQL Server 2000 is the ability to create your own functions.

• Database DiagramsThe best way to explain this is to equate it to the Access Relationship window on steroids. From within this tool, you can actually create the structure of entire applications. Existing relationships in your upsized database will not be visible until you create a new diagram.

• ActiveX Data Objects (ADO)DAO will no longer get you by. You will have to move all your code to ADO when working within an ADP. ADO is discussed in Chapter 21.

The following table also highlights some areas that also tend to catch out the Access developer moving to ADPs.