To apply the SQL Server linked servers to access a remote Access database

access|server|sqlserver| Access | server | links | data | database
In WEB development, you often use an Access database. However, because access is a file-type database, Access cannot be made across servers. Through the author's exploration, we find that we can use the linked server of SQL Server to integrate the geographically dispersed Access database, so that the Access database has the ability to cross the Web server farm. By doing so, you can also connect Access databases to network databases such as SQL Server, and even Oracle, to enable the interconnection of heterogeneous databases to perform distributed queries, updates, commands, and transactions.

First, create a linked server, 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 MyTest that operates on an Access database named Dos.mdb that e:\ my document \ folder, and executes in SQL Query Analyzer:

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

Second, 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 Oftware\microsoft\jet\4.0\engines Ystemdb

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.

Third, the test of the linked server

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 ')

Access database that accesses linked servers using 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 test linked server code in three to establish stored procedures for ASP code calls, or directly in ASP code to call the linked server, the following is the example I passed the test:

<%

Dim Conn, sSQL, RS

Conn= "PROVIDER=SQLOLEDB; Server=localhost; Uid=sa; Pwd=sqlserver; "' The password for the user SA is SQL Server. You can omit the database name

On Error Resume Next

sSQL = "SELECT * FROM Mytest...articles"
Set rs = Server.CreateObject ("ADODB.") Recordset ")
Rs.Open sSQL, Conn, 1, 1

Rs.movefirst
Response.Write Rs (0) & "<br>"

If Err.number<>0 Then
Response.Write "Data Error: Database connection error, or access datasheet error!"
Else
Response.Write "Ok!"
End If

%>

Zhang Qing 2002.10.1 3:58