Migration of Access 2000 to SQL Server 2000

Question

Post reply

Migration of Access 2000 to SQL Server 2000

gurukg

Valued Member

Points: 58
More actions
May 15, 2003 at 6:36 am

#105755

All,
I am supposed to migrate Access 2000 DB to SQL Server 2000 DB. I tried Upsizing Wizard of Access 2000. But it is of no use. I am not successful in that.
Then I chose SQL Enterprise Manager,DTS wizard for my migration.
I am successful in migrating ordinary tables and its data.
How will I migrae Linked Tables (as Views) using DTS or any other tool from SQL 2000?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

quote:
How will I migrae Linked Tables (as Views) using DTS or any other tool from SQL 2000?

Are these linked tables linked to different data sources?

You don't see linked tables in DTS but you can create query in ACCESS to query the linked table and DTS the query.

gurukg Valued Member Points: 58 More actions · Answer 2

Hi Allen,

Thanks for your repsponse.

Yes they are linked to Oracle Datasource. I am sorry. I did not mention that.

How will I migrate those into SQL Server and how?

Thanks

gurukg Valued Member Points: 58 More actions · Answer 3

Hi Allen,

Thanks for your repsponse.

Yes they are linked to Oracle Datasource. I am sorry. I did not mention that.

How will I migrate those into SQL Server and how?

Thanks

gurukg Valued Member Points: 58 More actions · Answer 4

Hi Allen,

Thanks for your repsponse.

Yes they are linked to Oracle Datasource. I am sorry. I did not mention that.

How will I migrate those into SQL Server and how?

Thanks

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

You can import data from ORACLE database using DTS too.

Or create queries on top of your linked tables in ACCESS and DTS those queries into SQL Server.

After data migration, are you still going to use ACCESS as fronted end? Can you tell what you try to achieve?

gurukg Valued Member Points: 58 More actions · Answer 6

Allen,

Thanks. Using DTS, I think I can only import Data alone. Am I right?

I cannot make Table creation with constraints to get copied into SQL Server. How will I achieve the same?

I will not be using Access Forms as Front end, which currently the application has. I am planning to make a shift to VB.

Thanks

KeithDavies SSC Veteran Points: 267 More actions · Answer 7

As regards using VB or Access as front end, having used both, both have advantages and disadvatages. If your application is an 'internal' one then it's possible you could stick with Access using a data project. An ADP doesn't use the Jet engine but has the advantage of allowing you to quickly convert your current dB by simply modifying the DAO items to ADO (you may already be using ADO) and Quieries to views/sp's etc but keeping your forms/reports. Also an ADP has the connection built in (are you using Windows Authentication). I've found that with a bit of careful design an ADE (compiled from ADP) is as fast as a VB exe. One advantage is it's a lot faster to develop in.

I've carried out quite a few migrations from Access to SQL Server - the upsizing wizard usually fails to convert eveything but in general can convert at least 50% of Access queries. If you are using DAO and recordsets then the vast majority of the code will be OK. Microsoft Access Projects with Microsoft SQL Server Book is a good starting point!

Hope this helps!

Keith Davies

Regards

Keith Davies
IT Consultant

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 8

quote:
I think I can only import Data alone. Am I right?

You are right. So you have to recrate the constraints in SQL Server using either QA or EM.

You need also redefine the indexes for your tables according to your application needs.

Edited by - Allen_Cui on 05/16/2003 07:49:06 AM

tombynum SSChasing Mays Points: 647 More actions · Answer 9

Along these same lines:

We are also moving from Access FE/BE apps to a SQL Server Engine.

I have selected to develope MDB / MDE interfaces (using ADO) because we will need to connect to two different databases (hosted by the same server).

In my examination of ADP's I found that you can only use one database on the server...or is there a way around that issue?

Of the two databases (on one server)

One of the databases is an accounting system, so we are just wanting READONLY access to tables and views in order to lookup customers, inventory, fill forms and stuff.

The primary database is home grown for our own business purposes, so this is where we will read / write data.

Any suggestions?

The ~BEST~ solution is always the simplest one!

JFW Mr or Mrs. 500 Points: 546 More actions · Answer 10

I have developed several ACCESS applications with SQL Server backends. I have to retrieve data from different databases, but want to limit the number of ODBC datasources that must be setup on each client. Also I do not want to pollute the third party databases with my stored procedures or views. Therefore, I created a "portal" database. It contains views and stored procedures that reference the necessary databases on the same server. One ODBC datasource gives access to all data on the server and isolates my code to my own database. Perhaps you could use this technique.

lawrencek SSC-Addicted Points: 444 More actions · Answer 11

If you have a product like Visio enterprise you can reverse engineer the access db and move it to a sql db and sill maintain the relations, then dts the data into to table already created.

lawrencek SSC-Addicted Points: 444 More actions · Answer 12

If you have a product like Visio enterprise you can reverse engineer the access db and move it to a sql db and sill maintain the relations, then dts the data into to table already created.

gurukg Valued Member Points: 58 More actions · Answer 13

Hi lawrencek,

Could you pls explain the procedures on reverse engineering Access DB to SQL DB suing VISIO Enterprise.

I do not understand this.

Thanks

gurukg Valued Member Points: 58 More actions · Answer 14

I am able to successfully migrate osme of the queries from Access 2000 to SQL 2000. Access DB has lot of Insert Queries.

In what form I can convert these Insert QUeries such as plain SQL statements, Stored Procedures, Views etc.,

I cannot create as a View bcos these being Insert Statements.

Expert Advice pls.

Thanks