64-bit SQL Server how to use a linked server to connect to access

Original: 64-bit SQL Server how to use a linked server to connect to access

Test environment

Operating system version: Windows Server R2 64-bit

Database version: SQL Server 2005 64-bit

Many tutorials on the Web show you how to connect to access using a linked server, but these tutorials are based on 32-bit SQL Server.

See the Classic Tutorial: SQL Server2005 Connect to Excel, Access

The tutorial mentions "provider" to select "Microsoft.Jet.OLEDB.4.0". However, because Windows Server 2008 64-bit does not provide a 64-bit microsoft.jet.oledb.4.0, there is no corresponding item in the provider for the linked server. As shown in.

What about that?

After a half-day exploration, the author found that microsoft.ace.oledb.12.0 can be used to replace microsoft.jet.oledb.4.0.

Microsoft.ace.oledb.12.0 is used to help existing Microsoft Office files, such as *.mdb, transfer data with other data sources, such as Microsoft SQL Server. It has 32-bit and 64-bit versions. Download the 64-bit version and install it. : http://www.microsoft.com/zh-CN/download/details.aspx?id=13255.

After the installation is complete, "microsoft.ace.oledb.12.0" appears in the provider.

The next way to use it is similar to the tutorial, as long as you replace the provider with microsoft.ace.oledb.12.0:

64-bit SQL Server how to use a linked server to connect to access