VBA - 通过 ADODB 将 Excel 2007 电子表格连接到 Access 2007 数据库

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1522324/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:51:33  来源:igfitidea点击:

VBA - Connect Excel 2007 Spreadsheet to Access 2007 Database via ADODB

excel-vbavbaexcel-2007ms-accessexcel

提问by John M

Currently there is a Excel 2002 spreadsheet writing/retrieving data from a Access 2002 database. IT is presently researching a upgrade to Office 2007 and when I upgrade both the spreadsheet and database the ADODB connection fails.

目前有一个 Excel 2002 电子表格从 Access 2002 数据库中写入/检索数据。IT 目前正在研究升级到 Office 2007,当我同时升级电子表格和数据库时,ADODB 连接失败。

The 2002 era VBA connection string is:

2002 时代的 VBA 连接字符串是:

Provider=Microsoft.Jet.OLEDB.4.0;
Jet OLEDB:Database Password = stackoverflow; 
Data Source= \test\test.mdb"

In usage:

使用中:

Dim cnt As ADODB.Connection
Set cnt = New ADODB.Connection
cnt.Open LiveCon

Changing the .mdb suffix to .accdb breaks the connection. Can someone direct me to the new syntax for the connection string?

将 .mdb 后缀更改为 .accdb 会中断连接。有人可以指导我使用连接字符串的新语法吗?

回答by Fionnuala

You may find this helpful: http://www.connectionstrings.com/access-2007

您可能会发现这很有帮助:http: //www.connectionstrings.com/access-2007

回答by onedaywhen

Try changing

尝试改变

Provider=Microsoft.Jet.OLEDB.4.0

to

Provider=Microsoft.ACE.OLEDB.12.0