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
jmThat'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! :rolleyes: )

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|||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.|||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.|||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|||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.