SQL linked server access remote Access database

The Access database cannot be accessed across servers because it is a file-type database. Let's take a look at how you can leverage SQL Server's linked servers to integrate geographically dispersed Access databases so that Access databases have the ability to cross a WEB server farm. This approach also enables Access databases to be connected to a database of SQL Server databases, even Oracle, to interconnect disparate databases, and ultimately to execute distributed queries, updates, commands, and transactions.

1. Create a linked server to connect to a local Access database

A linked server can be created by using Enterprise Manager or by executing system stored procedures sp_addlinkedserver. Use system stored procedures relatively quickly, in the form of:

sp_addlinkedserver ' linked server name ', ' Product name ', ' microsoft.jet.oledb.4.0 ', ' Access database path and filename '.

Specifies microsoft.jet.oledb.4.0 as the provider_name, specifying the full pathname of the Access database file as the data_source.. mdb database file must reside on the local server and the path must be a valid path on the server.

For example, this example creates a linked server named Anna that operates on an Access database named Dos.mdb under My Documents \ Folders, and executes in SQL Query Analyzer: e:\

sp_addlinkedserver ' Anna ', ' Access ', ' microsoft.jet.oledb.4.0 ', ' e:\ My Documents \dos.mdb '

2. Create a linked server login map

The same can be done with Enterprise Manager or stored procedures. The format of the stored procedure is:

sp_addlinkedsrvlogin ' linked server ', False, ' SQL Server login ', ' admin ', NULL

To access a unclassified Access database, a SQL Server login that attempts to access the Access database should have a login mapping defined for user Admin that does not have a password, and the following example enables the local user SA to access a linked server named MyTest:

sp_addlinkedsrvlogin ' MyTest ', false, ' sa ', ' admin ', NULL

To access a confidential Access database, use Registry Editor to configure the registry so that you can use the correct workgroup information file for access. Use Registry Editor to add the full path name of the workgroup information file that Access uses to the registry key:

Hkey_local_machine\software\microsoft\jet\4.0\engines\systemdb

After you have configured the registry keys, use sp_addlinkedsrvlogin to create a login map that logs on locally to Access logins:

sp_addlinkedsrvlogin ' MyTest ', false, ' sa ', ' [Accessuser] ', ' [accesspwd] '

The linked server and linked server login mappings are established and can be viewed in Enterprise Manager.

3. Linked Server Testing

You can test the linked server that you create in SQL Query Analyzer. Because the Access database does not have a directory and schema name, tables in the access-based linked server can use [linked_server] in distributed queries ... The four-part name of [table_name] is referenced. The following example retrieves all rows from the articles table from a linked server named MyTest:

Select * from Mytest...articles
Or: SELECT * from OpenQuery (mytest, ' select * from. Articles ')

4. Access database that accesses linked servers with code

Only the implementation of Access databases that use code to access linked servers makes the linked server the most flexible and practical. You can use the code from the test linked server in three to establish a stored procedure for ASP code calls, or you can call a linked server directly in ASP code.