473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

What is Your Strategy for Upgrade an Access db to SQL Server 2000?


This question probably has been asked many a time. And yet I feel it
is still relevant for one thing a search on this NG does not produce a
desirable answer.
It is kind of disappointing that MS would not be able to transfer ER
relationship from an Access db to a SQL Server 7/2000-based one, the
upgraded db/imported tables sitting on the SQL Server would not have
PKs, say, you have 100 user tables, you have to first recreate PKs for
each of them then set up relationship between/among them, quite time
consuming. Do you have a better way?

Along the same line of the task, what options out there for converting
Access Modules into SQL Server-based Stored Procedures and/or UDFs?
The manual option is sure there, third party tool? I wouldn't trust
them that much though.

TIA.

Jun 22 '06 #1
6 1399
NickName wrote:
This question probably has been asked many a time. And yet I feel it
is still relevant for one thing a search on this NG does not produce a
desirable answer.
It is kind of disappointing that MS would not be able to transfer ER
relationship from an Access db to a SQL Server 7/2000-based one, the
upgraded db/imported tables sitting on the SQL Server would not have
PKs, say, you have 100 user tables, you have to first recreate PKs for
each of them then set up relationship between/among them, quite time
consuming. Do you have a better way?


Use the MS Access Upsizing wizard (Tools > Database Utilities > Upsizing
wizard). It will convert all relationships, PKs, FKs and indexes from
the Access DB to the SQL DB.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Jun 22 '06 #2
Thanks, good to know, last time (probably a little over half a year
ago) when I tried this approach of using Access Upgrade Wizard, it did
not convert relationships, keys etc.
Too bad, I can't use this approach at this point because the server
machine does not have Access installed and I can't do that without a
license etc. etc. While using the SQL Server import facility is an
option. I wrote a script to automatically create a PK for each user
table (good thing the current Access db has a good patent for PKs), but
this is a very desirable option though for one thing the relationships
are not there, not too sure it would be easy to automatically create
FKs.

MGFoster wrote:
NickName wrote:
This question probably has been asked many a time. And yet I feel it
is still relevant for one thing a search on this NG does not produce a
desirable answer.
It is kind of disappointing that MS would not be able to transfer ER
relationship from an Access db to a SQL Server 7/2000-based one, the
upgraded db/imported tables sitting on the SQL Server would not have
PKs, say, you have 100 user tables, you have to first recreate PKs for
each of them then set up relationship between/among them, quite time
consuming. Do you have a better way?


Use the MS Access Upsizing wizard (Tools > Database Utilities > Upsizing
wizard). It will convert all relationships, PKs, FKs and indexes from
the Access DB to the SQL DB.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


Jun 22 '06 #3
MGFoster (me@privacy.com) writes:
Use the MS Access Upsizing wizard (Tools > Database Utilities > Upsizing
wizard). It will convert all relationships, PKs, FKs and indexes from
the Access DB to the SQL DB.


As I have never worked with Access, I don't have any experience of the
upsizing wizard. However, I have seen Mary Chipman who has been an MVP
for both Access and SQL Server (today she is an MS employee), say several
times "don't use the wizard". The two products are so different that the
best way to convert is hard work where you make a thorough redesign of
the application. Using the upsizing wizard leads to an Access database
that runs in SQL Server, but which maintains many of the problems with
Access.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 23 '06 #4
Thanks. Would be interested in reading M. Chipman's writings on "don't
use the wizard", did key word search for it to no avail.

Erland Sommarskog wrote:
MGFoster (me@privacy.com) writes:
Use the MS Access Upsizing wizard (Tools > Database Utilities > Upsizing
wizard). It will convert all relationships, PKs, FKs and indexes from
the Access DB to the SQL DB.


As I have never worked with Access, I don't have any experience of the
upsizing wizard. However, I have seen Mary Chipman who has been an MVP
for both Access and SQL Server (today she is an MS employee), say several
times "don't use the wizard". The two products are so different that the
best way to convert is hard work where you make a thorough redesign of
the application. Using the upsizing wizard leads to an Access database
that runs in SQL Server, but which maintains many of the problems with
Access.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Jun 23 '06 #5
NickName (da****@rock.com) writes:
Thanks. Would be interested in reading M. Chipman's writings on "don't
use the wizard", did key word search for it to no avail.


I went to http://groups.google.com/advanced_group_search and for
exact phrase I filled in "upsizing wizard" and for author I specified
"Mary Chipman", and I opted to get 100 hits per page. Next I pressed Search.
I got 189 hits.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 23 '06 #6
Thanks, unintended regression for a minute (forgot the Advance search
option).
I've found some pattern of the intersection/link tables in the Access
db and based on that created a script to add FKs/relationships for the
tables, missed one or two (which can easily be handled by hand). So,
in other words, I have one script to address PKs/Indexes and another
for FKs, but of course they can be just one.

Now, as for the modules and queries (Access), I guess, they are messy
parts, would have to convert them probably one by one into either SPs
or UDFs.

Erland Sommarskog wrote:
NickName (da****@rock.com) writes:
Thanks. Would be interested in reading M. Chipman's writings on "don't
use the wizard", did key word search for it to no avail.


I went to http://groups.google.com/advanced_group_search and for
exact phrase I filled in "upsizing wizard" and for author I specified
"Mary Chipman", and I opted to get 100 hits per page. Next I pressed Search.
I got 189 hits.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Jun 27 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: kg7poe | last post by:
We have Sqlsvr 6.5 under NT4. We want to upgrade the database to Sqlsvr 2000 under windows 2K. Is there any way we can do this by copying the database to the win2K computer?? At any rate would...
2
by: Saiyou Anh | last post by:
Hi! If doing online version upgrade from SQL 7.0 to SQl 2000, (1) do I need to shut down server? (2) after upgrading, is all login and db user, group information, scheduled Jobs, maintenance...
1
by: DickChristoph | last post by:
Hi I am interested in converting a Access 97 application to VB.Net (well okay rewriting). This would be a VB.Net client with a SQL Server backend, as opposed to my other alternative which would...
1
by: Dave | last post by:
I am having problems accessing DTS after install SP4 and was wondering if someone could offer some advice. I installed SP4 and got the following error after it competed. Unable to write to...
8
by: Hermawih | last post by:
Hello , I want your opinion about this . In order to say it clearly , I think I have to describe it in long sentences . I could consider myself as Intermediate/Advance Access Developer ;...
2
by: DonLi | last post by:
Hi, I have a copy of Access 97 while my client uses Access 2000 which supports Unicode. My client has ordered a copy of Access 2000 upgrade for me, but the middleman, atomicpark.com is totally...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
5
by: HotRod | last post by:
I am new to this so please go easy. We currently have some students doing some work on some web based tracking documents for us. They are currently using VB .net to develop what we requested....
3
by: RayPower | last post by:
I'm having a system using Access 2000 as both front-end (queries, forms, reports & temp tables for reports) & back-end (data) with back-end running on the server. The application runs on the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.