SQL Server connection to an Access database implementation

The interconnection of different database platform is commonly called the heterogeneous service of the database, now each big database can realize such heterogeneous interconnection, but the specific implementation technology of each vendor is not the same, such as: In SQL Server called linked server, through ODBC and other database interconnection. The test environment for this article is: Operating system: WINDOWS2000 SERVER (Traditional system)

Installation database: SQLSERVER2000 (English) and access

Concrete implementation Steps

1. Require access and sqlserver2000 software to be installed on a PC

2. Configure Windows ODBC Data source

Start Menu-"assembly-" System management Tool-"data source (ODBC)-" Enter configure User DSN or System DSN can: Add--Select Microsoft ACCESS DRIVER (*. MDB)-"In the ODBC MICROSOFT ACCESS Setup screen, customize the data source name (optional: accesstest)-" Database Select to select an ACCESS repository (*) to establish ODBC. MDB)-"OK complete."

Configure and test ODBC complete!

3. Configure the connection server in sqlserver2000

Enterprise Manager-"Security-" Connection server-"right" New connection server-"define the connection name; Select a different data source; Specify the program name: MICROSOFT OLE DB PROVIDER for ODBC DRIVER; Product name is not filled; Data source Specifies the name of the data source that is defined in ODBC just now; the provider string is filled in the following format: User id=username; PASSWORD=USERPASSWD (or in the following format: Uid=username; PWD=USERPASSWD), where the username and password correspond to the username and password in the Access database to which you want to connect-security tab: Set to use this security context and enter Access's database username and password-the Server Option tab page can be determined by default-"."

4. Preparatory work Completed

In SQL Server Enterprise Manager-"security-" connect to the server to open the newly built connection server-"Click on the table, you can see in the right window of the Access database users have all the table names, but here you can not view the records of the table, This needs to be implemented in the Sqserver Query Analyzer with specific SQL! When accessing a table, use the format: [Connect server name]. [Access users]. [Table name]. More detailed and specific use here no longer repeat.