Converting Access Databases to SQL Server

The usual way to arrange a Microsoft Access application is to have one MDB (“front-end”) which contains all the program logic — forms, reports, queries — and another MDB which contains only the data (tables). This makes it possible to provide program updates without needing to manipulate the data also.

In this situation it is quite a straight-forward process to upscale the database to SQL Server. The program logic continues to reside in the Access MDB, but the data is stored elsewhere. Instead of the front-end MDB linking to tables in another MDB, it links to tables in SQL Server via ODBC.

However the major issue which complicates this is that Access is much more forgiving of table and field names than SQL Server. Access will quite happily operate with a table called “All Customer Code #s” whereas SQL Server will blench at the spaces and the special character (“#”). And yet if you have a non-trivial application, you can't necessarily go through every form, report and query and change all the names.

Fortunately Access has a feature which makes this easy to work-around. All objects (tables, forms, reports, queries) are referenced by their name, not an ordinal. This means that if you rename an object, the references to that object are not updated. This is necessary because any given MDB is not aware of all the other external locations that reference it.

Thus it is possible to rename a badly-name field, and then insert a substitute so all the forms and reports continue to function as expected.

Suppose you have a table called “All Customer Code #s” and a field in that table called “Commission %”. Both of these are illegal for SQL Server purposes and need to be renamed. The procedure is:

1. Make a backup of both the database and front-end MDBs.

2. In the database MDB:

   Rename the field to something SQL Server-compliant, such as “CommissionRate”.

   Rename the table to something SQL Server-compliant, such as “AllCustomerCodes”.

3. In the front-end MDB:

   Delete the linked table reference to “All Customer Code #s”.

   Re-link to the new table, “AllCustomerCodes”.

4. Make a query and add “AllCustomerCodes” to it. Add all the fields in the table to the query.

5. For the “CommissionRate” field, change the name of the column so that it reads “Commission %: CommissionRate”.

6. Save the query as “All Customer Code #s”.

7. Check that any forms, reports and other queries which relied on “All Customer Code #s” still work.

What has happened is that the tables and fields have been renamed and queries with the old names have been substituted. Access doesn't make a distinction between a table or query so when an object called “All Customer Code #s” is referenced, it accepts either.

If you have many tables and many fields with bad names, it would pay to write out the old names and the proposed new names before starting this process. All tables do not need to be done in one sitting, try one at a time.

Once the data MDB has been fully converted to SQL Server-friendly names, it is simple to upload it to SQL Server. Then the Access front-end simply needs to be re-linked to the new data source.



Copyright © 1996-2023 Cadzow TECH Pty. Ltd. All rights reserved.
Information and prices contained in this website may change without notice. Terms of use.
Question/comment about this page? Please email webguru@cadzow.com.au