Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    68

    Unanswered: Migration from MS access TO MS SQL

    We recently needed to switch from Access over to MS SQL because of the
    limitations with access and its size maximums. We currently use Access as our reporting trigger for the entire companies reporting. I'm currently trying to discover functionality that I had in Access in MS SQL. I'm slowly but surely learning. Our database structure in Access was set up with Master Files and revenue files in seperate Access databases and then linked into functional reporting access databases. So we had many links from DB to DB. Additionally we had links directly into the AS400(JDE). My question is two fold:


    How do you link from DB to DB in MS SQL, my assumption was using a VIEW.
    Is this the best method or should you store all the tables in the same DB(probably not, I would say).

    How do you link(create view) a file where the source is the as/400? And again is this a good method for getting at that data. I think my problem here is the PROVIDER_NAME

    One more. Our Infrastructure group named the server Local is that going to inhibit anything in the future.


    And also one more. I get an error creating views that look at other SQL DB's when I include criteria. When I don't they work..


    Thanks
    jm
    jm

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's a lot of questions. I can try to answer some of them:

    FACT: You can reference tables and other objects in other databases by using the full three-part name syntax:

    database.owner.object

    You can use this syntax is views, stored procedures, etc...wherever you would noramally reference the table or view. If you omit the database and owner, SQL server assumes you are refering to an object in the current database owned by either the current login or the dbo. Fully referencing objects is not normally required, and that's probably why you are not familiar with it.

    OPINION: Put all your tables in one database. Was there a reason you had them separated in Access? That probably affected system performance.

    QUESTION: What error are you getting when you include criteria in your linked views? (There should be some rules for the forum forbidding people from mentioning they are receiving an error without stating what the error is! )

    I don't think naming your server Local will do any harm except make code extremely confusing to read, especially code on other servers that reference it! It does show a complete lack of imagination and creativity, and you can tell them I said so.

    blindman
    Last edited by blindman; 08-14-03 at 01:42.

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Migration from MS access TO MS SQL

    As fare as I know, SQL Server does not support links to "alien" systems like AS/400. So, you have in my opinion two options:

    * Maintain MS Access as integration platform, even if your data is moved to SQL Server
    * Let your reporting tool connect to the different systems and join the results

    For a multi-database application, and a Access-SQL Server converison tool look at my references beneath.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jul 2003
    Location
    London
    Posts
    26

    Re: Migration from MS access TO MS SQL

    It is possible basically you may need MS Host Integration Server so you can talk to AS/400 servers. Using SQL 2000 Link Server technology you use Ole DB/ODBC to talk to any other RDBMS as long as it supports Ole DB/ODBC (including AS/400). I have not tried this myself so I could be way of the mark, I have tried it with Sybase running on Unix.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, you should be able to connect to your Oracle database. Checks SQL Server Books Online for "Configuring Linked Servers".

    Access makes a fine reporting tool and a flexible user-interface, but if your data is on SQL server you would get the best performance by pushing as much processing back onto the server as possible. Access is notoriously slow when processing queries on remote data, because it often must pull entire tables across a network in order to make its joins. It is sometimes able to formulate a plan that involves the server filtering the data first, but this cannot be relied upon. Your best bet is to let SQL server make all the links between tables and other servers and pass that data back to Access for display. Add to that the fact that MS Access' security schema is primitive and clunky compared to SQL server.

    Check out the option of converting your Access database to an Access Data Project (.adp file extension). ADP applications use a more direct connection to SQL server, and all processing is performed on the server since the ADP file cannot store tables and views. An Access Data Project is my preference for rapid application development.

    blindman

  6. #6
    Join Date
    Jun 2003
    Posts
    31
    Blindman is corected. Why need a "middleware" Access when your data is in SQL Server? not ony that SQL Server is more robust & secure than Access , the performance is much better when directly access data. thru SQL Server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •