473,883 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1442
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:::mgf0 0 <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:::mgf0 0 <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****@sommarsk og.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****@sommarsk og.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.co m) 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****@sommarsk og.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.co m) 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****@sommarsk og.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
2063
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 someone explain how to go about updating this. I know nothing about sqlsvr anyversion but do need to know. TIA
2
2122
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 paln... still there? or I have to save the info before upgrading and recreate after upgrading? (3) any other tips I need to know? I'm doign that for the first time. Thanks! Saiyou
1
2213
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 be rewriting this as an Access XP application with a SQL Server backend. I have worked enough in VB.Net at home to known that this would be a better solution but this would be the first application for our company using VB.Net (a largely...
1
4358
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 response file 'U:\WINDOWS\setup.iss' during recording. Please ensure enough space is available on target drive. I got the error 3 times (3 pop-ups).
8
4053
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 ; Intermediate/Advanced Database designer . Because of the requirements , I must create Web Application . Access Pages is not suitable for that so I think about learning VB Net / ASP Net . I am
2
1729
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 ABSURD, first they delayed delivery and secondly when they sent the ordered product they sent me a badly damaged CD, when I asked for replacement they did not even bother to apologize instead telling me to contact them. I don't ever ever want to...
47
4559
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 company and this is a big decision for us(!) It's not just the money it's committing to an new version of Access!
5
2960
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. Anyway I've been calling my local ISP's and no one supports .net it seems to be all apache and MySQL. I'm wondering if everyone here can answer a few questions. 1) Can I run vb .net web pages on a regular IIS server without the .net extensions? 2)...
3
2794
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 network with around 15 users. The back-end database is suffering from performance issues. There were several corruptions since few months & one major corruption & corrupted the MSysObjects table. Also, the system will run batch process that generate...
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9787
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11128
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10408
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9568
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4607
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4212
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.