Post reply

Moving access data to SQL Server but have to leave some behind!

  • December 7, 2005 at 5:28 am

    #188661

    Hi all,

    I wonder if anyone knows how I do this.  At the moment we have an access database which has many linked tables.  Some of the tables link to SQL Server and some link to other access databases.  I am trying to move our access database into SQL Server, but I cannot move the tables that are in the other access databases.  Is there a way in SQL server to link to an access table without actually importing the data in?  I will also need to link to other SQL Server tables on other servers without actually importing that data into my database.

    Any ideas?  Am I posting in the right area?

    Many thanks,

    Paula.

  • December 8, 2005 at 1:28 am

    #608340

    You could use LinkedServers or OPENQUERY to reference MDB data, this is copied from the Books Online (BOL) for Distributed Query Architecture:

    Ad hoc connector names

    For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. The rowset can then be referenced the same way a table is referenced in Transact-SQL statements:

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
        'c:\MSOffice\Access\Samples\Northwind.mdb';
        'Admin';'';Employees)

    Andy

Viewing 2 posts - 1 through 2 (of 2 total)

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