엑셀에서 VBA로 ADOX를 다뤄보기

ADOX는 DB를 만들고, Table을 만들어주는 개체이다
ADO는 이미 만들어진 테이블에 정보를 넣고,받고 ,분석하는 일들을
하지만 DB를 정의하는 일을 하는 개체는 ADOX라이브러리에서 한다
이것을 VBA로 엑셀에서 처리해보도록 하자



DAO를 사용할때는 DAO에서 DB와 Table의 정의도 같이 하였지만
ADO에서는 DB와 Table의 정의는 별도의 개체를 사용하기 때문에 두개의
라이브러를 참조 시켜야 한다

앞페이지의 WindowForm에서 하던것과 좀 다르게 해서 경험을 좀더 하도록하자
앞페이지에서는 ADOX로 단순히 DB만 만들었고
Table을 만드는 것은 SQL문으로 ADO.Net을 활용했어지만
첨부한 엑셀화일에서는 ADOX의 Table이라는 개체를 활용해 보도록 하자
(물론 이것도 windowform에서 당연히 할수 있는 것이고)

Option Explicit
외부 상수로 DB경로와 Table명을 준비하고
Const DB_FILE As String = "\myDBCreatedWithADOX.accdb"
Const TBL_NAME As String = "myTableCreatedWithADOX"

Sub createDBSystem()3가지 작업을 해보자..
deleteExistingDB'DB를 삭제하고
createTableWithADOX'DataBase와 Table구조를 만들고
fillDataToTable엑셀의 범위정보를 읽어서 테이블을 채운다
End Sub

Sub deleteExistingDB()
On Error Resume Next
VBA.Kill ThisWorkbook.Path & DB_FILE
End Sub

Sub createTableWithADOX()
Dim oADOX As New ADOX.Catalog'Catalog는 DB를 말하는 것
Dim oADOTable As New ADOX.Table'Table개체를 만들고 
oADOX.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & DB_FILE'DB를 생성하고
oADOTable.Name = TBL_NAME'테이블이름 주고


With oADOTable.Columns'Table의 휠드를 엑셀범위의 열머리를 읽어서 만든다
        .Append "ID", ADOX.DataTypeEnum.adInteger'Columns.Append메소드로 휠드명과 데이타타입과 데이타의 크기를 매개변수로 준다
        .Append "식당명", ADOX.DataTypeEnum.adVarWChar, 30
        .Append "전화번호", ADOX.DataTypeEnum.adVarWChar, 20
        .Append "위치", ADOX.DataTypeEnum.adVarWChar, 30
        .Append "구분", ADOX.DataTypeEnum.adVarWChar, 30
        .Append "용도", ADOX.DataTypeEnum.adVarWChar, 30
        .Append "추천별점", ADOX.DataTypeEnum.adVarWChar, 10
        .Append "평가1", ADOX.DataTypeEnum.adVarWChar, 30
        .Append "평가2", ADOX.DataTypeEnum.adLongVarWChar
        .Append "가본곳", ADOX.DataTypeEnum.adBoolean
        With !ID'ID휠드는 특별한 속성을 준다
            .ParentCatalog = oADOX'어떤 DB의 테이블인지 지정하고
            .Properties("Autoincrement").Value = True'ID휠드는 자동일련번호로 하게 한다
        End With
End With
oADOX.Tables.Append oADOTable
'외부라이브러리를 사용할때는 모든 변수를 사용후 끊어주어야 한다
' 특히 Connection 개체가 열려있으면 메모리상에 억세스와 엑셀이 보이지 않게 연결되어 있다
Set oADOX.ActiveConnection = Nothing
Set oADOTable = Nothing
Set oADOX = Nothing

End Sub


Private Sub fillDataToTable()'만들어진 테이블에 정보채우기
On Error Resume Next

Dim oCon As New ADODB.Connection'ADOX가 아닌 ADODB라이브러리를 활용하게 된다
Dim oCom As New ADODB.Command
Dim rTbl As Range
Dim rHeadRow As Range

Dim rRow As Range
Dim sStr As String

oCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & DB_FILE
oCon.Open'Connection개체 열어주고
oCom.ActiveConnection = oCon'작업을 하게 되는 Command개체의 ActiveConnection에 Connection개체를 준다

Set rTbl = Worksheets("restaurants").Range("A1").CurrentRegion'정보를 채울 엑셀의 범위
Set rHeadRow = rTbl.Rows(1)'DB Table의 휠드명에 해당하는 첫째행을 잡아서
Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)
Dim sSQL_Head As String
Dim iCol As Integer
Dim bVisit As Boolean

sSQL_Head = "INSERT INTO " & TBL_NAME & "("'SQL문을 작성한다
For iCol = 2 To rHeadRow.Columns.Count'순환하면서 각열머리명을 SQL 문에 붙여간다
    sSQL_Head = sSQL_Head & rHeadRow.Columns(iCol).Value & ","
Next
sSQL_Head = Left(sSQL_Head, Len(sSQL_Head) - 1) & ") VALUES"


For Each rRow In rTbl.Rows'범위의 각각의 행을 순환하면서
    bVisit = IIf(rRow.Cells(10) <> "", True, False)'행의 각셀의 값을 SQL문에 붙여나간다
      sStr = sSQL_Head & "('" & rRow.Cells(2) & "','" & _
                                               rRow.Cells(3) & "','" & _
                                               rRow.Cells(4) & "','" & _
                                               rRow.Cells(5) & "','" & _
                                               rRow.Cells(6) & "','" & _
                                               rRow.Cells(7) & "','" & _
                                               rRow.Cells(8) & "','" & _
                                               rRow.Cells(9) & "'," & bVisit & ")"
      oCom.CommandText = sStr'완성된 SQL문은 Command개체의 CommandText속성에 주고
	  oCom.Execute'마지막 명령,DB의 Table에 하나의 행이 추가된다
      
Next

'외부라이브러리를 사용할때는 모든 변수를 사용후 끊어주어야 한다
' 특히 Connection 개체가 열려있으면 메모리상에 억세스와 엑셀이 보이지 않게 연결되어 있다
Set oCom.ActiveConnection = Nothing
Set oCom = Nothing'나머지 개체들도 모두 죽여버린다
oCon.Close
Set oCon = Nothing

End Sub

***[LOG-IN]***