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

**MGFoster** · Jun 22 '06, 06:15 PM

Re: What is Your Strategy for Upgrade an Access db to SQL Server2000?

NickName wrote:[color=blue]
> 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?[/color]

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)

**NickName** · Jun 22 '06, 09:45 PM

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

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:[color=blue]
> NickName wrote:[color=green]
> > 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?[/color]
>
> 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)[/color]

**Erland Sommarskog** · Jun 23 '06, 11:05 AM

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

MGFoster (me@privacy.com ) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**NickName** · Jun 23 '06, 07:25 PM

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

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:[color=blue]
> MGFoster (me@privacy.com ) writes:[color=green]
> > 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.[/color]
>
> 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, esquel@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[/color]

**Erland Sommarskog** · Jun 23 '06, 09:15 PM

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

NickName (dadada@rock.co m) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**NickName** · Jun 27 '06, 06:55 PM

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

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:[color=blue]
> NickName (dadada@rock.co m) writes:[color=green]
> > 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.[/color]
>
> 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, esquel@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[/color]

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

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

Comment

Comment

Comment

Comment

Comment

Comment