Запись Excel VBA для получения данных из Access

Я пишу приложение excel, которое извлекается из базы данных Access для работы. Когда пользователь открывает инструмент Excel, таблица данных должна заполнять один из рабочих листов из базы данных Access, которую я создал. Я писал код VBA в excel, и я получаю ошибку во время выполнения: "429" компонент ActiveX не может создать объект.

Остальные вопросы написаны в Access, но я считаю, что мне нужен этот код, написанный из файла рабочей книги Excel. Код, который я написал, находится в функции Workbook_Open(), так что данные собираются правильно, когда пользователь открывает файл. Большое спасибо за помощь. BTW, я использую Access 2007 и Excel 2010.

Private Sub Workbook_Open()
    'Will fill the first listbox with data from the Access database
    Dim DBFullName As String
    Dim TableName As String
    Dim FieldName As String
    Dim TargetRande As String

    DBFullName = "D:\Tool_Database\Tool_Database.mdb"

    Dim db As DAO.Database, rs As Recordset
    Dim intColIndex As Integer

    Set TargetRange = Range("A1")
    Set db = OpenDatabase(DBFullName)
    Set rs = db.OpenRecordset("SELECT * FROM ToolNames WHERE Item = 'Tool'", dbReadOnly)

    ' Write the field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
    Next

    ' Write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs

    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Ответ 1

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

'~~> Remove all references as the below code uses Late Binding with ADO.

Private Sub Workbook_Open()
          Dim cn As Object, rs As Object
          Dim intColIndex As Integer
          Dim DBFullName As String
          Dim TargetRange As Range

10        DBFullName = "D:\Tool_Database\Tool_Database.mdb"

20        On Error GoTo Whoa

30        Application.ScreenUpdating = False

40        Set TargetRange = Sheets("Sheet1").Range("A1")

50        Set cn = CreateObject("ADODB.Connection")
60        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

70        Set rs = CreateObject("ADODB.Recordset")
80        rs.Open "SELECT * FROM ToolNames WHERE Item = 'Tool'", cn, , , adCmdText

          ' Write the field names
90        For intColIndex = 0 To rs.Fields.Count - 1
100           TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
110       Next

          ' Write recordset
120       TargetRange.Offset(1, 0).CopyFromRecordset rs

LetsContinue:
130       Application.ScreenUpdating = True
140       On Error Resume Next
150       rs.Close
160       Set rs = Nothing
170       cn.Close
180       Set cn = Nothing
190       On Error GoTo 0
200       Exit Sub
Whoa:
210       MsgBox "Error Description :" & Err.Description & vbCrLf & _
             "Error at line     :" & Erl & vbCrLf & _
             "Error Number      :" & Err.Number
220       Resume LetsContinue
End Sub

Ответ 2

Оба DAO и ADO включают типы объектов набора записей. Однако они несовместимы. Ваше объявление для переменной объекта rs неоднозначно.

Dim db As DAO.Database, rs As Recordset

Потенциальная проблема заключается в том, что если ваш проект включает ссылку на ADO, а приоритет этой ссылки выше вашего DAO-ссылки, rs завершается как набор записей ADO, а не набор записей DAO.

Я не уверен, что это причина ошибки, которую вы видите. Однако установка rs в db.OpenRecordset(something) должна завершиться неудачей, если rs - это набор записей ADO, потому что OpenRecordset возвращает набор записей DAO.

Я думаю, вы должны изменить объявление на это:

Dim db As DAO.Database, rs As DAO.Recordset

Даже если это изменение не решит вашу проблему, я рекомендую вам всегда квалифицировать тип при объявлении переменных объекта набора записей..., чтобы избежать двусмысленности.

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

Вот еще один красный флаг:

Dim TargetRande As String

Позже у вас есть:

Set TargetRange = Range("A1")

Добавьте Option Explict в раздел Declarations вашего модуля. Затем выберите Debug- > Compile в главном меню редактора VB. Это усилие будет отображать имена переменных с ошибками, а также предупреждать вас о синтаксических ошибках.

Ответ 3

Он отлично работает на моей машине (за исключением того, что имена полей перезаписываются первой строкой данных). Для имен полей вы, вероятно, имеете в виду TargetRange.Offset(0, intColIndex)).

Есть ли у вас Инструменты → Ссылки... в библиотеку объектов Microsoft DAO 3.6?

Возможно, вы используете 64-разрядную версию Excel 2010 (проверьте в разделе "Файл- > Справка" в разделе "О Microsoft Excel" )? Если это так, библиотеки старой библиотеки DAO не будут работать, и вам потребуется установить 64-разрядную библиотеку ACE DAO, которая доступна для 64-разрядных версий.