This document illustrates how to convert a MS Access database into a MS SQL Server database. The timeLines database, timelines.mdb, for the timeLines installation at skybuilders.com will be used to illustrate this process.
|
Warning: After much headbanging and Google/Deja scours I have come to the conclusion that there is no practical way to import primary keys and their accompanying attributes, eg., indentity, into an MS SQL Server database. (There may, in fact, be ways to effect their importation in certain circumstances programmatically through some arcane API device, but that is beyond the scope practical use. - Read: Who has time to waste doing that kind of nonsense?) I was even unable to effect the transfer of primary keys when cloning one MSSQL Server based database to another of the same! Amazing, isn't it? Can anyone consider such limitations in a real enterprise product? Be prepared for carpel-tunnel agony as you retrofit priamry keys and identity settings through the MS SQL Server table design GUIs. This is a serious drawback to MS SQL Server.
|
I. Using the MS SQL Import and Export Data Wizard
II. Changes using the MS SQL Enterprise Manager
III. Setting Editing the timeLines db.inc File
I. Using the MS SQL Import and Export Data Wizard
To begin click on the start bar button: Start > Programs > Microsoft SQL Server > Import and Export Data.
This opens the MS SQL Server Import and Export Data Wizard. Click the Next button.
Select a Data Source of type Microsoft Access and type in the Access database name or browse to to the Access database file. Click Next.
Select the relevant host name of the computer hosting the MS SQL Server.
Where it says Database at the bottom of the
previous illustration select <new> (<default> is shown), bringing up the Create Database display. Now enter the new database name and optionally increase the Data File size.
Clicking OK from the previous illustration will return you to the original interface. Observe the newly inserted Database Name.
Copy tables and press Next.
Press Select All (tables) and press Next.
Run immediately and click Next.
Click the Finish button.
You will then see the following when the conversion has completed. Click the Done button to exit the Export Data Wizard.
This concludes the process of exporting a MS Access database over to the MS SQL Server.
Back to top of page.
II. Changes using the MS SQL Enterprise Manager
Now that the Access database has been migrated into the MS SQL Server database space it must adjusted. We must add the IUSR as a database user associated with the newly imported database. We must also grant the IUSR read and write privileges to the new database.
This must be done through the MS SQL Enterprise Manager: Click Start > Programs > Microsoft SQL Server > Enterprise Manager and expand the Databases folder to enummerate the contents of the database space.
Expand the new database folder, shown in the previous screenshot as sBtL_skybuilders_com (the new MS SQL Server database name), to diplay Users, highlighted below.
Right click on Users, selecting Properties, choose the Login Name of the IUSR, HOSTS5\IUSR_HOSTS5 in this case, and set the database privileges accordingly, granting read and write privileges (db_dataread and db_datawrite) to the IUSR.
This concludes the granting of read and write privileges to the IUSR.
Back to top of page.
III. Editing the timeLines db.inc File
Coding Note: You will need to modify the connection object string to reflect the change in database type.
Roughly, you connection object coding changes will affect the following, which refer to an MS Access connection object.
sDBServerType = "MSAccess"
Set oDBConnection = Server.CreateObject("ADODB.Connection")
...
sDBFileName = "timeLines.mdb"
sDBPath = sOrgPath & sDBFileName
...
oDBConnection.Open("Driver={Microsoft Access Driver (*.mdb)};DBQ=" & sDBPath)
The MS SQL Server connection object code should resemble the nex sample.
sDBServerType = "MSSQLServer"
Set oDBConnection = Server.CreateObject("ADODB.Connection")
...
sMSSQLConnection = "Trusted_Connection=yes;Provider=SQLOLEDB; \
Data Source=HOSTS5; Initial Catalog=sBtL_skybuilders_com; \
User ID=; Password=;"
oDBConnection.Open sMSSQLConnection
Please note that bolded items, excepting the sDBserverType setting, are values particular to your own installation and will have to be changed accordingly.
* Please look near the end of the timeLines db.inc file for an example in context.
Back to top of page.
