PROGRAMMING-WORKSHOP

Access와 Excel의 연동_5|
엑셀테이블을 억세스DB 테이블로 만들기

이제 앞에서 엑셀시트에 랜덤으로 테이블을 만들고
관계형구조를 만드는 것을 시뮤레이션 해보았다
하지만, 엑셀의 장점이면서 약점이 되는 아무나 쉽게 테이블을 마음대로 수정편집해 버리면
관계를 물고 있는 키값들의 보안이 엉망이 된다
그리고 데이타를 통합문서에 너무 많이 보관하고 있는 것도 엑셀을 혹사시키는 것
데이타량이 많아지면 어디엔가 정보만 보관하는 것을 강구하는 것이 정석이다
그 어딘가에 보관할 곳이 바로 억세스 DB, 그렇게 사용하라고, Office에 들어 있는 DB인것
그래서 앞에서 랜덤으로 만든 테이블을 억세스테이블로 만들어 보자

억세스 DB는 이전 버전이 mdb 확장자의 것이 아니고, accdb확장자의 억세스로 하고
프로그래밍적으로 DB를 다루도록 해주는 개체는
DAO(Data Access Objects)를 사용하지 않고 ADO(ActiveX Data Objects)를 사용하도록 한다

DB는 Access를 포함하여 다양한 포멧의 DB가 세상에 널려있다
각 DB 포멧에 접근하기 위하여 Provider라는 것이 중요하다, 하나의 작은 프로그램이다
이페에지에서 하는 내용이 어느 분의 PC에서는 될수도 있고
어떤 분의 PC에서는 에러가 날수도 있다
버전에 따라서 Provider가 설치되었을수도 있고, 없을수도 있고
"provider=Microsoft.Jet.OLEDB.4.0;'"
라는 문자열 정보를 DB를 만들때 ADO에게 전달한다..이것이 에러가 난다면
"Provider=Microsoft.ACE.OLEDB.12.0;"
를 사용하시면 될 것이다
그래서 아래와 같이 DataBase만드는 프로시져를 하나 만들고


Sub createDB(sPathAndFile As String)
Dim oCatalog As Object
Dim sConString As String
On Error Resume Next
Set oCatalog = CreateObject("ADOX.Catalog")

sConString = "Provider=Microsoft.ACE.OLEDB.12.0;"
sConString = sConString & "Data Source=" & sPathAndFile
oCatalog.Create sConString
Set oCatalog = Nothing
Provider정보에서 에러가 나면 Provider정보를 위의 설명대로 바꿔서 하시기를..
If Err.Number <> 0 Then
    MsgBox Err.Description
End If
End Sub

위와 같이 CreateObject(...)로 하는 것보다는
코딩을 편리하게 하려면 라이브러리를 아래와 같이 참조시키고
하는 것이 좋다
이유는 ADO개체들의 구성원과 상수목록의 자원을 프로그래밍하면서 볼수 있기 때문에
편리하다
라이브러리를 참조하면 마치 엑셀의 개체를 다루면서 쉽게 구성원을 보는 것과 같은 것

아래는 폼이 로딩되면서 DB가 없으면 DB와 테이블을 모두 만들고,
데이타를 랜덤으로 입력하는 것을 한꺼번에 처리하도록 해보자

Sub createDBAndTable()
On Error Resume Next
Dim oCatalog As New ADOX.Catalog
Dim oConn As ADODB.Connection

Dim oTable As ADOX.Table
Dim sConString As String
Dim oKey As ADOX.Key
Dim oCol As ADOX.Column
Dim sSQL As String
Dim iX As Integer

'' Access 화일 만들기
oCatalog.Create sCon

'' 각각의 테이블 만들기

''고객테이블 만들기////////////////////////////
Set oTable = New ADOX.Table
oTable.Name = CUSTOMERS
With oTable.Columns
    .Append "CustomerID", adInteger
    .Append "CustomerName", adVarWChar, 20
    .Append "Address_1", adVarWChar, 30
    .Append "Phone", adVarWChar, 20
'' CustomerID는 테이블의 기본키값으로 자동입력되게 설정하자
'' 이하 모든 테이블의 첫째 휠드는 같은 형식으로 처리한다
    With !CustomerID
        Set .ParentCatalog = oCatalog
        .Properties("Autoincrement") = True
    End With
End With
'' 키값으로 지정하기 위하여서는 위에서 휠드를 만든 것으로 Key개체를 만들어서
'' 테이블의 Keys집합체에 등록하여야 한다
Set oKey = New ADOX.Key
oKey.Name = "PRIMARY"
oKey.Type = adKeyPrimary
oKey.Columns.Append "CustomerID"
oTable.Keys.Append oKey
oCatalog.Tables.Append oTable


'' 고객 데이타입력채우기////////////////////////////////////////////////
Set oConn = oCatalog.ActiveConnection
For iX = 1 To CUSTOMER_NUMS
    sSQL = "INSERT INTO " & CUSTOMERS & " (CustomerName,Address_1, Phone) values " & _
                                                "('고객명_" & Chr(64 + iX) & "','" & _
                                                Chr(Int(Rnd() * 26 + 65)) & Chr(Int(Rnd() * 26 + 65)) & Chr(Int(Rnd() * 26 + 65)) & "','" & _
                                                Int(Rnd() * 100) + 100 & "-" & Int(Rnd() * 1000) + 1000 & "')"
    oConn.Execute sSQL
    
Next

''이하 모든 테이블은 같은 요령이다
'' 애완동물테이블///////////////

Set oTable = New ADOX.Table
oTable.Name = PETS
With oTable.Columns
    .Append "PetID", adInteger
    .Append "CustomerID", adInteger
    .Append "PetName", adVarWChar, 30
    .Append "BirthDay", adDate
    .Append "State", adVarWChar, 30
    .Append "Type", adVarWChar, 10
    With !PetID
        Set .ParentCatalog = oCatalog
        .Properties("Autoincrement") = True
    End With
End With

Set oKey = New ADOX.Key
oKey.Name = "PRIMARY"
oKey.Type = adKeyPrimary
oKey.Columns.Append "PetID"
oTable.Keys.Append oKey
oCatalog.Tables.Append oTable


' 애완동물테이블 데이타입력
Const PET_Types As String = "Cat,Dog,Cat,Dog,Dog,Snake"
Dim arrPets As Variant
arrPets = Split(PET_Types, ",")
Dim sPetName As String
Dim sPetType As String
Dim iCustomerID As Integer
Dim datBirth As Date
Dim sPetState As String
For iX = 1 To PET_NUMS
'' SQL문을 작성하기 복잡한 경우는 변수를 사용하여 전달하면 덜 헷갈린다//////////
'' 안헷갈리면, 그냥 SQL문에 문자열로 위의 고객테이블과 같이 하여도 좋고/////////
    sPetName = arrPets(Int(Rnd() * 6)) & "_" & iX
    sPetType = Split(sPetName, "_")(0)
    iCustomerID = Choose(iX, 10, 1, 4, 3, 2, 5, 6, 8, 7, 9, 10, 11, 2, 3)
    datBirth = DateSerial(Year(Date) - Int(Rnd() * 6 + 1), Int(Rnd() * 12) + 1, Int(Rnd() * 30) + 1)
    sPetState = Array("A", "B", "C", "D", "E")(Int(Rnd() * 4))
    ''/////////////////////////////////////////////////////////////////////////////////////////////
    sSQL = "INSERT INTO " & PETS & " ( CustomerID, PetName,BirthDay,State,Type) values (" & _
                                                        iCustomerID & ",'" & _
                                                        sPetName & "',#" & Format(datBirth, "yyyy-mm-dd") & "#,'" & _
                                                        sPetState & "','" & sPetType & "')"
    oConn.Execute sSQL
Next


''진료타입테이블 ////////////
Set oTable = New ADOX.Table
oTable.Name = TREAT_TYPES
With oTable.Columns
    .Append "TreatID", adInteger
    .Append "TreatName", adVarWChar, 30
    .Append "Price", adCurrency
    With !TreatID
        Set .ParentCatalog = oCatalog
        .Properties("Autoincrement") = True
    End With
End With

Set oKey = New ADOX.Key
oKey.Name = "PRIMARY"
oKey.Type = adKeyPrimary
oKey.Columns.Append "TreatID"
oTable.Keys.Append oKey
oCatalog.Tables.Append oTable

For iX = 1 To TREAT_NUMS
    sSQL = "INSERT INTO " & TREAT_TYPES & " (TreatName,Price) values " & _
                                                "('진료타입_" & Chr(64 + iX) & "'," & _
                                                Choose(iX, 50000, 80000, 100000, 150000, 180000) & ")"
    oConn.Execute sSQL
Next
      
'' 진료진행테이블 ///////
Set oTable = New ADOX.Table
oTable.Name = WORKS
With oTable.Columns
    .Append "WorkID", adInteger
    .Append "PetID", adInteger
    .Append "TreatID", adInteger
    .Append "WorkDate", adDate
    With !WorkID
        Set .ParentCatalog = oCatalog
        .Properties("Autoincrement") = True
    End With
End With

Set oKey = New ADOX.Key
oKey.Name = "PRIMARY"
oKey.Type = adKeyPrimary
oKey.Columns.Append "WorkID"
oTable.Keys.Append oKey
oCatalog.Tables.Append oTable

Dim iTreat As Integer
Dim iCustomer As Integer
Dim iPet As Integer
Dim datWork As Date

For iX = 1 To WORK_NUMS
    iTreat = Int(Rnd() * TREAT_NUMS) + 1
    iPet = 2
    datWork = DateSerial(2016, Int(Rnd() * 12 + 1), Int(Rnd() * 30 + 1))
    sSQL = "INSERT INTO " & WORKS & " (PetID,TreatID,WorkDate) values " & _
                                                "(" & iPet & "," & iTreat & ",#" & datWork & "#)"
    oConn.Execute sSQL
    
Next


oConn.Close
Set oConn = Nothing
Set oTable = Nothing
Set oCatalog = Nothing

End Sub

위와 같이 하면 아래와 같이 통합문서와 같은 폴더내에 억세스화일이 만들어지고

필요한 테이블과 테이블에 데이타가 모두 채워졌다
엑셀에서 시트에 채웠던 것을 DB버전으로 모두 변경한 셈이다
이제 엑셀문서는 가벼워지고, 간결해진 셈이다

위의 코드는 테이블 하나만 이해하시면 된다
DB에서 중요한 것은 각각의 휠드의 데이타타입이다
이것은 아래의 그림과 같이 엑셀에서 상수이름을 몰라도 상수목록을 통하여 선택하면

정식풀경로는 아래와 같이
ADOX.DataTypeEnum. 에서 갖고 있는 상수목록이다

쉽게 입력되듯이 외부라이브러리를 참조하면 편리하게 코딩할수 있는 것이다
만약 CreateObject("개체명...")으로 하면 이런 편리함은 얻을수 없다
그런데 ADOX는 무엇이고 ADO는 뭔가 , 왜 복잡하게 두개가 있나??
ADOX는 순수하게 테이타베이스개체와 테이블개체등의 구조를 정의하는 라이브러리이고
한마디로 DataBase라는 집을 짓는 개체
ADO는 테이블등이 모두 완성된후 테이타를 관리하는 개체라이브러리인것이다

실은 한번 데이타베이스의 구조가 완성된후에는 쓸일이 없는 것이니..
데이타만을 다루는 것과 같이 있어 보았자, 자리만 차지하는 셈이니 자연스럽게
별도라이브러리를 구성하게 한 셈이다

아래 화일에서 실행하시면서 관찰해보시기를..
다음 화일에서는 UserForm에 DB의 내용을 연결하고 편집할수 있도록 하자

***[LOG-IN]***

PROGRAMMING WORKSHOP

UNO-SOLUTION

Access와 Excel의 연동_5|
엑셀테이블을 억세스DB 테이블로 만들기

PROGRAMMING WORKSHOP

UNO-SOLUTION

Access와 Excel의 연동_5| 엑셀테이블을 억세스DB 테이블로 만들기

Access와 Excel의 연동_5|
엑셀테이블을 억세스DB 테이블로 만들기