VBA를 사용하여 MS-Excel(2010)에서 MS-Access Table을 쿼리하는 방법

VBA를 사용하여 MS-Excel(2010)에서 MS-Access Table을 쿼리하는 방법

엑세스의 테이블을 쿼리하기 위해 엑셀에 vba 코드를 작성하려고 합니다.추가된 링크와 같은 여러 코드 샘플을 시도해 보았는데 "연결 열기" 부분에서 모두 실패한 것 같습니다.다른 참조를 사용해 보았지만 어떤 것을 사용해야 하는지, 일부 버전 간의 차이점은 무엇인지 잘 모르겠습니다(예:Microsoft ActiveX Data Objects 2.0, 2.1, ..., 6.0) 또는 제공자 정보를 지정해야 합니다.공급자 정보의 경우 대개 다음과 같은 것을 확인할 수 있습니다.

"제공자=솔루션.Jet.OLEDB.4.0;데이터 소스="

그러나 위에 표시된 공급자 문자열의 조건이 변경되어야 하는 이유/어떤 조건을 사용해야 하는지 잘 모르겠습니다.이런 일을 제대로 하는 방법을 가르쳐 주실 수 있나요?

참고: 가능하다면 다른 애플리케이션을 다운로드하지 않고도 작동할 수 있고 2007년과 2010년 버전의 Access와 Excel 모두에서 작동할 수 있는 솔루션을 원합니다. 이는 사무실 버전이 서로 다른 다른 컴퓨터에서 실행되어야 하기 때문입니다.

유사한 질문에 대한 링크:액세스하기 위한 Excel VBA 쿼리가 실패했습니다. 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 값 =

공급자 = 공급자입니다.Jet.OLEDB.4.0;데이터 소스=C:\사용자\bwall\데스크탑\Excel 쿼리 액세스 테스트\Masterlist_Current_copy.accdb;

공급자 조각은 다음과 같아야 합니다.Provider=Microsoft.ACE.OLEDB.12.0대상 데이터베이스가 ACCDB 형식인 경우.Provider=Microsoft.Jet.OLEDB.4.0이전 MDB 형식에서만 작동합니다.

32비트 Windows(윈도우)를 실행하는 경우에는 액세스를 설치할 필요가 없습니다.Jet 4는 운영 체제의 일부로 포함되어 있습니다.64비트 Windows(윈도우)를 사용하는 경우 Jet 4는 포함되어 있지 않지만 액세스 자체를 설치할 필요는 없습니다.Microsoft Access Database Engine 2010 재배포 가능을 설치할 수 있습니다.일치하는 버전(Access Database Engine)을 다운로드해야 합니다.32비트 윈도우즈의 경우 exe, 64비트의 경우 AccessDatabaseEngine_x64.exe).

참조가 필요 없는 지연 바인딩을 사용하면 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

이 대답으로 문제가 해결되지 않으면 질문을 편집하여 사용하려는 전체 연결 문자열과 해당 연결 문자열에 대한 응답으로 표시되는 정확한 오류 메시지를 표시합니다.

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

당신은 ADODB만 있으면 됩니다.연결

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

Option Explicit

Const ConnectionStrngAccessPW As String = _"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\BARON\Desktop\Test_DB-PW.accdb;
Jet OLEDB:Database Password=123pass;"

Const ConnectionStrngAccess As String = _"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\BARON\Desktop\Test_DB.accdb;
Persist Security Info=False;"

'C:\Users\BARON\Desktop\Test.accdb

Sub ModifyingExistingDataOnAccessDB()

    Dim TableConn As ADODB.Connection
    Dim TableData As ADODB.Recordset


    Set TableConn = New ADODB.Connection
    Set TableData = New ADODB.Recordset

    TableConn.ConnectionString = ConnectionStrngAccess

    TableConn.Open

On Error GoTo CloseConnection

    With TableData
        .ActiveConnection = TableConn
        '.Source = "SELECT Emp_Age FROM Roster WHERE Emp_Age > 40;"
        .Source = "Roster"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
        On Error GoTo CloseRecordset

            Do Until .EOF
                If .Fields("Emp_Age").Value > 40 Then
                    .Fields("Emp_Age").Value = 40
                    .Update
                End If
                .MoveNext
            Loop
            .MoveFirst

        MsgBox "Update Complete"
    End With


CloseRecordset:
    TableData.CancelUpdate
    TableData.Close

CloseConnection:
    TableConn.Close

    Set TableConn = Nothing
    Set TableData = Nothing

End Sub

Sub AddingDataToAccessDB()

    Dim TableConn As ADODB.Connection
    Dim TableData As ADODB.Recordset
    Dim r As Range

    Set TableConn = New ADODB.Connection
    Set TableData = New ADODB.Recordset

    TableConn.ConnectionString = ConnectionStrngAccess

    TableConn.Open

On Error GoTo CloseConnection

    With TableData
        .ActiveConnection = TableConn
        .Source = "Roster"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
        On Error GoTo CloseRecordset

        Sheet3.Activate
        For Each r In Range("B3", Range("B3").End(xlDown))

            MsgBox "Adding " & r.Offset(0, 1)
            .AddNew
            .Fields("Emp_ID").Value = r.Offset(0, 0).Value
            .Fields("Emp_Name").Value = r.Offset(0, 1).Value
            .Fields("Emp_DOB").Value = r.Offset(0, 2).Value
            .Fields("Emp_SOD").Value = r.Offset(0, 3).Value
            .Fields("Emp_EOD").Value = r.Offset(0, 4).Value
            .Fields("Emp_Age").Value = r.Offset(0, 5).Value
            .Fields("Emp_Gender").Value = r.Offset(0, 6).Value
            .Update

        Next r

        MsgBox "Update Complete"
    End With


CloseRecordset:
    TableData.Close

CloseConnection:
    TableConn.Close

    Set TableConn = Nothing
    Set TableData = Nothing

End Sub

언급URL : https://stackoverflow.com/questions/7842607/how-to-query-a-ms-access-table-from-ms-excel-2010-using-vba