You can add a Access database to a SQL Server database. Following steps are -
(1) Open EM.
(2) Goto the Server to which you want to add it as linked server.
(3) Then goto security > Linked Servers section from console tree.
(4) Right click on the Client area. Then New Linked Server.
(5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
(6) Prvide the location of the MDB file.
(7) Click OK.
-- OR --
Issue this statement in QA of SQL Server-
EXEC sp_addlinkedserver @server = 'DBName', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
You have added it as linked server now. Then, use full qualified name to issue your statements.
For ODBC connection mail me at - firstname.lastname@example.org
- Som Dutt