Implementation of SQL Server connection to ACCESS database

The interconnection of different database platforms is generally called the heterogeneous service of databases. Currently, all major databases can achieve such Heterogeneous Interconnection, but the specific implementation technologies of different vendors are different, such: linkedis called LINKEDSERVER, and ODBC is used to interconnect with other databases. The test environment in this article is: Operating System: WIN

The interconnection of different database platforms is generally called the heterogeneous service of databases. Currently, all major databases can achieve such Heterogeneous Interconnection, but the specific implementation technologies of different vendors are different, such: in SQL SERVER, it is called LINKED SERVER, and ODBC is used to interconnect with other databases. The test environment in this article is: Operating System: WIN

DifferentDatabasePlatform interconnection is generally calledDatabaseHeterogeneous servicesDatabaseYesImplementationSuch Heterogeneous Interconnection is only specific to various vendors.ImplementationDifferent technologies, such as linked server in SQL SERVER.ImplementationAnd othersDatabase. The test environment in this article is Windows SERVER (Traditional Chinese system)

InstallDatabase: SQLSERVER2000 (English version) and ACCESS

DetailsImplementationProcedure

1. install ACCESS and sqlserver2000 software on the PC

2. Configure the ODBC data source for windows

Choose "start"> "program set"> "System Management Tools"> "Data Source (ODBC)"> "Configure user DSN" or "system DSN". Choose "add"> "microsoft access driver (*. MDB)-> On the odbc microsoft access setup screen, you can customize the data source name (for example, ACCESSTEST)-> select database to create an odbc access database (*. MDB)-"OK.

ODBC configuration and test completed!

3. ConfigureConnectionServer

Enterprise Manager-security-ConnectionRight-click server-> CreateConnectionServer-DefinitionConnectionName: select another data source. The specified program name is microsoft ole db provider for odbc driver. The product name is optional. The data source name is the data source name defined in ODBC; enter the provider string in the following format: User ID = username; Password = userpasswd (or in the following format: UID = username; PWD = userpasswd ).ConnectionACCESSDatabaseIn the "user name and password"-"Security Tab: Set to use this security context, and enter the ACCESSDatabaseSelect "user name and password"> "server options" and click "OK" by default.

4. All preparations have been completed.

In sqlserver Enterprise Manager-security-ConnectionOpen the server.ConnectionServer-click table and you will see the ACCESSDatabaseAll table names owned by the user, but the table records cannot be viewed here. This requires the use of specific SQL statements in the SQL Server Query analyzer.Implementation! When accessing a table, the format is :[ConnectionServer name]. [ACCESS user]. [Table name]. For more details, I will not go into details here.