MSAccess --> SQL Server Migration

Question

MSAccess --> SQL Server Migration

Chris Gilbert

SSC Enthusiast

Points: 191
More actions
January 14, 2004 at 10:48 am

#59874

I have a dynamic site with all content coming from an Access database but I expect the amount of data to exceed the 2Gb Access limit eventually. I have been told that it's fairly easy to migrate from Access to SQL Server. Firstly, will this migration cause any problems with my ASP code? Secondly, how easy is it to migrate considering I have no SQL Server experience (although I am planning on learning)? Thirdly, is there a size constraint in SQL Server?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Chris Gilbert SSC Enthusiast Points: 191 More actions · Answer 1

Continuation of above post........... Sorry, I hit return by mistake. Any advice, links, general information you can offer on this subject is very much appreciated. Thanks in advance, Chris Gilbert.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

To 1: In most cases only the connection string will change.

To 2: Migrating the data itself isn't that difficult. Either the Access Upsizing Wizard or the SQL Server Import wizard will do the job. In both cases you need to look at the results they produced. A common pitfall are Yes/No fields in Access which will be transformed into bit columns in SQL Server. If you're using such columns make sure you place a DEFAULT values on those columns. Otherwise you'll get some strange, annoying behaviour. More work is required when you have complex Access queries that use Access Query specific features.

To 3: Take a look at the SQL Server Online Help (commonly refered to as BOL) for Maximum Capacity Specifications.

I suggest to actually migrate your data first and then come back for more questions, which surely will arise.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

There are a lot of little differences between the SQL that Access uses and T-SQL. For example, T-SQL only has RTrim and LTrim, so you will get an error if you query is uring Trim(). And T-SQL has no IIF function or NullIf function.

There are lots of other functions that exist in Access but not SQL, but I can't recall them all off of the top of my head. Just be ready to look for them if errors start popping up.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

justevolved SSC Enthusiast Points: 188 More actions · Answer 4

One major difference is the way date is handled, MS-Access uses # for dates while SQl server uses single quotes.

You have to dig for tons of dirt to get an ounce of Gold

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 5

I used the wizards to migrate and was caught out many times by the many small pitfalls. With hindsight, I think I might have been better off if I had built the backend from scratch.

yaweah Default port Points: 1489 More actions · Answer 6

I have done a lot of upgrade and I will tell you this common fact. Migrate you tables first and this is fairly easy by using SQL DTS or Access Upsizing Wizard or Converting your Access MDB file into an Access ADP file. This will automatically move the tables into SQL. After that I suggest you migrate you queries to SQL one at a time, that way you debugging will not over whelm you. Like one user posted above, watch out for the Access specific functions, the immediate "IF" statements, the data format and the Join types. For most complex Access queries, it will not migrate simple. So you may have to re-write the the complex Access Queries as SQL Views by using the SQL View Designer. Also if you SQL Code in you ASP Code, you have have to look at those, because it may be specific to Access. If you don't have SQL Code in you ASP Code that all your work will be on the Database level which is good. Hope this advice helps.

Alamir SSCertifiable Points: 6085 More actions · Answer 7

Note: use Access XP (2002) upsizing wizard .. which is the best tool to transfer data and tables structure to SQL server 2000 ... and then change your connection string to use SQL Server ... and I think you have to add a permision for Web user to access the DB ... and then change all the function that it is not work in SQL ... Note: to make sure that the query work in SQL server .. paste your query in QUERY ANALYZER (which is one of SQL Server Tool) .. I hope this help u.

Alamir Mohamed
Alamir_mohamed@yahoo.com