어떻게 연결 Excel로 Access 데이터베이스에 VBA를 사용하여
Access 데이터베이스는 많은 양의 데이터를 체계적으로 효과적으로 저장하는 관계형 데이터베이스 관리 시스템입니다. Excel은 데이터를 의미있는 정보로 분해하는 강력한 도구입니다. 그러나 Excel은 너무 많은 데이터를 저장할 수 없습니다. 그러나 Excel과 Access를 함께 사용하면 이러한 도구의 성능이 기하 급수적으로 증가합니다. 따라서 VBA를 통해 Access 데이터베이스를 데이터 원본으로 Excel에 연결하는 방법을 알아 보겠습니다.
Access 데이터베이스를 데이터 원본 Excel로 연결
1 : AcitveX 데이터 개체에 대한 참조 추가
ADO를 사용하여 데이터베이스에 액세스하기 위해 연결합니다. 따라서 먼저 ADO 개체에 대한 참조를 추가해야합니다.
VBA 프로젝트에 모듈을 추가하고 도구를 클릭하십시오. 참조를 클릭하십시오.
이제 Microsoft ActiveX Data Object Library를 찾으십시오. 가지고있는 최신 버전을 확인하십시오. 6.1이 있습니다. 확인 버튼을 클릭하면 완료됩니다. 이제 Access 데이터베이스에 대한 링크를 만들 준비가되었습니다.
2. VBA 코드를 작성하여 Access 데이터베이스에 대한 연결을 설정
Excel을 Access 데이터베이스에 연결하려면 Access 데이터베이스가 있어야합니다. 내 데이터베이스 이름은 “Test Database.accdb”입니다. “C : \ Users \ Manish Singh \ Desktop”위치에 저장됩니다. 이 두 변수는 중요합니다. 필요에 따라 변경해야합니다. 나머지 코드는 그대로 보관할 수 있습니다.
아래 코드를 복사하여 Excel VBA 모듈을 만들고 요구 사항에 따라 변경하십시오. 아래 코드의 각 줄을 설명했습니다.
Sub ADO_Connection()
'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString
'the query I want to run on the database.
query = "SELECT * from customerT;"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn
'clearing the content of the cells
Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
'closing the connections
rec.Close
conn.Close
End Sub
위의 코드를 복사하거나 아래 파일을 다운로드하고 요구 사항에 맞게 파일을 변경하십시오.
`link : /wp-content-uploads-2020-02-VBA-Database-Learning.xls [__ 파일 다운로드 : VBA 데이터베이스 학습]
이 VBA 코드를 실행하면 Excel에서 데이터베이스에 대한 연결을 설정합니다. 그런 다음 설계된 쿼리를 실행합니다. 시트의 이전 내용을 지우고 A 열을 데이터베이스의 필드 1 (두 번째 필드) 값으로 채 웁니다.
이 VBA 액세스 데이터베이스 연결은 어떻게 작동합니까?
Dim conn As New Connection, rec As New Recordset
위의 줄에서 우리는 연결 및 레코드 세트 변수를 선언하는 것뿐만 아니라 New 키워드를 사용하여 직접 초기화합니다.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
이 두 줄은 참가자입니다. DBPATH는 데이터베이스에서만 변경됩니다. PRVD가 OLE DB 공급자를 연결하고 있습니다.
conn.Open connString
이 줄은 데이터베이스에 대한 연결을 엽니 다. Open은 여러 인수를 사용하는 연결 개체의 기능입니다. 첫 번째 필수 인수는 ConnectingString입니다. 이 문자열에는 OLE DB 공급자 (여기서는 PRVD)와 데이터 원본 (여기서는 DBPATH)이 포함됩니다. 보호 된 데이터베이스에 대한 선택적 인수로 admin 및 password를 사용할 수도 있습니다.
Connection.Open의 구문은 다음과 같습니다.
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
데이터베이스에 ID와 비밀번호가 없으므로 ConnectionString 만 사용합니다. ConnectionString의 형식은 “Provider = provider_you want to use; _ Data Source = _Fully Qualified name of database“입니다. 이 문자열을 inconnString 변수로 만들고 저장했습니다.
query = "SELECT * from customerT;"
이것은 데이터베이스에서 실행하려는 쿼리입니다. 원하는 쿼리를 가질 수 있습니다.
rec.Open query, conn
이 문은 정의 된 연결에서 정의 된 쿼리를 실행합니다. 여기에서는 레코드 세트 개체의 Open 메서드를 사용합니다. 모든 출력은 레코드 세트 objectrec에 저장됩니다. 레코드 세트 개체에서 값을 조작하거나 삭제할 수 있습니다.
Cells.ClearContents
이 줄은 시트의 내용을 지 웁니다. 즉, 시트의 셀에서 모든 항목을 삭제합니다.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
위의 행 세트는 레코드 세트가 비어 있는지 여부를 확인합니다. 레코드 세트가 비어 있지 않으면 (쿼리가 일부 레코드를 반환했음을 의미 함) 루프가 시작되고 // cells-ranges-rows-and-columns-in-에서 필드 1의 각 값 (이 경우 두 번째 필드, 이름)을 인쇄하기 시작합니다. vba / 3-best-ways-to-find-last-non-blank-row-and-column-using-vba.html [열의 마지막 미사용 셀]`.
(이것은 단지 설명으로 사용됩니다. 이러한 줄이 없을 수도 있습니다. 데이터베이스에 대한 연결을 열려면이 줄 위에있는 VBA 코드로 충분합니다.)
rec.EOF를 사용하여 레코드 집합이 끝날 때까지 루프를 실행했습니다. rec.MoveNext는 다음 레코드 집합으로 이동하는 데 사용됩니다. rec.Fields (1)은 필드 1에서 값을 가져 오는 데 사용됩니다 (필드 인덱싱이 0에서 시작하는 두 번째 필드입니다. 제 데이터베이스에서 두 번째 필드는 고객의 이름입니다).
rec.Close conn.Close
마지막으로 rec 및 conn에서 원하는 모든 작업이 완료되면 닫습니다.
특정 연결을 개별적으로 열고 닫으려면 이러한 라인을 별도의 서브 루틴에 둘 수 있습니다.
자, 여러분, 이것은 ADO를 사용하여 ACCESS 데이터베이스에 연결하는 방법입니다. 다른 방법도 있지만 이것이 VBA를 통해 액세스 데이터 소스에 연결하는 가장 쉬운 방법입니다. 최대한 자세히 설명했습니다. 아래 댓글 섹션에서 이것이 도움이되었는지 알려주세요.
관련 기사 :
link : / import-and-export-in-vba-use-a-closed-workbook-as-a-database-dao-using-vba-in-microsoft-excel [닫힌 통합 문서를 데이터베이스 (DAO)로 사용 Microsoft Excel에서 VBA 사용]| 닫힌 통합 문서를 DAO 연결이있는 데이터베이스로 사용하려면 Excel에서이 VBA 조각을 사용하십시오.
link : / import-and-export-in-vba-use-a-closed-workbook-as-a-database-ado-using-vba-in-microsoft-excel [닫힌 통합 문서를 데이터베이스로 사용 (ADO) Microsoft Excel에서 VBA 사용]| 닫힌 통합 문서를 ADO 연결이있는 데이터베이스로 사용하려면 Excel에서이 VBA 코드 조각을 사용하십시오.
link : / applications-word-outlook-in-vba-getting-started-with-excel-vba-userforms [Getting Started With Excel VBA UserForms]| * 데이터베이스에 데이터를 삽입하기 위해 양식을 사용합니다. Excel 사용자 양식은 사용자로부터 정보를 얻는 데 유용합니다. 다음은 VBA 사용자 양식으로 시작하는 방법입니다.
===
link : / user-forms-input-boxes-in-vba-change-the-valuecontent-of-several-userform-controls-using-vba-in-microsoft-excel [여러 UserForm 컨트롤의 값 / 내용 변경 Excel에서 VBA 사용]| * 사용자 양식 컨트롤의 내용을 변경하려면이 간단한 VBA 스 니펫을 사용하십시오.
link : / user-forms-input-boxes-in-vba-prevent-a-userform-from-closing- when-the-user-clicks-the-x-button-using-vba-in-microsoft-excel [ Excel에서 VBA를 사용하여 사용자가 x- 버튼을 클릭 할 때 사용자 폼이 닫히지 않도록 방지]| 사용자가 폼의 x 버튼을 클릭 할 때 사용자 폼이 닫히는 것을 방지하기 위해 UserForm_QueryClose 이벤트를 사용합니다.
인기 기사 :
link : / keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [50 Excel 단축키로 생산성 향상]| 작업 속도를 높이십시오. 이 50 개의 바로 가기를 사용하면 Excel에서 더 빠르게 작업 할 수 있습니다.
link : / formulas-and-functions-introduction-of-vlookup-function [Excel의 VLOOKUP 함수]| 이것은 다른 범위와 시트에서 값을 조회하는 데 사용되는 Excel의 가장 많이 사용되고 인기있는 기능 중 하나입니다.
link : / tips-countif-in-microsoft-excel [Excel 2016의 COUNTIF]| 이 놀라운 기능을 사용하여 조건으로 값을 계산합니다. 특정 값을 계산하기 위해 데이터를 필터링 할 필요가 없습니다.
Countif 기능은 대시 보드를 준비하는 데 필수적입니다.
link : / excel-formula-and-function-excel-sumif-function [Excel에서 SUMIF 함수 사용 방법]| 이것은 또 다른 대시 보드 필수 기능입니다. 이를 통해 특정 조건에 대한 값을 합산 할 수 있습니다.