In this article, we will simulate migrating a relatively small but established business from Microsoft Access 2000 to SQL Server 2000. There are a variety of reasons a business might wish to perform such a migration. Legacy queries, forms, and reports will be taken into consideration, as well as the appropriate way to handle database files.
Growing businesses often come to a stage where they need to migrate their MS Access database to a SQL Server. The reasons for this, apart from the natural growth in the size of the database, are other considerations which may be summarized as follows:
User base is increasing
Internet access is increasingly demanded
Security has suddenly become a issue
Ready availability is desired.
In such cases, the natural course is to migrate to a SQL Server, maybe via MSDE in the first pass. However, the business has developed a large number of queries, forms, reports, and even placed some of their data for Internet access via DAP. Obviously, it is too much to recreate all this information in the short run. The desirable path would be to retain all those user objects including the queries. You would then export just the tables which can safely stay in the SQL Server, but can be accessed via the network.
In this migration simulation, an access database file, Migrate1.mdb has some five tables, a query, a form, a report and a data access page hosted on the intranet. The tables will be migrated to SQL Server and then linked back to the application. It is shown that user objects on Access are usable and updates to the SQL Server tables are reflected in the access objects. The down side to this is the increase in network traffic.
The starting point is the MS Access database that is to be migrated to SQL Server 2000. For the sake of demonstration this database contains five tables (a subset of the Northwind.mdb) with the relationship as shown in the picture.
The MS Access file has other objects as shown based on the following query. This query has data from all the related tables.
SELECT Employees.LastName, Employees.FirstName, Customers.CompanyName, Customers.ContactName, Products.ProductName, Orders.RequiredDate, [Order Details].Quantity FROM (Customers INNER JOIN (Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) ON Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID;
Migrating from MS Access 2000 to SQL 2000 Server
A form based on the above query is shown below. This form has two sub-forms, Orders and Order details. For each employee it looks up the orders they have taken. Also for each order the order details are shown.
A report based on the query is shown here. This is based on the query grouped by Company Name as shown.
This is a Data Access Page hosted on the intranet server. The DAP is saved to the web root and it can be accessed by the intranet.
As a first step to migration, a database Migrate1 is created on the SQL Server, XPHTEK as shown in the next three screen shots. This is a new database from scratch, accepting the defaults.
The data file name is Migrate1_Data and its path is as shown. Since this is only a demo, the size and growth are all those of the default.
The transaction log file name, size and allowed growth are as shown, again set to the default values.
The database Migrate1 is created as shown. Presently there are only the system tables. It has only one user, the dbo at this time. More can be added later by the dbo.
There are several options for exporting the tables from MS Access to SQL 2000 Server. One could use DTS (Data Transformation Service) to transfer all the tables in one go. You could also export tables to SQL Server using an ODBC connection. This may entail sending tables one at a time to the SQL Server.
Establishing a Connection for this Database
In this tutorial, for example, each table was exported using an ODBC MS SQL Server setup whose details are as shown below. Only the final step of the connection steps is shown here, but you would start from the Control Panel ->Administrative Tools->ODBC Manager. You would use a SQL Server driver. The System DSN created is called MigrateAccess as shown here.
After the connection is established, you could export all the tables to the SQL 2000 Server as shown. This is a montage of the database plus one of the tables showing the data. As soon as the data is exported, there is no need for the data to remain on the MS Access file. One could delete those tables. Now there are no tables on the Migrate1.mdb, but there are objects, queries, forms, reports, and so forth. At the moment they are orphaned.
In this step, using import data in MS Access, you can set up links to the MS SQL Server Migrate1 database using the same ODBC connection which is already established. In this way you will link back all the tables and some system tables that will come by default as shown. As we saw in an earlier tutorial, the tables will come with the prefix dbo_ added to the table name. For example, the employees table on SQL Server will come over as dbo_employees.
Renaming Links
The SQL query previously established had table names without this appendage, and will not function correctly for these tables even though the same data is present. It will be necessary to rename the tables to their original name. For example, you will need to rename dbo_customers to customers. The MS Access database with the new linked objects will appear as shown, retaining all the other UI objects.
Now you will see that all the objects in the MS Access would function normally, as if nothing has happened. The next picture shows the DAP on the intranet after migrating the tables.
Updating Data
Updating the data is carried out at the SQL Server either via programming, or directly running queries, or stored procedures in the query analyzer. For example, in the above report for Customers, the city corresponding to the Company 'Alfreds Futterkiste' is 'Berlin'. By running the following query the city name 'Berlin' can be updated to 'Julich'.
UPDATE Customers SET City = 'Julich' WHERE (CustomerID = 'ALFKI')
This automatically updates the report since the linked table is updated as shown.
Summary
The tutorial describes migrating an MS Access database to SQL Server 2000. Although the export function of MS Access was used for exporting the data to SQL Server, the Data Transformation Services on the SQL Server can also be used for the transfer. The user defined objects on MS Access continue to function transparently, as if the data existed in the MS Access application. Since it is on the SQL Server it can be accessed over the network from multiple clients. Data security is now provided by the SQL Server.
Microsoft Access 
/ 14 
Distributed Queries in MS Access
