Create the database manually (on SQL Server 2005), then right-click
the DB (on SQL Server 2005) and choose Task|Import Data, drill into
your Access DB and import the tables. Once imported you can create a
Database diagram (on SQL Server 2005) since all constraints will now be
handled by SQL server. (make sure you key all of the tables)
Then create an ODBC file DSN for this database connection (to SQL
Server 2005), then fire up the Access database and right-click in the
‘Tables’ window, choose Link Tables, choose ODBC Databases() and then
choose the DSN you created (to SQL Server 2005 database) and finally
all of the tables you need to link.
You’ll want to rename the old tables to “tableName_old” then rename the linked tables removing the “dbo_” from the table names.
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.