Creo Parameter <=> PostgreSQL Table #3

VBA로 데이터베이스 테이블 값 가져오기:  ODBC 드라이브러를 이용하여, VBA로 데이터 베이스 값을 가져올수 있습니다.

기본 코드

Sub GetPostgreSQLData()
    '// 변수 선언
    Dim conn As Object
    Dim rs As Object
    Dim connectionString As String
    Dim query As String
    Dim ws As Worksheet
    Dim i As Integer
    
    '// 워크시트 지정
    Set ws = ThisWorkbook.Sheets("Sheet1") '// 데이터를 불러올 워크시트 지정
    
    '// PostgreSQL ODBC 연결 문자열
    connectionString = "Driver={PostgreSQL Unicode(x64)};" & _
                       "Server=localhost;" & _
                       "Port=5432;" & _
                       "Database=creomodel01;" & _
                       "Uid=postgres;" & _
                       "Pwd=****;"

    '// SQL 쿼리 작성 (스키마 : DesignTeam)
    query = "SELECT * FROM ""DesignTeam"".modelinfo;"  '// 가져올 테이블 이름 입력
    
    '// ADODB 객체 생성
    On Error GoTo ErrorHandler
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    '// 데이터베이스와 연결 열기
    conn.Open connectionString
    
    '// SQL 실행
    rs.Open query, conn, 1, 1 '// 1, 1은 adOpenKeyset과 adLockReadOnly를 의미
    
    '// 워크시트에 데이터 출력
    If Not rs.EOF Then
        '// 헤더 출력
        For i = 1 To rs.Fields.Count
            ws.Cells(1, i).Value = rs.Fields(i - 1).Name
        Next i
        
        '// 데이터 출력
        ws.Range("A2").CopyFromRecordset rs
    Else
        MsgBox "No data found in the table.", vbExclamation
    End If
    
    ' 연결 닫기
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "Data imported successfully!", vbInformation
    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description, vbCritical
    If Not rs Is Nothing Then rs.Close
    If Not conn Is Nothing Then conn.Close
End Sub

결과

---

▷코드 설명

1. 변수정의 내용

Dim conn As Object	Object는 모든 종류의 객체를 담을 수 있는 일반적인 데이터 형식입니다. ADODB (ActiveX Data Objects)를 사용하여 데이터베이스에 연결할 때, Connection 객체를 conn 변수에 저장하기 위해 As Object로 선언하는 것이 일반적입니다.
Dim rs As Object	(Recordset) 데이터베이스 쿼리 결과 집합을 저장하는 변수임을 나타냅니다.
Dim connectionString As String	(연결 문자열) "연결 문자열"이라는 의미를 담고 있으며, 데이터베이스 연결에 필요한 정보를 저장하는 변수임을 나타냅니다. 일반적으로 연결 문자열은 데이터베이스의 위치, 인증 정보 등을 포함합니다.
Dim query As String	(SQL 쿼리)

* ADODB (ActiveX Data Objects)는 Microsoft에서 개발한 기술로, 다양한 데이터 원본에 접근하기 위한 프로그래밍 인터페이스(API)입니다. 여러분의 프로그램(예: 엑셀 VBA, VB.NET, ASP 등)이 데이터베이스, 텍스트 파일, 스프레드시트 등 다양한 형태의 데이터에 일관된 방식으로 접근하고 조작할 수 있도록 도와주는 도구 모음이라고 생각하시면 됩니다.

2.  ADODB 객체 생성 코드

    '// ADODB 객체 생성
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

Set conn = CreateObject("ADODB.Connection")

"ADODB.Connection"이라는 이름의 데이터베이스 연결 기능을 가진 부품(객체)을 찾아서 조립(생성)하고, 그 부품을 사용할 수 있도록 손잡이(객체 참조)를 제공하는 역할을 합니다. 이 손잡이를 변수에 저장하여 데이터베이스 연결 작업을 수행할 수 있게 되는 것입니다. 즉, 데이터베이스와 통신하기 위한 연결 통로를 만드는 과정이라고 이해하면 쉽습니다.

Set rs = CreateObject("ADODB.Recordset")

Recordset 객체 생성: CreateObject("ADODB.Recordset")는 ADODB 라이브러리에서 Recordset 객체를 생성합니다. Recordset 객체는 데이터베이스에서 쿼리를 실행한 결과를 담는 역할을 합니다. 마치 엑셀 시트처럼 행과 열로 구성된 테이블 형태의 데이터를 저장할 수 있습니다.

객체 참조 할당: 생성된 Recordset 객체의 메모리 주소를 rs라는 변수에 할당합니다. 이제 rs 변수를 통해 생성된 Recordset 객체를 조작할 수 있습니다.

3. 쿼리를 실행하고, 그 결과를 Recordset 객체에 저장

 rs.Open query, conn, 1, 1

SQL 쿼리를 실행하고 그 결과를 Recordset에 저장하는 코드이며, 특히 1, 1은 adOpenKeyset과 adLockReadOnly를 의미하여, 다른 사용자의 수정은 반영하되 추가/삭제는 반영하지 않고, 데이터를 읽기만 가능하도록 설정합니다.

커서 유형:

• adOpenForwardOnly (0): 순방향 전용 커서. 레코드를 앞으로만 이동할 수 있습니다. 메모리 사용량이 적지만, 유연성이 떨어집니다.
• adOpenKeyset (1): 키 집합 커서. 위에서 설명한 대로, 다른 사용자의 수정은 반영하지만 추가/삭제는 반영하지 않습니다.
• adOpenDynamic (2): 동적 커서. 다른 사용자의 모든 변경 사항(추가, 수정, 삭제)을 반영합니다.
• adOpenStatic (3): 정적 커서. 쿼리 실행 시점의 데이터 스냅샷을 가져옵니다. 다른 사용자의 변경 사항이 반영되지 않습니다.

잠금 유형:

• adLockReadOnly (1): 읽기 전용. 데이터를 수정할 수 없습니다.
• adLockPessimistic (2): 비관적 잠금. 레코드를 편집하기 시작할 때 잠금을 걸어 다른 사용자가 동시에 수정하지 못하도록 합니다.
• adLockOptimistic (3): 낙관적 잠금. 레코드를 실제로 업데이트할 때만 잠금을 겁니다. 충돌 발생 가능성이 있지만, 동시성을 높일 수 있습니다.
• adLockBatchOptimistic (4): 배치 낙관적 잠금. 여러 레코드를 한 번에 업데이트할 때 사용합니다.

4. 워크시트에 데이터 출력

'// 워크시트에 데이터 출력
    If Not rs.EOF Then
        '// 헤더 출력
        For i = 1 To rs.Fields.Count
            ws.Cells(1, i).Value = rs.Fields(i - 1).Name
        Next i
        
        '// 데이터 출력
        ws.Range("A2").CopyFromRecordset rs
    Else
        MsgBox "No data found in the table.", vbExclamation
    End If

 

rs.EOF

Recordset 객체와 함께 사용되는 속성으로, "End Of File"의 약자입니다. 파일의 끝을 나타내는 것처럼, Recordset의 마지막 레코드 이후를 가리키는지를 나타냅니다.

ws.Range("A2").CopyFromRecordset rs

 

• .Range("A2"): 워크시트의 특정 범위를 나타냅니다. 여기서는 "A2" 셀을 시작점으로 지정합니다. 즉, Recordset의 데이터가 A2 셀부터 아래쪽과 오른쪽으로 채워집니다.
• .CopyFromRecordset: Range 객체의 메서드(기능)입니다. 이 메서드는 Recordset의 데이터를 지정된 범위에 복사합니다.
• rs: 이전에 rs.Open 등을 통해 데이터를 가져온 Recordset 객체를 나타내는 변수입니다. 이 Recordset 객체에 담긴 데이터가 Excel 시트로 복사됩니다.