SQL Server > Migrating from Access articles: • Migrating or Upsizing Your Microsoft Access Database and ASP Code to SQL Server
|
Return to index of articles
Migrating or Upsizing Your Microsoft Access Database and ASP Code to SQL Server
Category: SQL Server
Category: Migrating from Access
So, you've got a site or application
running on NT using Active Server Pages (ASP) and an ADO database connection
to a Microsoft Access database. The time may come when you need to convert
to SQL server, either due to increased traffic or a move to a new ISP that
doesn't support MDB files. This is not meant to be comprehensive but is
based on my own experiences and hopefully will be helpful to those in the
same situation.
There are two major procedures involved:
Upsizing or migrating the database and its contents
from Access MDB file to SQL Server
Updating your ASP Code to reflect syntax differences between
Access and SQL
Upsizing or Migrating the Database Structure and
Contents
1. Make sure you have the SQL server info you
need from your ISP
This will include the user name and password for the SQL server, and the
name or IP address of the SQL server machine (e.g. sql.provider.com) -
the SQL server may or may not be the same machine as your domain is hosted
on.
2. Install the Microsoft Upsizing Wizard for Access
This is available for free download from Microsoft here.
Download it to your hard drive and then run it. The install only takes
a few seconds if your machine is quick.
3. Create a DSN for the SQL Server
This involves going to Start -> Settings -> Control Panel
in Windows and going into your ODBC settings. Rather than repeat the instructions
just check out the details in the readme.txt and help files that come
with the Upsizing Wizard.
4. Upsize it baby!
Run Access (I think it has to be Access 97, I believe that's what the
Upsizing Wizard works with and that's what I used.) Open the MDB file
you want to upsize. Then go to Tools -> Addins and choose Upsize
to SQL-Server.
Changing your ASP Code
to reflect differences between Access and SQL
5. Change your connection string
Hopefully you have your connection string in an include file so
you only have to change it in one place. If not, you'll have to change
every occurrence. Your original connection string probably looked something
like this:
dbName.ConnectionString = _
"DBQ=\path\filename.mdb;DefaultDir=;Driver={Microsoft Access Driver
(*.mdb)};"&_
"DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;"&_
"PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;;"
You will need to change it to something like this (there are more items
possible in the string but this works for me):
dbName.ConnectionString = _
"Driver={SQL Server};DSN=dsnName;UID=sqlUserID;PWD=sqlPassword;"&_
"DB=dbName;Database=dbName;SERVER=sql.servername.com;"
6. Fix your SELECT statements
A few things I discovered that I had to change in my SELECT statements
when I'm getting recordsets from the database:
- change fieldName=true to fieldName=1 (this is the number
one not a lower-case L) -
for example if your SELECT statement was "SELECT * FROM tblName
WHERE tblName.ShowOnSite=true;" you would change it to "SELECT
* FROM tblName WHERE tblName.ShowOnSite=1;"
- Where Access uses date() to represent today's date, SQL uses getdate(),
so change date() to getDate() -
for example change "SELECT * FROM tblName WHERE tblName.ExpiresDate
> date() ;" to "SELECT * FROM tblName WHERE tblName.ExpiresDate
> getdate() ;"
7. Fix DELETE statements if necessary
One of the pages I had was crashing on this statement: "DELETE
* FROM tblName WHERE..." It turns out that while Access allows this
syntax, SQL returns a syntax error because of the asterisk (*) so you'll
have to change this type of DELETE statement to: "DELETE FROM tblName
WHERE..." (no * in there at all)
8. Fix/change Cursor Types where necessary
One of my pages was crashing with this error:
"Multiple-step OLE DB operation generated errors"
By commenting out sections of the code I determined that it was crashing
on display of a Memo field, all the other fields displayed fine.
On some sort of divine inspiration I tried changing the CursorType setting:
originally it was:
rsName.CursorType = 0
Changing it to 1 or 3 got rid of the OLD DB error. Don't ask me why.
More info on CursorTypes can be found here
if you want to learn more.
If you have any suggestions or questions feel free to email me! tech@spamblocktenvolt.com
(remove the spamblock)
6/1/2002
|