SQL linked server access remote Access database

Source: Internet
Author: User
Tags mdb database sql query access database access

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.



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.