Microsoft SQL Server to MS Access conversion -

Microsoft SQL Server to MS Access conversion

<- ->

> > >

Assuming you are using MS Access 2000 or 2003, the MS SQL Server database to MS Access database conversion instructions are below. This proceedure is designed to take either the SQL Server Presto Template database, or a working live Presto installation SQL Server database, and convert it to MS Access.

The motivation for this is usually to place a moderately active website that has been developed with a Presto MS SQL Server database onto a lower-cost ISP windows server hosting account.

Note: this approach enables import of "autoincrement" fields, which the MSSQL server doesn't export via its Data Transformation Services system. It is very difficult to apply an autoincrement feature to a MS Access field after its data has been imported, so this problem is avoided in the proceedure below.

Convert database:

Ensure that your local computer has an ODBC connection to the Presto MS SQL database that you want to convert to MS Access. Lets call this connection "ACME" for the purpose of these instructions.
Create a new MS Access database.
Use the MS Access file menu "Get External Data","Import ..." options to import tables from the MS SQL database. When selecting the database to import, go to the "files of type..." menu, and select the bottom item, "ODBC databases ()".
On the "Select Data Source" window, select the ODBC connection (e.g. ACME). It probably is under the "Machine Data Source" tab.
Enter any required user ID & password info to gain access to the SQL database.
Select all tables for import EXCEPT the dbo_dtproperties, dbo_sysconstrtaints, and dbo_syssegments tables.
Now, back in MS Access after the data import, rename all tables so they don't have the "dbo_" prefix.
Create or adjust the ODBC connection to point to the MS Access database.
You should now be able to login to the website with the newly converted database.
In the MS Access database's "setup" table, set the sql_type field to 0, which enables the use of MS Access. You can change it by directly editing the setup table; or you can use the presto_checklist.cfm script to change the setting.
Run the following code to reestablish primary keys and search indexes in the MS Access database.
presto/backend_website/admin/mssql_to_msaccess.cfm