[VBA] DATABASE 서버와 엑셀과의 데이터를 주고 받을 때 주의 할 점

간만에 포스팅 해 봅니다.

그간 많은 엑셀 자동화 프로그램들을 진행을 했고,

부족한 점이 많지만, 제 자신이 한 층 더 성장했음을 느낍니다.

지금 개발 중인 것들애 대한 핵심들인데, 이 걸 몰라서 에러 잡는데,

얼마나 많은 불멸의 밤을 보냈는지 모릅니다. 각설하고 들어 갑니다.

1. 엑셀에서 풀(POOL) 기능이 있을 법도 한데, 어디서도 관련 자료를 구하지 못했습니다.

(1) 서버와 연결 --> 쿼리 --> 결과값 반환 --> (2) 서버 연결 종료

이렇게 한 번에 한버의 쿼리만 가능하다는 것.

서버와의 연결 후 여러개의 쿼리를 실행 하면 에러 납니다

(1) 서버와 연결 --> 쿼리1 --> 결과값 반환 --> 쿼리2 --> 던지는 순간 에러 --> 결과값 반환 --> ... (2) 서버 연결 종료

(1) 서버와 연결 --> 쿼리1; 쿼리2; 쿼리3;... --> 던지는 순간 에러

이것이 가능하다는 글을 보았으나, 구체적인 방법을 제시하는 곳을 찾지는 못했습니다. 그냥

카더라 하는 글만 난문할 뿐. 오빠두님이 강의나 방법을 댓글로 달아 주시면 정말 감사...(꾸벅)

2. DB --> SHEET --> ListBox

기존의 코딩을 이렇게 했었는데, ListBox.RowSource 기능이 워낙 막강해서.

그런데, ScreenUpdate를 False로 설정을 해도, 데이터 량이 많으면, 오래 걸리는 데다,

에러가 많이 발생합니다. 특히 데이터 형이 수시로 변합니다. 날짜 포맷이라든가, 숫자 등등

특히, 언어가 다른 컴퓨터들끼리 하면, 죽음입니다. 이 때문에 각 열마다 포맷을 해 주는 함수를 따로 만들 정도 였습니다. 그래도 사용자 정의폼으로 불러 들이면, 참... 답 없을 정도로 포맷이 멋대로 변합니다. 저는 한글 엑셀로, 대부분의 사용자들은 영문 엑셀이라 생기는 아주 고질적인 문제입니다.

그래서 중간에 SHEET를 거치지 않고 DB--> ListBox로 뿌려 주면 그런 현상이 말끔이 사라 지고, 속도는 10배 정도 빨라 집니다. (데이터 량에 따라 조금 차이가 있습니다.)

3. ListBox에 직접 뿌려 줄때 주의 사항

Recordset 객체를 통해 데이터를 가져 오면, 2차원 종적배열입니다.

ListBox.List = rs.getRows 이렇게 해보면, 리스트 박스에 종적으로 뿌려 지는 것을 볼 수 있습니다. 이런 경우 WorksheetFunciton.Transpose(rs.getRows)를 쓰면 횡적 배열(Row)로 변환을 해 줍니다.

ListBox.List = WorksheetFunciton.Transpose(rs.getRows)

이렇게 하면, 깔끔하게 데이터가 리스트 박스로 들어 갑니다.

미국 서버에서 1000개의 데이터를 불러서 리스트 박스에 뿌려 주는대 0.3초 정도 걸립니다.

여기서 만나는 치명적 에러. 원본 데이터에 NULL값이 있으면, Transpose에서 부터 에러 작렬시작. ListBox에는 Type Mismatch 에러. 이 원인 알아내기 위해 얼마나 많은 검색과 시도를 했는지.ㅠㅠ

이거 해결하는데 만 하루 날림.

Transpose 함수를 새로 정의해서 만들고, Null 값이 있는 곳을 점(.)이나, 공백(스페이스)로 채워 주면 이 에러가 해결 됩니다.

나중에 리스트 박스 클릭해서, 각각의 콘트롤에 뿌려 줄 때, 점이나 공백을 삭제하는 꼼수를 쓰면 해결이 됩니다. 속도에는 어의 영향을 미치지 않습니다.

4. CRUD를 위한 모듈화

여러개의 폼에 데이터를 주고 받는 과정은 거의 동일합니다.

이를 모듈화해서 코드 중복을 막고, 코딩의 간편화를 하니, 이전의 프로젝트에서 1만행 이었다면, 지금은 2천행 정도로 줄었습니다. 코드 재사용성의 기능도 높였고, 아주 자주 쓰이는 것들은 클래스로 만들어 편하게 관리/사용하고 있습니다.

(1) 사용자 정의폼들 --> CRUD --> 모듈(서버접속) --> 쿼리 --> 결과(서버 종료) 가공/반영

이렇게 하니, 만드는 폼들의 디자인은 달라도 내부 처리 방법은 모두 똑같습니다. 시간을 적게 들이고도 빠른 속도로 개발이 가능하네요.

지금의 이 이야기는 3년전 제가 그렇게도 갈구하던 것들이었습니다.

누군가에는 너무도 어려운 이야기 일 수도 있고, 누군가에게는 가려운 부분을 해소하는 부분일 수도 있습니다. 남의 코드를 복/붙 하는 사람은 결코 이해 할 수 없는 이야기 일 수도 있습니다.