소량의 데이터라면 엑셀로 처리해도 큰 문제는 없었으나 요즘 대량데이터를 가지고 놀다보니 엑셀에 한계가 있음을 느꼈다. 그래서 눈을 돌려봤더니 데이터베이스가 있었다. 그 중 엑셀과 가장 가까운 DB ACCESS를 이용해 대량 데이터 처리를 시작해보고자 한다.
액세스? 엑세스? ACCESS !
- 엑셀과 액세스 준비
- 조회(SELECT)
- 삽입(INSERT INTO)
- 변경(UPDATE)
- 삭제(DELETE)
1. 엑셀과 액세스준비
우선 연습용 액세스 DB를 만들어준다. 필자는 C드라이브에 DB 폴더를 만들고 그 안에 DB 파일을 생성하겠다.
만들기 -> 테이블을 클릭하면 엑셀 시트같은 화면이 나타난다. 여기에 '추가하려면 클릭'을 눌러서 이름이나 나이 등 아무거나 데이터를 집어 넣도록 하자. 여기서 ID, 이름, 나이를 필드라고 부른다. 그 밑에 1, 도박사, 50 이것들은 레코드라 불리는 녀석들이다.
데이터를 넣은다음 ctrl + s를 눌러 저장하면 테이블 이름을 지을 수 있다. 필자는 TEST라는 테이블 이름을 선택했다. 확인까지 누르면 실험을 위한 액세스 준비는 끝. 이제 엑셀로 넘어가면 된다.
엑셀로 넘어와 VB편집기를 열고 참조 하나를 추가해준다.
엑셀 -> Alt + F11 -> 도구 -> 참조 -> Microsoft ActiveX Data Object 6.1 Library 체크 후 확인
Option Explicit
Sub DB_TEST()
Dim db As New ADODB.Recordset
Dim dbQury As String
Dim dbCon As String
dbCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB\DB.accdb;"
'데이터베이스 연결과 관련된 내용
dbQury = "SELECT * FROM test"
'실행할 명령
db.Open dbQury, dbCon, adOpenStatic, adLockReadOnly, adCmdText
'명령 실행
If db.EOF Then '결과가 없으면 다음과 같은 명령 실행
Range("A1") = "데이터 없음"
Else '결과 있으면 A1에 붙여넣기
Range("A1").CopyFromRecordset db
End If
db.Close
Set db = Nothing
End SubC:\DB\DB.ACCDB의 TEST 테이블의 모든 레코드를 조회하는 코드를 작성했다.
- dbQury 변수는 실행할 명령어를 저장
- dbCon 변수는 DB 형태 및 경로를 저장
- db 변수는 DB를 제어하기 위한 객체
위 코드를 실행하면 활성화된 시트에 쿼리 결과를 다음과 같이 출력해준다.
2. 조회(Select)
- 기본: SELECT * FROM 테이블이름
EX) SELECT * FROM TEST -> TEST 테이블의 모든 레코드를 출력
EX) SELECT 나이 FROM TEST -> TEST 테이블의 나이 필드 값만 출력
테이블에 있는 모든 레코드(=데이터)를 조회한다.
- WHERE(조건 추가): SELECT * FROM 테이블이름 WHERE 필드명='레코드'
EX) SELECT * FROM TEST WHERE 나이=50 -> TEST 테이블에 나이 필드가 50인 레코드들만 조회 (숫자일때 따옴표를 붙이지 않는다.)
Ex) SELECT * FROM TEST WHERE 이름='도박사' -> TEST 테이블에 이름이 도박사인 레코드들만 조회 (문자일때 따옴표를 붙인다.)
Ex) SELECT * FROM TEST WHERE 이름='도박사' AND 나이=35 -> TEST 테이블에 이름이 도박사이고 나이가 35인 레코드들만 조회
Ex) SELECT 이름 FROM TEST WHERE 이름='도박사' -> TEST 테이블에 이름 필드에서 레코드가 도박사인 녀석들 만 출력
UNION, GROUP BY, ORDER BY, 문자열 함수들을 이용해서 복잡한 쿼리를 만들 수도 있다. 하지만 난 아직 배워가는 중이므로 고급 스킬은 차차 배워보자.
3. 삽입(INSERT INTO)
-기본: INSERT INTO 테이블이름 ([필드명], [필드명]) VALUES( '레코드', '레코드')
EX) INSERT INTO TEST ([이름], [나이]) VALUES ( '이박사', 23)
4. 수정(UPDATE)
-기본: UPDATE 테이블이름 SET 필드명='레코드B', 필드명='레코드B' WHERE 필드명='레코드A'
EX) UPDATE TEST SET 이름='또박사', 나이=99 WHERE 이름='도박사'
이름 필드에서 도박사인 레코드를 이름은 또박사, 나이는 99살로 변경하는 쿼리
모든 도박사가 99살 또박사가되었다... 레코드 수정할땐 이런 경우를 방지하기 위해 정확한 조건을 넣어주는 것이 좋다 고 한다. 지금은 이름 조건만 있었지만 여기에 ID 값이나 나이 등 정확히 하나의 레코드만 나오도록 식별 할 수 있는게 필요하다. 여기선 고유의 값을 가지고 있는 ID 필드를 이용해 다시 한번 업데이트를 시도해보자.
EX) UPDATE TEST SET 이름='똘박사', 나이=1000 WHERE 이름='또박사' AND ID=1
5. 삭제(DELETE)
-기본: DELETE FROM 테이블이름 WHERE 필드명='레코드'
EX) DELETE FROM TEST WHERE 이름='또박사'
업데이트 쿼리의 교훈을 무시하고 이렇게한다면 TEST 테이블의 모든 또박사가 삭제된다.. 조건은 정확하게 만들자.
EX) DELETE FROM TEST WHERE 이름='또박사' AND 나이=99 AND ID=4
조건을 정확하게 설정하면 필요한 레코드만 뙇! 삭제가된다. 아주 나이스
액세스 DB에 데이터를 저장하고 엑셀로 불러오니까 엑셀이 한결 가벼워짐을 느꼈다. SQL 쿼리에 대해서도 많이 배워가고 있어서 아주 훌륭한것같다. 귿귿