Post reply

Migration of Access 2000 to SQL Server 2000

  • 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?

  • May 15, 2003 at 7:14 am

    #457252

    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.

  • May 15, 2003 at 7:16 am

    #457257

    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

  • May 15, 2003 at 7:16 am

    #457256

    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

  • May 15, 2003 at 7:16 am

    #457255

    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

  • May 15, 2003 at 8:05 am

    #457282

    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?

  • May 15, 2003 at 9:54 pm

    #457410

    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

  • May 16, 2003 at 5:51 am

    #457441

    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

  • May 16, 2003 at 7:12 am

    #457454

    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

  • May 19, 2003 at 7:52 am

    #457622

    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!

  • May 19, 2003 at 8:44 am

    #457642

    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.

  • May 22, 2003 at 9:22 am

    #458191

    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.

  • May 22, 2003 at 9:22 am

    #458190

    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.

  • May 22, 2003 at 8:58 pm

    #458287

    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

  • May 23, 2003 at 11:56 pm

    #458440

    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

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

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