Как запросить таблицу MS-Access из MS-Excel (2010) с помощью VBA

Я пытаюсь написать некоторый код VBA в Excel для запроса таблицы в Access. Я пробовал несколько примеров кода для этого, таких как добавленные ссылки, и они все, кажется, терпят неудачу в части "Открытого соединения". Я пробовал использовать разные ссылки, но я не уверен, какие из них следует использовать, в чем различия между некоторыми из разных версий (например, объекты данных Microsoft ActiveX 2.0,2.1,..., 6.0) или какова информация о поставщике должно быть. Для информации о поставщике я обычно видел что-то вроде

"Поставщик = Microsoft.Jet.OLEDB.4.0; Источник данных ="

Но я не уверен, что это то, что мне нужно использовать, или почему/какие условия что-либо в строке поставщика, показанной выше, должно измениться. Может кто-нибудь объяснить мне, как правильно делать такие вещи?

Примечание. Если это вообще возможно, я хотел бы получить решение, которое бы работало без загрузки какого-либо другого приложения и работало бы как в версиях Access, так и в Excel как в 2007, так и в 2010 году, поскольку оно должно работать на разных компьютерах с, возможно, различными версиями Office.

Ссылки на похожие вопросы: Сбой запроса VBA для доступа к Excel http://www.mrexcel.com/forum/showthread.php?t=527490

Код:

Sub asdf()

strFile = "C:\Users\bwall\Desktop\Excel Query Access Testing"

Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Dim AppPath As String
Set cn = CreateObject("ADODB.Connection")
AppPath = Application.ActiveWorkbook.Path


strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & AppPath & "\Masterlist_Current_copy.accdb;"
Debug.Print strConnection
strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components];"
cn.Open strConnection
Set rs = cn.Execute(strSql)
MsgBox rs.Fields(0) & " rows in MyTable"
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Значение strConnection =

Provider = Microsoft.Jet.OLEDB.4.0; Источник данных = C:\Users\bwall\Desktop\Тестирование доступа к запросам Excel\Masterlist_Current_copy.accdb;

Ответ 1

Провайдер должен быть Provider=Microsoft.ACE.OLEDB.12.0, если ваша целевая база данных - это формат ACCDB. Provider=Microsoft.Jet.OLEDB.4.0 работает только для более старого формата MDB.

Вам даже не нужно устанавливать Access, если вы используете 32-битную Windows. Jet 4 входит в состав операционной системы. Если вы используете 64-битную Windows, Jet 4 не входит в комплект, но вам все равно не понадобится сам доступ. Вы можете установить Microsoft Access Database Engine 2010 Redistributable. Обязательно загрузите подходящую версию (AccessDatabaseEngine.exe для 32-битной Windows или AccessDatabaseEngine_x64.exe для 64-разрядной версии).

Вы можете избежать проблемы с ссылкой на версию ADO, используя позднюю привязку, которая не требует ссылки.

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

Затем присвойте свой объект ConnectionString объекту conn. Вот краткий пример, который запускается из модуля кода в Excel 2003 и отображает окно сообщения с подсчетом строк для MyTable. Он использует позднюю привязку для объектов ADO и объектов набора записей, поэтому не требует установки ссылки.

Public Sub foo()
    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Access\webforums\whiteboard2003.mdb"
    strSql = "SELECT Count(*) FROM MyTable;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.fields(0) & " rows in MyTable"
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Если этот ответ не решает проблему, отредактируйте свой вопрос, чтобы показать нам полную строку подключения, которую вы пытаетесь использовать, и точное сообщение об ошибке, которое вы получаете в ответ на эту строку соединения.

Ответ 2

Sub Button1_Click()
Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Documents and Settings\XXXXXX\My Documents\my_access_table.accdb"
    strSql = "SELECT Count(*) FROM mytable;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.Fields(0) & " rows in MyTable"

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Ответ 3

Все, что вам нужно, это ADODB.Connection

Dim cnn As ADODB.Connection ' Requieres reference to the
Dim rs As ADODB.Recordset   ' Microsoft ActiveX Data Objects Library

Set cnn = CreateObject("adodb.Connection")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Access\webforums\whiteboard2003.mdb;"

Set rs = cnn.Execute(SQLQuery) ' Retrieve the data