자재관리 | 청구서관리에서 DB활용
VBA를 어느 정도 한후..딜레마에 빠지는 부분이 요 정도까지 만든후이다
화일을 손으로 그냥 삭제를 해버리면
로그시트의 내용은 허당이 되고
로그시트의 내용을 얼떨결에 지워버리면 화일은 또 고아가 되고
또한 청구서화일을 별도로 열어서 이것 저것 수정을 하였다 치면
또 이것도 앞뒤가 안맞게 되고..
이것을 이렇게 저렇게 어떻게 해보려고 하면 할 수록 일이 꼬이고
코드가 꼬이고..악몽이 되어 버리게 된다
또 이렇게 불안 불안한 상태의 것을 다른 동료나 부하직원에게
사용하게 하는 것도 불안, 불안 하게 된다
이런 일은 VBA에서 만 겪는 것이 아니고 모든 정보관리에서 꼬이게 되는 부분이다
그래서 탄생하게 되는 것이 DB 인 것이다
DB만 잘 만들어져있다면 쓸데없이 [청구서]화일은 폴더에
쌓아둘 필요도 없는 것이다
필요할때 청구서 만들어서 보낼곳에 버리고..별도로 보관하고
있을 필요는 없게 되는 셈이다
STEP_8 ---------------------------------
이것은 자재,청구서관리뿐만 아니고 모든 업무에서 적용되는 것이니
모두 악착같이 달려 드시기를...
우선 DB를 억세스같은 데이타베이스 없이도 엑셀시트로 충분히
DB를 만들어서 활용할수 있다
앞에서 만들었던 것 많이 뜯어 고칠 것이다
앞에서 만든 상태에서 만족하여도 별 문제는 없고
성장지향주의적인 마인드를 갖은 분들은 달려 들고..
DB는 업무의 블랙박스다..
다른 것은 죄다 날아가도 이것만 하나 꽉쥐고 있으면
이런 보고서, 저런 보고서, 이런 분석, 저런 분석
이런 청구서, 저런 청구서를 만들어 버리면 되는 것이다
대개가 이것을 만들기 싫어서, DB에 대한 개념이 없어서
화일을 중구난방으로 정신 사납게 관리하게 되는 것이고
진정한 정보관리가 아닌 것이다
블랙박스에는 허접한 정보는 저장하지 않는다
예를 들어서 어떤 품목에 단가가 있고 판매수량이 있을때
판매액은 저장하지 않는 것..판매액은 단가*판매수량으로 계산해
낼 수 있는 것이니..이런 것을 저장하는 것은 아니다
정보의 엑기스..단가와 판매수량..
대개 이런 정보의 종류에서 헷갈림으로 해서 DB설계를 못하게 된다
또한..지금까지 한 정보의 내용에서 테이블이 몇개가 만들어져야
하는지 판단이 되지 않는다..그것도 DB를 이해못하는 이유가 된다
[청구서]관리 프로그램의 DB는 엄밀히 따지면
3개(혹은 확장성에 따라 그 이상) 테이블이 필요하지만
여기에서는 두개의 테이블로 구분하자
Log시트의 내용이 하나의 태이블이 되고
각[청구서]화일의 주문내역이 또 하나의 테이블이 되면 된다
아무튼 테이블의 정보는 중복되는 것이 있으면 안된다
왜 3개의 테이블이 필요하다고 하였냐 하면
매번 똑같이 저장되는 [현장명],[팀명],[담당자]등은 하나의
현장에서는 똑같은 내용이 될 것이다
이것을 여러현장에서 사용하는 소루션이라면 이것도 하나의 별도의
테이블이 되어야 하지만 너무 확장되면 헷갈려지니까..
중복되는 정보를 그냥 감수하고 2개의 테이블로 정하고 가자
[청구서]하나의 행의 정보에 [청구내역]에 여러행의 정보가 연결이
되는 것..
[청구서]테이블의 부모테이블이고 [청구내역]테이블이 자식테이블인 셈이다
중요한 것은 어떤 [청구서]행과 [청구내역]의 어떤 행들이 관계가
유지되고 있는지를 나타내는 연결고리가 하나 있어야 할 것이다
이것을 Key값이라고 하고, 이 정보를 [청구서]테이블에서
[청구ID]라는 값으로 만들어지고 이 것에 해당하는 자식들의
관계를 나타나게 하기 위하여 [청구내역]에 [청구ID]라는 같은 값이
기록이 되면 되는 것이다
이 두개의 테이블이 들어있는 엑셀화일은 열지 않고 처리하게 된다
그냥 닫힌 상태에서 읽고 쓰고 할 수 있는 것이고
그것이 DB인 셈이다
이렇게 엑셀을 데이타베이스형식으로 접근하려면
특별한 라이브러리가 필요하게 된다
즉..모듈시트에서 도구메뉴의 참조에서 외부라이브러리를 하나 끌어들이면 된다
다양한 버전이 있을 것이다
Microsoft ActiveX Data Objects----그냥 ADO라고 약칭하여 부른다
아무 버전이나 상관없으니 참조시키면 된다
ADO의 가장 중요한 것은 DB와의 연결을 담당하는 개체가 있다
우리가 전화를 할때 가장 중요한 것은 연결(Connection)이라는 것이
이루어져야 뭔 내용이 오고,가고 할 수 있는 것과 마찬가지로
Connection개체라고 하는 것이 있다
참으로 개념이 쉬운 것인데..
전화하는 것을 생각하면 된다
DB는 여러분의 컼뮤터에 있을 수도 있고 저어기..미국의 써버에 있을 수도 있다
이것을 연결하여 정보를 주고 받고 하는 것이다
그러니 Connection이 이루어져야, 즉 Connection 개체가 만들어지고
Connection 개체가 열려야 한다
그래서 ADO의 아래와 같은 개체를 생성한다
Dim oCon As New ADODB.Connection
이것은 전화기를 손에 잡은 상태이지 아직 연결이 된 상태는 아니다
연결하는 것은 말 그대로 Open 이라는 메소드가 있는 것이다
oCon.Open
그런데 이렇게 해서는 안되겠지..
전화번호가 있어야 할 것 아닌가????
전화번호를 주어야지..!!!
DB에서 전화번호에 해당 하는 것이 무엇일까..
써버에서 갖여올때는 써버의 IP어드레스가 필요하겠으나
이것은 엑셀을 DB로 꼼수를 써서 사용하려고 하는 것이니..
엑셀화일이름과 경로가 전화번호인 셈이다
그런데 무엇을 연결할때 전압의 차이가 나면 아답타라는 것을 끼우듯이
엑셀은 DB가 아니다!!!
DB같이 사용하려고 하는 것이다
그래서 이것을 DB형식으로 해석하는 중간역할하는 아답타가 하나 있어야 한다
이것을 Provider라고 한다
그래서 아래와 같이 연결을 하게 된다
oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;"Data Source=" & ThisWorkbook.Path & _
"\supplyDB.xls;Extended Properties=Excel 8.0;"
Provider=Microsoft.Jet.OLEDB.4.0
중간 아답타역할을 하는 것을 Microsoft.Jet.OLEDB.4.0 이라는 작은 프로그램을
사용하라고 지시를 하는 것이다
그리고 경로및화일명은
Data Source=화일경로와 화일명
위의 내용은 그냥 수첩에 적어 두면 된다..외우려고 하는 무식한 짓을
할 필요도 없고..
그냥 ADO로 엑셀을 열때 연결정보가 어떻게 되지??라는 컨셉을
알고 있는 것이 중요하다..전화번호와 연결방식!!!
이제 연결된 것이다
연결상에 문제가 발생하면 에러가 날 것이고..
무언가를 배운다는 것은 지식을 외우는 것이 아니다
지식을 어떻게 활용하는가, 지식이 어디에 있는가??를 아는 것이 중요하다
VBA를 못해도 [우노]같은 사람이 잘 도와준다는 것을 알면
활용하면 된다..죽기 살기로 부려 먹을줄 알면 된다
그냥 아는 체하고 뻐텨 보았자 자기만 손해다..
자원의 활용!!!^^
이제 위와 같이 연결은 되었다..
이제 대화를 해야지..대화란 콘텐츠가 왔다 갔다하는 것이다
정보를 실어서 보내고, 받고 하는 자동차가 하나
connection이라는 연결도로위를 왔다 갔다하여야 한다
이것을 RecordSet 개체라고 한다
이 Recordset개체에 정보를 태워서 나른다
이때 어떤 정보를 갖여와라, 어떤 정보를 갖다가 저장해라!!
라는 것은 Recordset에 장치가 달려있다
oRST.Open "[청구서$]", oCon, adOpenKeyset, adLockptimistic, adCmdTable
이것도 Open이라는 매소드를 사용한다
[청구서]테이블을 열어서 갖어 오라고 하는 것..
이때 엑셀의 경우 테이블명에 반드시 딸라 표시를 하라고 약속이
되어 있다..[테이블명$] 이라고 하면 테이블을 인식하게 된다
그리고 두번째 매개변수는 oCon 즉 어떤 연결도로를 활용하느냐..
즉 위에서 개체를 만들고 열어 놓은 Connection개체를 준다
나머지는 선택적매개변수 상수값..이것은 아직 별로 관심을 갖지 않아도 좋다
(설명하려면 너무 깊이 들어간다)
그냥 데이타베이스를 갖여 오는데도 효울적으로 용도에 따라서
어떤 형식으로 포장을 해서 갖여 오느냐의 주문내역이라고 보면 좋을 것이고
그냥 위와 같이 알고 있으시고..
저렇게 열면 하드상의 엑셀화일의 내용을 메모리로 줄줄이
불러 올린다...화일을 열 필요가 없다는 것이 매력적인 것이다
위와 같이 연결하고, 테이블을 갖여온후
아래와 같이 하여 저장하고 싶은 정보를 각 휠드에 값을 주면 된다
중요한 것은
Recordset의 AddNew 라는 메소드로 이제 부터 새정보 들어간다!!
받을 준비하라!!라는 메소드를 호출 하고..
줄줄이 다 정보를 옮긴후에는
Recordset의 Update 라는 메소드로 종료를 하면
메모리상의 Recordset에 저장된 것이 하드상의 화일로 주루룩
날아가게 되는 것이고..정보추가 작업은 상황끝이 되는 것이다
Set oshtList = Worksheets(modMain.SHT_LIST)
sID = Format(Now, "YYMMDDHHMMSS")
With oRST
.AddNew
.Fields("청구ID") = sID
.Fields("업체명") = oshtList.Range(modMain.COMPANY_NAME_).Cells(1)
.Fields("발주일") = oshtList.Range(modMain.REQUEST_DATE_).Cells(1)
.Fields("입고일") = oshtList.Range(modMain.SUPPLY_DATE_).Cells(1)
.Fields("현장명") = oshtList.Range(modMain.SITE_NAME_).Cells(1)
.Fields("공종명") = oshtList.Range(modMain.WORK_NAME_).Cells(1)
.Fields("팀명") = oshtList.Range(modMain.TEAM_NAME_).Cells(1)
.Fields("청구자") = oshtList.Range(modMain.REQUESTER_).Cells(1)
.Fields("핸드폰") = oshtList.Range(modMain.HP_).Cells(1)
.Fields("메모") = oshtList.Range(modMain.STATUS_).Cells(1)
.Update
End With
이번 스텝에서는 [청구서] 부분만 DB에 저장하였다
혹시 에러가 나면 에러의 이유는 각자의 컴퓨터에 따라서
ADO의 버전이 다를수도 있으니 참조에서 아무 버전이나
여러분의 컴에서 사용가능한 것을 재참조하시고 하시면 된다
아래 화일에 SupplyDB.xls는 데이타베이스용으로 테이블두개를
만들어 놓은 것이나 같은 경로에 놓고 사용하시기를..
***[LOG-IN]***
STEP_9 ---------------------------------
[청구ID]라는 번호는 정보의 주민등록번호와 같다
유일한 값을 주어야 한다
일련번호를 주어도 좋겠지만,조금 의미있는 정보를 만들어서
유일한 값으로 만들어주면 좋을 것이다
그래서 위에 년도+월+일+시+분+초로 구성하여 만들면
죽었다깨어도 같은 값은 없을 것이다
이런 ID속에서 많은 정보를 만들어 낼수도 있다면 더욱좋은 것이다
위의 ID값에서 정보발생의 시간은 자연스럽게 읽어 낼수 있을 것이고
[청구서]화일의 화일명도 만들어 낼수도 있는 그런 의미있는 정보가
좋을 것이다
기능을 추가하다 보면 해당기능을 별도의 프로시져를 만들어서
기존 기능의 어느 시점에 삽입할 것인가..타이임을 잘 생각하는 것이
좋을 것이다
임시 목록시트의 임무를 모두 끝낸 시점에
DB에 저장하는 일을 한후
임시시트를 삭제하는 순서로 바꾸는 것이 좋을 것이다
한번 [청구서]화일을 만들때 마다
[청구서]화일 하나가 만들어지고
DB의 [청구서] 테이블에 하나의 마스터가 기록되고
DB의 [청구내역]테이블에 여러개의 청구내역이 기록된다
아래의 그림과 같이
물론 색상은 설명하려고 칠한 것이고
DB는 순수한 정보가 저장되는 것..
[청구서]화일을 하나 저장후
supplyDB화일을 열어 보시고 입력내용을
확인해 보세요
이것이 있으므로 [청구서]파일을 내역을
보기 위하여 열어 볼 필요는 없어지는 셈입니다
다음 스텝에서는
저장된 DB를 어떻게 실무에서 활용하는지를
전개해 보도록 합니다
***[LOG-IN]***