November 16, 2006 at 7:59 am
Hi. I'm a regular Access developer that is somewhat new to SQL Server. I've worked with SQL 2000, but not SQL 2005. Anyway, i have a client who i am going to upgrade their Access 2003 Jet database to an Access frontend/SQL Server 2005 Express backend. I was thinking i would use the upsize wizard in Access and create an Access adp and SQL 2000 database and then after fine tuning it, push it out to SQL 2005 Express. I was wondering if anyone had any advice or recommendations around this? Is this an okay approach? Is there a better one? Should i create an Access mdb and link through ODBC instead, and if so, what are the advantages/disadvantages?
Thanks,
Jenn
November 18, 2006 at 4:50 am
The disadvantage of converting to ADP is that it doesn't work. The reason is that the converter will try to convert your access queries to SQL ones and will most probably fail, unless you only have few simple queries.
So you have to link the tables only in a normal access db. This works fine and performance is good, especially if you follow some rules for the queries (no more than 1 left join, no VB functions, etc.). If you follow these rules, Access will send the queries to SQL for execution; otherwise they will also work, but will be executed locally by Access and will run much slower.
November 20, 2006 at 12:44 am
I have done this a few times in the past using 3rd party tools. They all had some problem or another. Some did not convert the primary indexes, some did not convert the queries.
One of the best I have used was "Access to MS SQL" from INtelligent Converters.
Also lately (a month ago) Microsoft started giving out for free a tool that does just this. Conversion from MS Access to SQL 2005. I tries it and it looked nice enough to be used on my next conversion. it is called "Microsoft SQL Server Migration Assistant (SSMA) for Access".
Try it.
November 20, 2006 at 3:00 pm
Thanks for the tips!
December 15, 2006 at 6:10 am
You could also take a look at https://www.keper.com/dbexplorer/conversiontool/ . It copies a Access database to SQL Server by preserving MS Access' behaviour. Converting of views succeeds for 80% automatically, and it provides an interface for manually converting the remaining 20%. Moreover, it also supports the conversion of DML and DDL queries into procedures.
On the site, there is a White Paper worth to read about the troubles of this kind of conversion.
The registration process on the site seems out of order; drop me a note if you want to get a working version.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply