Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Replicate Access DB in SQL Server
- Thread starter Xenon54
- Start date
From Book Online:
To create a linked server to access an Access database
Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.
For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the C:\Mydata directory, execute:
sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'c:\mydata\Nwind.mdb'
To access an unsecured Access database, SQL Server logins attempting to access an Access database should have a login mapping defined to the username Admin with no password.
This example enables access for the local user Joe to the linked server named Nwind.
sp_addlinkedsrvlogin 'Nwind', false, 'Joe', 'Admin', NULL
To access a secured Access database, configure the registry (using the Registry Editor) to use the correct Workgroup Information file used by Access. Use the Registry Editor to add the full path name of the Workgroup Information file used by Access to this registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
After the registry entry is configured, use sp_addlinkedsrvlogin to create login mappings from local logins to Access logins:
sp_addlinkedsrvlogin 'Nwind', false, 'Joe',
'AccessUser', 'AccessPwd'
Access databases do not have catalog and schema names. Therefore, tables in an Access-based linked server can be referenced in distributed queries using a four-part name of the form linked_server...table_name.
This example retrieves all rows from the Employees table in the linked server named Nwind.
SELECT *
FROM Nwind...Employees
You can also convert the tables to SQL Server and link them in Access.
To create a linked server to access an Access database
Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.
For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the C:\Mydata directory, execute:
sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'c:\mydata\Nwind.mdb'
To access an unsecured Access database, SQL Server logins attempting to access an Access database should have a login mapping defined to the username Admin with no password.
This example enables access for the local user Joe to the linked server named Nwind.
sp_addlinkedsrvlogin 'Nwind', false, 'Joe', 'Admin', NULL
To access a secured Access database, configure the registry (using the Registry Editor) to use the correct Workgroup Information file used by Access. Use the Registry Editor to add the full path name of the Workgroup Information file used by Access to this registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
After the registry entry is configured, use sp_addlinkedsrvlogin to create login mappings from local logins to Access logins:
sp_addlinkedsrvlogin 'Nwind', false, 'Joe',
'AccessUser', 'AccessPwd'
Access databases do not have catalog and schema names. Therefore, tables in an Access-based linked server can be referenced in distributed queries using a four-part name of the form linked_server...table_name.
This example retrieves all rows from the Employees table in the linked server named Nwind.
SELECT *
FROM Nwind...Employees
You can also convert the tables to SQL Server and link them in Access.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
