Port BE to SQL2K

[XMan]

I used the DB Splitter and it splitted the BE to another MDB but I wanted to
create on SQL2K. Where's the option to do this? TIA.

 

[Dirk Goldgar]

I used the DB Splitter and it splitted the BE to another MDB but I
wanted to create on SQL2K. Where's the option to do this? TIA.

Use the Upsizing Wizard. It should be there in the Tools -> Database
Utilities submenu.

 

[XMan]

I'm using Access97 for now and it doesn't have upsizing option.

Do I need to convert to Access2K first?

 

[Dirk Goldgar]

I'm using Access97 for now and it doesn't have upsizing option.

Do I need to convert to Access2K first?

I haven't done it in Access 97, but I imagine that you would have to do
it manually. Probably you'd start by working from the SQLS side, using
DTS to copy the tables from your current back-end .mdb. You may need to
add timestamp fields to some or all of the SQLS tables, to enable
Access/ODBC to maintain updatability of the data in some circumstances.
After you've created your SQL Server database and created+imported the
tables from the .mdb file, then you can go into the Access front-end and
relink the tables using the Linked Table Manager.

 

[XMan]

Thanks for your help so far. As for the Link Table Manager. Can I point the
link to SQL2K or I have to create a link using Import menu option? TIA.

 

[Albert D. Kallal]

There is a upsizing wizard aviable for a97 in the office downloads section.
I think the a2000 one is better.

Here is some reading, and links that you should find usefull:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175619&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Center
The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.
http://support.microsoft.com/?id=128384

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

 

[Dirk Goldgar]

Thanks for your help so far. As for the Link Table Manager. Can I
point the link to SQL2K or I have to create a link using Import menu
option? TIA.

I'm assuming that you have already split your database into front-end
and back-end, so the front-end is currently linked to tables in the Jet
(.mdb) back-end. I *was* thinking that you'd be able to use the Linked
Table Manager to relink these tables, this time specifying the SQL
Server tables. However, I see I was wrong, and the LTM won't do that.
Instead, you need to delete all the linked tables in the front-end (just
deleting the links, you see, not the tables in the back-end) and then
use File -> Get External Data -> Link Tables to link to the SQL Server
tables. You can go in after the tables have been linked and rename the
links -- manually or programmatically -- to get rid of the "dbo_"
prefix.

I haven't looked at it, but I expect the white paper on Upsizing that
Albert Kallal pointed you to has more comprehensive instructions than I
can give you.

 

[XMan]

Thanks. Your suggestion was right on.

I'm assuming that you have already split your database into front-end
and back-end, so the front-end is currently linked to tables in the Jet
(.mdb) back-end. I *was* thinking that you'd be able to use the Linked
Table Manager to relink these tables, this time specifying the SQL
Server tables. However, I see I was wrong, and the LTM won't do that.
Instead, you need to delete all the linked tables in the front-end (just
deleting the links, you see, not the tables in the back-end) and then
use File -> Get External Data -> Link Tables to link to the SQL Server
tables. You can go in after the tables have been linked and rename the
links -- manually or programmatically -- to get rid of the "dbo_"
prefix.

I haven't looked at it, but I expect the white paper on Upsizing that
Albert Kallal pointed you to has more comprehensive instructions than I
can give you.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)