엑셀+억세스데이타베이스+UserForm
uno-weekly의 한페이지로서 진행하던 엑셀과 억세스테이타베이스를
활용한 업무자동화도구 만들기를
별책으로 구분하여 정리하여 올려 놓고 있습니다
매주 하나씩..
다 마치면 억세스와 엑셀의 연동의 문제는 마스터하시게 될 것입니다
물론 워크북 하나도 빼먹지 말고 충실히 잘 진행할때만 가능합니다
모르는 것이나 도저히 이해 못할 소리가 있으면 항상 메일하세요!!
외부데이타 연습용화일/NorthWind.mdb
XL-DB_025| XL-DB-UserForm_25
게으름을 피우다가..XL-DB시리즈를 몇주 놓쳤다
25번째 화일 올린다..
정보는 조회,수정,입력,삭제가 기본중의 기본..삭제를 할차례였었다
오랫만에 화일을 열어보니 몇가지 오류가 있다
데이타베이스의 중요한 Null값을 처리하는 것을 추가하여 에러를 막고
삭제를 하려면 레코드의 ID값을 알아야 하는데 ID값이 콤보상자속에
숨어 있다
이것을 찾는 것 까지만 하도록 하자..
많이 하면 질리니까..!!
XL_DB_659_025.
XL-DB_024| XL-DB-UserForm_24
새로운 정보를 입력할때
DAO의 메소드를 사용하여 입력해보았었다
이번에는 문자열정보를 잘 다듬을줄 아는 내공이 필요한
SQL문으로 새로운 정보를 엑셀에서 데이타베이스에
입력해 보도록 하자
시간정보를 문자열로 표현할때
숫자를 문자열로 표현할때
변수를 받아서 문자열 속에 조합할때
콘트롤의 값을 직접문자열속에 조합할때
문자를 문자열로 표현할때등을 잘 챙기는 것이 문자열정보의 내공을
키우는 것이다
XL_DB_643_024.
XL-DB_023| XL-DB-UserForm_23
정보를 입력,편집하는 도구는
유효성검사를 잘 할수있게 설계 하는 것이 중요하다
DAO를 불러서 정보를 저장하기 전에
UserForm의 각정보에 해당 하는 콘트롤의 값들이 유효한 정보인지
미리 검증을 하는 습관이 좋다
억세스가 갖고 있는 폼의 기능은 엑셀에서의 UserForm기능보다 지능적으로
뛰어나다고 할 수 있는 것은 테이블과 연결된 폼이
테이블의 설계상의 정보상태를 읽어서 유효성검사를 자동화하고
있다는 것이 다른 것이다
엑셀에서 UserForm을 사용하여 입력창으로 활용한다면
VBA로 적절히 검증절차를 설계하여야 하는 수고를 하여야 할 것이다
어떤 면에서 능숙하다면, 이렇게 VBA로 처리하여 검증하는 것이
좀더 융통성이 있고 좋다고 할 수도 있다
콤보상자에 사용할 목록은 억세스의 정보를 불러서
활용할수도 있겠지만
요령껏 엑셀의 워크시트에 필요한 목록정보를 보관하고
불러서 사용하는 것이 좀더 융통성이 있고 효율적일것이다
XL_DB_642_023.
XL-DB_022| XL-DB-UserForm_22
데이타베이스를 엑셀에서 다루는데 있어서는
데이터를 뽑아서 보는 SELECT 문
데이터를 수정하는 UPDATE문
데이터를 새로입력하는 INSERT문
데이터를 삭제하는 DELETE문
더 이상 무엇이 있겠는가
SELECT문에서 다양한 변형이 있겠으니
단순한 조회와 분석및 보고서를 만드는 부분이나 미래예측을 위한
지난 정보를 검토하기 위한 정보를 뽑거나 모두 SELECT 문이다
그것에 비한다면 UPDATE나 INSERT나 DELETE는 단순하다
물론 데이타베이스 자체의 관리를 위한 SQL문은 좀더 복잡하겠지만
엑셀에서 정보를 다루는 것은 별로 복잡할 것이 없다
이번주에는 신규정보를 저장하는 것을 해보자
지지난주의 정보의 수정저장을 하는 프로시져를 몇줄만
수정하면 수정하는 것이나 신규저장하는 것이나 같은 프로시져를
활용해도 된다
그런것을 해보도록 하자
XL_DB_640_022.
XL-DB_021| XL-DB-UserForm_21
지난주에 DAO의 Update메소드로 정보의 수정저장을 했었다
그런데 SQL문으로는 UPDATE하는 방법이 없나???
물론 있다..
그것을 이번주에 해보도록 하자
그냥 빼고 넘어가려다가..
나중에 Access가 아닌 SQL을 다룰일이 생긴다면
반드시 알고 있어야 할 것들이라서 이번주에는 SQL의 UPDATE문으로
처리해 보고
눈이 뱅뱅도는 문자열정보의 작성의 내공을 키워 보시기 바란다
Dim sSQL As String
sSQL = "UPDATE Employees SET lastname='" & Trim(Me.txtName.Text) & "'," & _
"firstname='" & Trim(Me.txtNameEn) & "'," & _
"address='" & Trim(Me.txtAddress) & "'," & _
"birthdate=" & CDate(Trim(Me.txtBirth)) & "," & _
"hiredate=" & CDate(Trim(Me.txtHire)) & "," & _
"notes='" & Trim(Me.txtMemo) & "'," & _
"extension='" & Trim(Me.txtInternal) & "'," & _
"postalcode='" & Trim(Me.txtPost) & "'," & _
"country='" & Trim(Me.txtNation) & "'," & _
"homephone='" & Trim(Me.txtPhone) & "'," & _
"title='" & Trim(Me.txtTitle) & "'," & _
"region='" & Trim(Me.txtRegion) & "'," & _
"titleOfCourtesy='" & IIf(Me.optMan, "남", "여") & "'," & _
"reportsto=" & Me.cboReportTo.List(Me.cboReportTo.ListIndex, 0) & _
" WHERE employeeID=" & lID
문자열로 정보의 전달에 대한 내공이 쌓이는 것이
프로그래밍의 내공!!!
꼭 직접작성해 보시기를!!
XL_DB_639_021.
XL-DB_020| XL-DB-UserForm_20
지난주의 것에서
아래와 같은 것이 처리가 안되었었다
선택된 직원과 해당직원의 직속상관이 달라야 하는데
멍청하게 해당직원의 ID로 콤보상자를 처리하여 후속적으로 잘못된
정보처리가 되었다
수정하고
해당직원을 수정하여 저장하는 작업을
DAO의
Edit메소드와 Update메소드를 사용하여 간단하게 처리해보자
데이타베이스에 정보를 전달할때 가장 중요한 점은
정보의 타입이라는 점이다
데이타베이스는 모두 쉬운데..귀찮고 짜증나는 부분이(초보때)
정보의 타입이 안맞으면 가차없이 에러를 낸다는 것
사용자인터페이스상의 것은(UserForm)텍스트박스가 주된 것이라서
모두 정보가 문자열정보다
이것을 DB가 설계된 정보의 타입대로 변환하여 전달하여 주어야
한다는 점이 DB의 내공이 얼마나 쌓였냐??의 가름이 된다
특히나 이놈 저놈 자동으로 변환하여 잘 받아주는
엑셀시트에 중독이 된 사람들에게는
특히나 짜증나는 부분이다
XL_DB_638_020.
XL-DB_019| XL-DB-UserForm_19
이번주에는 목록상자에서 직원을 선택하면
선택된 직원의 정보를 각각의 텍스트박스에 옮기고
[수정모드]로 변환하는 작업을 해보자
다시 [새정보]버튼을 크릭하면 텍스트박스의 내용을 모두 지우고
[신규입력모드]로 변환시키는 작업을 하자
초보님들이 화일을 헷갈려 하여
관심이 있는 분들만 이동하여 다운 받으시기를..
XL_DB_637_019.
XL-DB_018| XL-DB-UserForm_18
이번주에는 목록상자에서 직원을 선택하면
선택된 직원의 정보를 각각의 텍스트박스에 옮기고
[수정모드]로 변환하는 작업을 해보자
다시 [새정보]버튼을 크릭하면 텍스트박스의 내용을 모두 지우고
[신규입력모드]로 변환시키는 작업을 하자
초보님들이 화일을 헷갈려 하여
관심이 있는 분들만 이동하여 다운 받으시기를..
XL_DB_636_018.
XL-DB_017|UserForm으로 데이타베이스 편집창 만들기
목록상자와 콤보상자에 DAO에서 직원테이블의 정보를 갖여다가 담고,
카렌다컨트롤을 외부라이브러리에서 참조하여 그려넣고
날짜를 요하는 콘트롤을 선택할때
목록상자가 작아지고 카렌다콘트롤가 타나나게 한다
XL_DB_635_017.
XL-DB_016| UserForm으로 데이타베이스 편집창 만들기
지금까지 SQL문으로 이미 데이타베이스에 저장된 내용을
분석하고 조회하는 작업을 해보았다
이제 데이타베이스의 정보를 수정하기도 하고 신규입력하기도 하는 작업을
해보도록 하자
이때 입력장치를 잘 만드는 것이 하나의 과정이 될 것이다
워크시트를 활용하여 만들수도 있겠지만
UserForm의 활용을 숙달시키기 위하여 계속 UserForm으로 편집창을 만들어
보도록 하자
이 씨리즈를 처음부터 마음먹고 바짝 달라붙으신 분들은 이 씨리즈가 마칠때 쯤되면
데이타베이스와 엑셀을 사용한 소루션을 만들수 있는 파워를 갖추게 될 것이다
이제 UserForm을 다루는 테크닉을 좀더 갖추어 가보도록 하자
UNO-Daily라는 작은 소루션을 만들어서 사용자님들에게 배포하면서
느끼는 것이 UserForm을 잘 다루는 테크닉은 다른 환경에서의
Form작업에도 연결이 된다는 생각을 하게 되고 ..
좀더 이 씨리즈에 열정을 부어 드려야 하겠다는 생각이 들게 하였다
우선 UserForm은 콘트를올 한꺼번에 다루는 기능이 없다
컨트롤을 통제하려면 하나,하나 다루어야 한다..
그런데 Class모듈을 활용하면 한꺼번에 콘트롤을 처리할수 있게 된다
또한 사용자정의 오브젝트의 생성(Class모듈의 활용)은 나중에 VB.Net이나
다른 언어를 하게 된다고 하더라도 기본이 되는 사항이니 잘 챙겨나가시기 바란다
XL_DB_634_016.
XL-DB_015 | 데이타테이블에 대하여 하나 질문과 답
이번주에는 진행 잠깐쉬고 질문 하나 처리하고 가자
억세스로 업무의 도구를 만드는 것 까지는 좋은데
테이블의 설계가 참으로 헷갈려 한다
테이블설계에서 가장 중요한점은 중복되는 정보가 최대한 들어가지 않게 한다
또한 테이블의 행과 열사이에 빈셀이 있게 설계가 되었다면
이것도 잘못된 테이블 설계다
빡빡하게 유일한 정보들로 만들어졌는지 확인해 보셔야 한다
만약 중복되는 것이 있다면 이것은 잘못된 테이블 설계가 되는 것이다
그런 질문에 대한 이야기를 해보도록 하자
XL_DB_633_015.
XL-DB_014 | 목록상자에서 정보를 전달하여 다른 목록상자에 관련정보채우기
목록에서 전체보기를 선택하면 전체 직원의 매출분포를
챠트로 보여주고
목록에서 직원이름을 선택하면 해당 직원의 상품분류별 매출현황을
챠트 대신에 목록상자에 올려주는 것을 이번주에 해 보도록 한다
XL_DB_632_014.
XL-DB_013 | Image콘트롤에 매출챠트 올리기
이번 주에는 지난주에 갖여왔던 정보를
차트로 표현하여 UserForm에서 보여주는 작업을 해보자
데이타와 UserForm은 아주 좋은 궁합이니..
SQL, DAO와 더불어 UserForm을 잘 활용하는 연습을 이 코너에서
계속 해보도록 하자
아래 그림과 같이 [전체선택]을 하였을때는
직원들의 총매출 합계를 챠트로 표현하고
직원이름을 선택하면 새로운 매출 상세목록을 갖여 오는 것을
그 동안의 학습의 복습겸..얼마나 잘 이해하고 있는지 테스트겸
이번주 숙제로 해 보시기 바란다
XL_DB_631_013.
XL-DB_013 | Image콘트롤에 매출챠트 올리기
이번 주에는 지난주에 갖여왔던 정보를
차트로 표현하여 UserForm에서 보여주는 작업을 해보자
데이타와 UserForm은 아주 좋은 궁합이니..
SQL, DAO와 더불어 UserForm을 잘 활용하는 연습을 이 코너에서
계속 해보도록 하자
아래 그림과 같이 [전체선택]을 하였을때는
직원들의 총매출 합계를 챠트로 표현하고
직원이름을 선택하면 새로운 매출 상세목록을 갖여 오는 것을
그 동안의 학습의 복습겸..얼마나 잘 이해하고 있는지 테스트겸
이번주 숙제로 해 보시기 바란다
XL_DB_631_013.
XL-DB_012 | 직원별 매출실적 시트로 옮기기
이곳에서는 UserForm을 마음대로 활용한다
데이타베이스의 정보를 주고 받으려면 시트도 좋지만
UserForm을 최대한 잘 활용하는 것이 좋다
UserForm에 직원들의 매출현황을 올리고
선택된 직원 혹은 전체직원을 선택하게 하고
[시트로 보내기] 버튼을 크릭하면 시트로 옮기도록 하는
콘트롤타워로서 UserForm을 맘껏 활용하자!!!
Userform이 로딩될때 Northwind.mdb에서 직원리스트를
얻어다가 목록상자에 올리고
직원을 선택할때마다 해당 직원의 매출실적을 데이타베이스에서
찾아다가 올리고..
이 내용을 버튼을 크릭하면 시트로 옮기고..
DAO도 익히고..
Userform도 능숙하게 하고
Userform같은 것은 나중에 VSTO같은 곳에 가서
.Net FrameWork등을 작업하고자 할때..
빵빵한 기초다지기가 된다는 것을 염두에 두시고,.
XL-DB_630_012.
XL-DB_011 |
직원별로 매출액을 뽑아 보았었고
이것을 엑셀에서 합계를 내고 백분율을 계산하고 법썩을 떨었었다
그러나
SQL의 파워는 그런 합계를 내고 백분율을 내고 하는 짓을
한줄의 문자열로 끝나게 되는 것이다
그것도 서식까지 한줄에 끝날수 있는 것이다
백분율을 퍼센트로 문자열서식하고 , 숫자를 천단위로 끊는 것도
SQL문 내에서 처리하면 된다
그럼 엑셀은 뭐만 하면 되나..
예쁘게 받아다가 진열만 해도 되는 셈이다
그러니 별로 엑셀입장에서 할일이 없다
그래서 할일을 좀하게 UserForm을 정보의 진열창으로 활용해보자
SQL 에 너무 모든 것을 시키기가 미안하니까..
아래의 그림과 같이 UserForm하나 띄우고
버튼을 크릭할때마다 해당 직원의 내용이나 혹은
전체보기를 하면 전체의 내용을 갖여오거나 할수 있는 것이다
이런 저런 기능의 종합셋트다!!!
SQL로 쿼리할때 직원별 실적을 뽑았는데
총합계를 어떻게 적용할 것인가???는
SQL초보님들에게는 도전꺼리다!!
간단하게 생각하시면 된다
SQL로 갖여온 쿼리는 또 다른 쿼리를 불러서 사용할수 있는 것이고
결국은 SELECT문 내부에 또다른 SELECT문이 들어 갈수 있다는 개념을
알면 된다
또한 SQL 문내에서는 SQL에서 지원하는 다양한 함수들이 있다
엑셀과 VBA만 함수있냐??!##
나도 있다!!!
라고 준비해 놓고 있는 것이다..
함수의 이름은 거의 같고 사용방법도 같다
그러니 VBA에서 연마한 함수들은 다른 언어에서도 모두 사용되는 것이니
(이름만 종종 다르고 약간의 문법적으로 다른 점이 가끔있을뿐..)
VBA와 엑셀은 모든 프로그래밍을 배우는데 기본이 될 수 있고
또한 엑셀과 VBA는 배우면서 동시에 써먹는다!!!
XL-DB_629_011.
XL-DB_010 |
직원별로 매출합계를 보고 싶다면
직원별로 그룹핑을 지어야 한다
그리고 계산휠드를 만들어야 한다
어떤 테이블의 단가 휠드정보와 판매수량 휠드정보를 갖여다가
곱하여 가상의 휠드를 하나 만들고 이것을 계산휠드라고 한다
데이타베이스의 테이블은 계산된 결과를 담고 있는 테이블이 아니다
테이블에는 순수한 정보만 갖고 있어야 잘 만든 데이타베이스테이블이다
계산은 쿼리테이블에서 한다
쿼리테이블에서 각각의 테이블의 원시정보를 참조하여
계산을 수행하여 결과물을 만들어 주는 것이다
그러니 억세스에 만들어 놓은 쿼리테이블들은 물리적으로
하드 디스크를 차지 하고 있는 테이블정보들이 아니다
그냥 단순히 어떤 테이블과 어떤 테이블의 내용을 갖여다가
이렇게 저렇게 계산하고, 정렬하고, 휠터하는 등등의
작은 프로그래밍 구문..SQL구문을 담고 있는 텍스트정보인 것이다
XL-DB_628_010.
XL-DB_009 |
직원별로 매출현황을 보고싶다
직원별로 매출현항을 보고 싶다면 테이블을 3개를 연결해야 한다
왜 그렇게 복잡하게 해 놓았을까???
만약 위의 것을 하나의 테이블로 구성을 했다고 치자..
어떤 테이블이 될까???
홍길동이가 2010/1/1 에 와서
과자를 하나에 100원하는 것을 10개 사고
라면을 하나에 200원하는 것을 10개 사고
사과를 하나에 120원하는 것을 5개 사고
소주를 한병에 20원하는 것을 2병 사고
그랬다면 하나의 테이블에 기록을 한다면 날짜를 반복 입력을 하고
홍길동이라는 이름을 반복입력을 하여야 한다
그러니 3개의 테이블로 나누어서
최소한의 관련 정보만(Key값) 입력하여 정보를 최대한 반복을 배제하는
테이블을 만든 것이 관계형테이블인 것이다
그리고 나중에 쿼리로 중복이 되는 정보가 있던 없던 상관없이
원하는 정보를 뽑아볼수 있게 되는 것이다
이번주에는 직원의 매출현황..즉 3개의 테이블을 연결하여 원하는
정보를 뽑아보는 쿼리를 만들어 보도록 한다
XL-DB_627_009.
XL-DB_008 |
SQL문을 살짝 하나만 고치면
각직원별로 불러오던 것을 직원전체를 불러서 볼수 있다
아래의 그림과 같이
2007버전을 ADO를 사용하지 않고 다른 방법이 없냐고 하신다
물론 있다!!!
ADO를 사용하면 ADO라이브러리 하나만 설치하고 경우에 따라서는
2003버전 억세스에 접근하기도 하고
2007버전 억세스에 접근하기도 하지만..
본래는 2003버전에 DAO가 있듯이
2007버전에도 데이타베이스 라이브러리가 당연히 있다
Microsoft Office 12.0 Access DataBase Engine Object Library
라는 라이브러리를 참조대화상자에서 참조시키면 된다
이때는 DAO라이브러리를 제거해 주어야 한다
충돌이 생기니까!!
구문은 DAO에서 사용하던 것을 그대로 사용해도된다
그대로 사용해도 되는 것은 중요한 오브젝트이름들이 같다는 이야기다
그래서 그냥 사용해도 된다
라이브러리만 2007용 엔진으로 참조시키고
그런데 또 다른 문제가 하나 있다
2007버전 Northwind.accdb는 테이블이름이라던가 휠드이름이 모두 한글로
바꿔 놓았다
그러니 이 시트에서의 구문은 2003버전(이것은 영문휠드,테이블이름을 그대로 사용한다)를
작업하던 구문들이 예를 들어서
2003버전에서는 아래와 같지만
Set oRst = oDB.OpenRecordset("SELECT lastname,employeeID FROM employees")
2007버전 northwind.accdb는 아래와 같이 각해당 휠드를 찾아서
아래와 같이 당연히 수정해 주어야 한다
Set oRst = oDB.OpenRecordst("SELECT 이름,ID FROM 직원")
어떤 면에서는 학습효과가 더 클지도 모르겠다..
테이블이름과 휠드이름이 틀린다고 에러가 나면..
억세스 테이블 열어보고 테이블과 휠드명 확인하고 코드 수정하고
그러다 보면 실력이 확장 될 것이니까!!!!
아무튼 고수가 되는 것은 코딩하는 실력보다는 이렇게 헷갈리는 것들을
잘 정리정돈하여 필요할 때, 상황에 따라 융통성있게 이놈, 저놈 갈아가면서
일을 시키는 테크닉의 확장인것이다
XL-DB_626_008.
XL-DB_007 |
이 시트때문에..혹시라도 uno-weekly는 무지 어려운 것을 하는 모양이다!#@
라고 하면 오산이다
이페이지는 데이타베이스를 학습함과 더불어 정보의 분류방법과
정보의 활용요령등..일잘하는 질 좋은 업무의 기본컨셉을 익힘과 동시에
실제적인 파워를 습득 하는 곳이다
종종 엑셀은 컴퓨터마다 깔아 놓고도..
피벗테이블의 필드를 이해하거나 피벗테이블이 무엇을 하는지 아는 사람들이
별로 없다고 한다
그러니 그룹핑을 한다는 것의 의미도 잘 이해 못하는 것이고
그러면서 정보화작업을 한다고 하는 것은 웃기는 일들이다
DAO를 시작한지 몇주 되지 않았지만
매주 똑같이 만들어보고 자판을 열심히 두들겨 본 분들은
일을 바라보는 패라다임이 슬슬 바뀌어 갈 것이다
우리 부서에서 발생하는 정보를 이렇게 보관하고 이렇게 찾아보면
매주, 매월 보고서는 별도로 시간을 낼필요가 없겠네@#
라는 생각들을 할 수 있을 것이니까...
아무튼 겁먹고 실실 피하는 분들은 그냥 그렇게 살라구 하고
열심히 하시는 분들은 계속 열심히 가자!!
이번주에는 GROUP BY와 HAVING이라는 키워드를 사용하게 된다
XL-DB_625_007.
XL-DB_006 |
이 시트의 설명을 답답하지 않게 이해하는 방법은
NorthWind.mdb를 구성하는 테이블의 이름을 외우시기 바란다
여러분이 갖고 있는 회사라고 생각하고 관심을 갖고 테이블의 이름을
기억하시는 것이 이 시트를 앞으로 진행하면서 편하게 볼수 있게 된다
이 것을 매주 이야기할때마다..어떤 테이블에 뭐가 있었지 하면 준비가 안된상태다
데이타베이스를 위한 것 뿐만 아니라 여러분의 조직을 이해하는데도 도움이 된다
테이블간의 논리적관계를 이해하고 머리속에 넣는다면
아마도 여러분이 위치한 조직의 부서간의 관계상의 어떤 그림이
연상이 될 것이고 이것은 여러분이 사물을 보는 눈이 폭이 엄청 넓어지는 셈이 된다
과장이 되려면 과장의 눈으로 볼줄 알아야 하고
부장이 되려면 부장의 눈으로 볼줄 알아야 하고
사장이 되려면 전체의 구성을 한눈에 볼줄알아야 한다
그러니 Northwind.mdb화일의 테이블이름과 관계를 이해하고 외우고
그릴줄 안다면 이미 여러분은 Northwind라는 가상의 회사의 사장인 셈이다
그런 눈을 갖아야 문제의식이 생기고..어떤 정보가 필요한지 머리속에서
폭발적으로 일어나게 되는 것이다
그러니..이 시트는 단순한 데이타베이스만을 생각하는 것이 아니고
일의 관계를 입체적으로 이해하는데 도움이 되기 위한 곳이다
이번주에는 아래와 같은 주제다
길동이라는 직원이 관계한 운송회사의 리스트를 뽑아라!!!
이제 하나의 테이블로서는 문제가 안풀린다
관련된 테이블을 연결하여야 하고 어떤 조건을 주고 어떤것을 뽑고
모두 SQL이라는 말로 구성하여야 한다
그런데 좀복잡할 것 같다!!
어떤 테이블이필요한가??
직원테이블..Employees
거래테이블...Orders
운송회사테이블...Shippers
3개의 테이블에서 정보를 얻어와야 한다
점점 재미있어 진다!!
XL-DB_624_006.
XL-DB_005 |
억세스 2007을 DAO로 접근하려니 알수없는 데이타베이스라고 하는데..
이것이 뭔가요???
DAO는 Access 2003이하의 Native Object이다
영어를 미국사람이 하면 Native Speaker라고 하듯이
DAO는 Access 사람이다..즉 억세스자체의 오브젝트인 것이다
문제는 Access2007 부터는 2003이하와는 종자가 다른 데이타베이스이다
그러니 DAO로 접근하려면 수선을 떨어야 하고 2003과 2007이 같이 깔려있으면
이것도 헷갈려 하고 그러니..이럴때는 ADO라고 하는
ActiveX DataBase Object를 사용하는 것이 바른 길이다
이 코너의 궁극적인 목표는 DAO가 아니고 ADO로 간다..
ADO는 억세스의 족보에 있는 녀석이 아니고 Microsoft사의 이런,저런
데이타베이스를 모두 커바하는 독자적인 라이브러리이다
그러니 이것을 사용하면 억세스이던 SQL이던 데이타베이스는 모두 접근한다
물론 중간에 Provider라는 통역하는 녀석이 있지만..
아무튼 질문 하신 분은 아래와 같이 해보시기 바란다
Sub withADO()
Dim oRst As New ADODB.Recordset
Dim oCon As New ADODB.Connection
oCon.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.Path & "\northwind.accdb;Jet OLEDB:Database"
oCon.Open
Set oRst.ActiveConnection = oCon
oRst.Source = "SELECT * FROM 직원"
oRst.Open
MsgBox oRst(5).Value
End Sub
Access2007은 테이블이름을 모두 한글로 바꾸어 놓아서
2003버전이하와 다른 테이블 족보들이니 알아서 입력하셔야 할 것이고
아무튼 외부데이타연습을 위하여서는
Access 2003 이하의 NorthWind.mdb를 사용하고..
DAO는 몇주만 더 하고 , ADO로 바꾸어서 이야기를 전개할 것이다
헷갈리지 마시기를...
이제 테이블 하나에서 갖여 오는 것으로는 재미가 없다
이제 관계형으로 슬쩍 발을 들여 보자
그래야 데이타베이스가 재미있어진다
예를 들어서 직원별 영업활동의 실적을 보고 싶다고 치자
Employees 테이블 하나만으로는 아무것도 할 수 없다
무엇을 팔았는지..어떤 영업을 하였는지 기록이 이곳에는 하나도 없다
그냥 직원의 개인적 기록만 있을 뿐이다
이제 아하!@#! 그래서 관계형 데이타베이스라고 하는 구나??!!
직원이 다른 영업활동을 한 정보를 기록한 테이블과 관련이 있겠구나??!!
그렇지!!!
그런 관계의 고리를 형성하는 것이 KEY값 인것이다
Empoyees테이블에는 유일한 값인 EmployeeID가 Primary Key값으로 존재하고
이 값을 영업활동을 한 테이블(Orders테이블)속에 살짝 걸치고 있다는 것이다
Orders테이블입장에서는 이 Key값이 외부에서 온 외부키인 셈이다
이것을 Foreign Key 라고 Primary Key와 대별된다
자신의 ID가 아니고 외부테이블에서 관계를 맺자고 들어 온 녀석이다
국가간의 대사관이 하나 파견되어 있는 셈이다
영업활동을 기록한 테이블인 Orders테이블의 족보가 아래와 같다
그러니 홍길동이라는 녀석의 영업활동을 보고 싶다면
홍길동의 직원테이블(Employees)상에서 부여받은 ID값...EmpoyeeID를
활용하면 Orders테이블에서 자신의 Key값이 있는 기록은 몽땅 뽑아서볼수 있게
되는 것이다
아래의 그림과 같이 직원들 버튼을 만들고 버튼을 크릭하면 해당직원이
관련이 있는 영업기록은 몽땅 뽑아서 시트에 뿌려 보도록 하자
점점..재미있어 지지 않는가???
도전하는 재미..성취하는 재미를 만끽하셨으면 좋겠다!!!
XL-DB_623_005.
XL-DB_004 |
외부데이타를 갖여다가 상황과 분위기에 맞추어
적절한 보고서, 분석등을 후딱 만들어서 제출하는 사람은
유능한 사람..
그런데 이때 문제가 있다
상황과 분위기를 보아서 만든다는 것..
이것이 엑셀프로그래머와 다른 언어의 프로그래머들과
차별화 할 일이다
상황과 분위기를 잘 파악하는 재주!!!
감성을 읽는 재주!!
사람과 사람간의 에너지를 읽는 재능..!!!
이것을 위하여 Front페이지가 있는 것이다!!
이런 재능이 없으면
엑셀 잘하고 왕따 된다!!
재수없으면 회사도 쫓겨날수 있다
회사의 중요한 정보를 갖고 회를 쳐먹는 위험한 놈이라고!!
XL-DB_622_004.
XL-DB_003 |
[엑셀과 외부데이타]는 엑셀프로그래밍의 고급코스다
외부데이타를 갖여다가 적절히 엑셀의 기능과 조화를 하는 것은
정보 가공과 표현의 최상의 표현이라고 해도 과언이 아니다
죽기 살기 기초만 다루는 초급코스 [VBA엑셀프로그래밍|기초]시트와
적절한 내공이 쌓여가는 [VBA엑셀프로그래밍|응용]시트와 더불어
uno-weekly의 컨셉이 자리를 잡아가고 있는 것 같다
어느 의료임상데이타를 데이타베이스로 만들어 보면서 이런 질문을 하신다
초보때 ..왜 이러지??!@#라는 의문이 팍 생기는 문제다..
PatientID 값을 입력을 하려고 하면 죽어도 입력이 안되는데 왜 그런가요???
당연히 이 값은 입력이 안된다
[일련번호]로 설정한 정보는 테이블의 레코드를 유일한 값을 유지 하는
ID 정보에 설정하는 정보의 타입이고..
이것은 DB시스템이 알아서 주욱 일련 번호를 부여하여 나간다
또한 어떤 레코드를 삭제를 하게 되면 이 레코드에 부여 되었었던 [일련번호] ID값은
다시는 나타나지 않는다..영원히 사라진다
이것은 관계형 데이타베이스의 핵심이다
지난 주의 테이블설계부분을 차근 차근 보시면 될텐데..!@#
이번주에는 Employees테이블에서 직원이름을 불러서
엑셀의 도형오브젝트로 버튼을 만들고
버튼의 OnAction속성에 또 DB에서 해당 버튼의 직원이름을 찾아오는
SQL의 조건절 WHERE 절을 익히는 기회를 갖도록 하자
SQL문이야 말로 순수 간단명료 영작문이라고 보아도 된다
로봇끼리 대화나눌수 있는 간단명료한 대화법이라고 할수 있는
재미있는 문장이다..
일상회화 그렇게 해도 괞찮을 것이다...
그런데 말투가 전부 명령쪼라서 잘못하면 디지게 쥐어 터질수도 있다..하하!!
즐겨 보시기 바란다..
XL-DB_621_003.
XL-DB_002 |
DAO라이브러리
DataBase오브젝트..
RecordSet오브젝트..
모두 외부 데이타베이스에서 데이타를 갖여 오는 일을 해주는 유용한 오브젝트들이다
그러나 위의 것은 그냥 몇줄만 알면 끝난다
문제는
Recordset을 만들기 위하여 DataBase오브젝트의 OpenRecordset()메소드에
SQL문을 어떻게 전달하느냐를 차근 차근 익혀가는 것이 VBA에서 SQL문으로 확장이
이루어져 가는 과정이다
이번 주에는 SQL문을 몇개 작성해 보고
그중에 데이타를 갖여다가 엑셀의 기능과 적절히 짬뽕하여
아래와 같이 도형에 데이타베이스 Northwind.mdb의 Employees테이블에서
직원의 이름휠드에서 성만 쏙뽑아내어 갖여다가 도형의 TextFrame오브젝트의
Characters.Text값으로 전달하고 도형을 서식해 보도록 하자
Set oRST = oDB.OpenRecordset("SELECT Left(lastname,1) AS 성 FROM Employees")
XL-DB_620_002.
XL-DB_001 |
앞으로 이페이지는 엑셀프로그래밍의 고급과정으로 생각하시면 될 것이다
기본을 능숙하게 하는 것도 필요하고 과연 엑셀프로그래밍으로 어느정도의 것까지
할수 있는지 동기부여도 하여야 할 것이고
그래서 메뉴얼로 쿼리테이블을 갖여오는 과정같은 것은 도움말에서
충분히 커버가 될 것이니..여기에서는 VBA프로그래밍으로 직행하도록 한다
설명하는 놈도 지루하고 듣고 보는 분들도 지루하다!!
다행이 지난 몇주동안 데이타베이스테이블의 설계와 필드의 설계부분은
많은 개념파악에 도움이 되도록 하였으나 ..아직 뭐가 뭔지 모를수도 있다
그냥 직행하면서 또 설명을 해가면서 하기로 하자
데이타베이스를 다루는데는 외부라이브러리 DAO나 ADO가 필수이다
[VBA+데이타베이스를 다루는 외부라이브러리]가 되어야 한다
그러니 엑셀프로그래밍의 고급과정이라고 보면 된다
궁극적으로는 ADO로 가지만 우선 DAO로 정보를 다루어 보도록 하자!!
DAO로 Northwind.mdb 데이타베이스를 접근하여
테이블 employees를 가져다가
Range오브젝트의 CopyFromRecordset메소드를 실행하니..
어렵쇼..에러가 난다!!
NorthWind.mdb화일을 같은 폴더에 넣고..아래를 실행!!
Sub getDataFromAcc_1()
Dim oRST As Recordset
Dim oDB As Database, rX As Range
Set oDB = OpenDatabase(ThisWorkbook.Path & "\northwind.mdb")
Set oRST = oDB.OpenRecordset("employees")
With Worksheets.Add.Range("A1")
___.CopyFromRecordset oRST
______ With .CurrentRegion
__________.Font.Name = "맑은 고딕"
__________.Font.Size = 9
__________.Columns.AutoFit
______End With
End With
End Sub
다른 테이블은 잘 갖여 오는데 왜 유독 employees테이블은 에러가 날까???
중요한 개념을 알아야 한다
왜 그런 에러가 나는지..
다른 정보를 다룰때 가장 중요한 개념이다
원인을 알면 얼마던지 빠져나가는 길들이 있다..
그런데 원인을 모른다..!!!
그럼 문제는 안풀린다!!
XL-DB_619_001.