June 5, 2008 at 12:13 pm
hii everyone
I wanted to know how to convert access97 to sql server 2005
If anybody has done it could u please list down the problems u face d and how u resolved them.
Thank u in advance
June 5, 2008 at 2:03 pm
I have done this before, we migrated to VB.net/SQL2005 from access. We had to basically start by porting all the data into sql, then we recreated all the form functionality to VB.NET 1 form at a time using the SQL as a back end. We really didn't hit anything that bad from what I remember, the hardest thing we faced is convincing management to let us do it (we had 75 users using a single access database, needless to say, they bought in once it started crashing multiple times daily.)
June 6, 2008 at 12:33 pm
hyd (6/5/2008)
hii everyoneI wanted to know how to convert access97 to sql server 2005
If anybody has done it could u please list down the problems u face d and how u resolved them.
Thank u in advance
The best approach is to convert the file to Access 2000-2002 format, and then use the SQL Server Migration Assistant for Access (SSMA) which does a fine job of transferring your Access data into SQL Server and creates links in Access to the new SQL tables.
July 8, 2008 at 5:38 pm
If you are unable to convert the access file into a higher version of access, there is an Access Upsizing Wizard which is available in Access 97. I used it ages ago when converting to a SQL 6.5 database, not sure if it will would work with newer versions of SQL Server but from memory it should work. But please don't hold me to that since I honestly can't remember.
Best of Luck
---Edited
http://support.microsoft.com/kb/176614
This is the link, it talks about SQL 7 being the highest version, not sure why this it, perhaps the version of libraries it uses to connect to SQL. but there is a link at the bottom of the page for the application, there is also one for Access 2000 as well,
http://support.microsoft.com/kb/325017
I hope this helps some.
July 22, 2008 at 10:43 am
I spent about 5 years doing just this task. I tried using the Upsizing Wizard for Access97, but found there were too many things that I had to manually change, triggers for autonumbers to name one. I prefer an Increment as opposed to the Trigger that the the Upsizing Wizard creates.
I used the SQL Server Import Wizard and had more luck. This also allowed me to change field names (Some people would actually name their fields "Variable?") to what I wanted and name them properly. After the data was moved to SQL server, I converted all Access97 queries to views or stored procedures. Talk about a vast improvement........ I was still using the Access97 front-end, but using all SQL Server to do the processing. You could have hundreds of people in the Access97 database and it never got locked up. I did have to go thru the Access97 Forms and Reports and change field names if I had changed them. There were over 100 Access databases and I put all their data on SQL Server, reducing the need for so many Access97 databases. This also allowed me to compress all the duplicated data.
I then started using the web to display and process data. It's much better, plus now that Reporting Services (SSRS) is easy to use and build, the need for Access databases seems to be declining.
July 24, 2008 at 11:49 pm
The Access97 Upsizing Wizard is to be avoided, as sqlWannabee illustrates with his mention of the trigger vs. DRI (declared referential integrity) approach. And, he is steering you correctly toward the SQL Server import wizard - much better than the upsizing wizard of Access2003/07.
The aforementioned SSMA for Access is a great tool for analysis of the issues you will find in the conversion. It is not necessarily the best way to actually move the data but it can provide immense in pointing out what is wroing when/if the import wizard doesn't succeed.
One of the distinctive competencies of SSMA4access is it's ability to combine multiple MDB sources into a monolithic SQL data store. The current version (4.0) is a CTP and will likely improve. It is much superior as is to the prior versions. There is also a separate version in CTP for Katmai (due to new data types, datetime for example). They are target engine exclusive.
I'm curious about any advantages that may be had by sticking with Access97 forms. Is it due to un-upgraded Office desktops?
If so, the run time versions of A.07 (and, now A.03) are free so the greater versatility of those platforms may be worth considering. I know several prolific Access2SQL developers and I believe none of their products still utilize A.97 technology.
My one taken exception to SqlWannabee's judgement is the future value of Access in database solutions. That is, it remains by far the most productive data entry platform available. Consider any alternative (either VB, AJAX, ADP.net, ...) - they take twice as long to create a given level of functionality compared to Access. It is the only tool that is designed EXCLUSIVELY for such applications and it scales very well with a SQL backend (which all of the others require as well). AND, with the greater functionality of DAO (not available to the others), it also provides the developer with greater flexibility in develping rich clients.
July 29, 2008 at 6:26 am
I've been working quite a bit with SSMA, it works very very well but there are a few issues related to Access:
Every table needs a primary key, otherwise you can't edit, update or delete for that table.
You cannot use date math, e.g. [end_date]-[start_date] you have to change that to DateDiff("d",[start_date],[end_date]) or similar.
An Access query that expects True = -1 will fail because MSSQL uses True = 1. You can change your -1 to True and it will work.
Relationships that are not enforced will not be carried over into MSSQL.
It will tolerate table or field names that have spaces, but you'll get a warning for each of those. Better to use Find and Replace (www.rickworld.com) or similar utility to fix Access first.
You may wind up with a few very slow queries that will need to be converted to pass-thru queries or stored procedures.
One SSMA bug: OLE fields are converted to varbinary(max) but that may not work in Access if you're storing bitmaps AND using SQL Server authentication (it works when using Windows authentication, go figure). Changing to image datatype fixes that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply