The Access database is often used in Web development. However, since Access is a file type database, it cannot be accessed across the server. After the author's exploration, it is found that the SQL Server link server can be used to integrate geographically dispersed Access databases, so that the Access database has the ability to span the web server group. Doing so, you can connect the Access database to SQL Server, and even Oracle and other network databases to implement interconnects of heterogeneous databases to perform distributed queries, updates, commands, and transactions.
First, create a link server, connect local Access database
Creating a link server You can use Enterprise Manager or execute system stored procedure sp_addlinkedServer to complete. Use the system stored procedure relatively fast, format is: sp_addlinkedServer 'link server name', 'product name', 'microsoft.jet.oledb.4.0', 'Access database path and file name'
Specify microsoft.jet.OleDb.4.0 as provider_name, specify the full path name of the Access database file as Data_Source. The .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 link server named myTest, and performs an ACCESS database named dos.mdb under the E: / my document / folder, executes in the SQL Query Analyzer:
Sp_addlinkedServer 'MyTest', 'Access 2000', 'Microsoft.jet.OleDb.4.0', 'E: / My Document /Dos.mdb'
Second, create a link server login map
It can also be done with "Enterprise Manager" or stored procedure. The format of the stored procedure is:
Sp_addlinkedsrvlogin 'Link Server', False, 'Sql Server Login', 'Admin', NULL
To access the non-confidential Access database, the SQL Server login attempt to access the Access database should have a login map defined by the user admin, the next example enables the local user SA to access a link server called MyTest:
Sp_addlinkedsrvlogin 'MyTest', False, 'Sa', 'Admin', NULL
To access a confidential Access database, you need to configure the registry using the Registry Editor to use the correct workgroup information files of Access. Use the Registry Editor to add the full path name of the workgroup information file used by Access in the registry key:
HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / Jet / 4.0 / Engines / SystemDB
Once the registry key is configured, use sp_addlinkedsrvlogin to create a login mapping from local login to Access login:
sp_addlinkedsrvlogin 'MyTest', False, 'Sa', '[Accessuser]', '[AccessPWD]'
Link Server and Link Server Login Mapping is completed, you can view in Enterprise Manager.
Third, the test of the link server
You can test the created link server in the SQL Query Analyzer. Since the Access database does not have a directory and architectural name, the tables in the Access-based link server can be referenced using the four parts names of [Linked_Server] ... [Table_name] in a distributed query. The following example retrieves all the lines of Articles tables in the link server named mytest: SELECT * from mytest ... Articles
Or: select * from OpenQuery (MyTest, 'Select * from .Articles')
Fourth, use the code access to the Access database of the link server
Only the Access database that implements the Code Access Link Server will make the link server exert maximum flexibility and practicality. You can use the code to establish a stored procedure using the code of the third test link. For the ASP code call, you can also call the link server directly in the ASP code. Here is the example I pass the test:
<%
DIM CONN, SSQL, RS
CONN = "provider = SQLOLEDB; Server = localhost; uid = sa; pwd = SQLServer;" "The user SA's password is SQL Server. Can omitted the database name
ON Error ResMe next
SSQL = "Select * from mytest ... Articles" set = server.createObject ("AdoDb.Recordset") RS.Open SSQL, CONN, 1, 1
Rs.Movefirst Response.write RS (0) & "
"
If Err.Number <> 0 Then Response.write "Take Data Error: Database Connection Error, or Acqui Data Fault!" Else Response.write "OK!" endiff
%>
Zhang Qing 2002.10.1 3:58
