[VB.NET] 차근차근 MS-Access DB 연결후 사용법

VB.NET을 업무용으로 사용할때는 일반적으로 MS-SQL을 사용하는 경향이 강하지만, 개인용이나 과제, 레포트용으로 사용할때는 사실 Access만 있어도 충분하다. Db에 연결하는 방법, SQL Query문을 사용하는 방법등 학습용으로 사용할때도 궃이 중대형 DB인 MS-SQL을 사용하는것보다는 Access를 사용하는게 환경설정이나 기타 들어가는 품이 훨씬 적으니 소규모 회사에서도 즐겨 사용하는 방법이다. 그런만큼 여기저기 각종 팁들도 많고, 사용법도 많이 있지만, 그래도 빼놓고 넘어가기에는 좀 아쉬우니 여기서도 간략하게나마 다뤄보자.

지난 강좌에서 MS-SQL에 연결하는 방법에 대해 설명했는데 사실 큰 차이점은 없다. 거의 비슷한 방법으로 Access DB의 사용이 가능하고, 그렇기에 설명이 훨씬 수월하게 이뤄질 수 있을것으로 보인다.

지난 강좌에서 만든 함수중 ConnectDB라는 함수는 그냥 사용하면 된다. 그냥 연결문자열인 conn_str을 수정하면 된다. MS-SQL의 연결문자열은 아래와 같다.

Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=서버아이피;Initial Catalog=DB명;User ID=사용자ID;Password=사용자비번;

여기보면 DB 제공자인 Provider가 SQLOLEDB.1로 설정돼 있는걸 확인할 수 있다. 우선 이걸 Microsoft.ACE.OLEDB.12.0 이렇게 변경해야 한다. 말 그대로 DB 제공자가 SQL이 아니라 Access라는 의미다. 그다음 Data Source는 Access MDB파일의 로컬 주소를 넣어주면 되고, 일반적으로 Access는 UserID와 UserPW를 사용하지 않으니 연결 문자열이 훨씬 간단해진다. 옛날에는 MDB였는데 이제는 확장자가 accdb로 변경됐네. 별 상관은 없는듯 하고, 여기서는 D:\test\TAccess.accdb라는 파일명으로 생성했다.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test\TAccess.accdb;"

SQL의 연결문자열을 Access의 연결문자열로 변경후 ConnectDB 함수를 호출하면 해당 MDB 파일과 연결이 된다. 여기서, 네트워크 드라이브를 잡으면 여럿이 동시사용도 가능하다.

이제 실습을 해보자.

아래 표를 참고해서 MS-Access를 이용해서 최대한 간단하게 만들어보자.

Table Name : Student
user_name	user_score	user_ranking
홍길동	90	2
김말숙	100	1
이순신	80	3

이제 Query문을 이용해서 이 데이터를 조회도 해보고, 삽입, 삭제등의 작업을 해보자. 폼에 아래 각각의 버튼 추가후 해당 버튼 Click 이벤트를 확인하자.

Type	Name	Text
Button	ButtonSelect	&Select
Button	ButtonInsert	&Insert
Button	ButtonUpdate	&Update
Button	ButtonDelete	&Delete

1. DB접속

Dim conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test\TAccess.accdb;"
If ConnectDB(conn_str) = False Then
    Debugger.Break()
End If

DB 접속은 연결문자열을 MS-Access DB에 맞게 작성후 지난 강좌의 MS-SQL 접속함수인 ConnectDB를 그대로 사용하면 된다.

2. Select

Dim sql = "SELECT * FROM Student ORDER BY user_ranking DESC"
DBRecordSet.Open(sql, DBConnection)
Dim cnt = DBRecordSet.RecordCount
Dim str As String = ""
For i As Integer = 0 To cnt - 1
    str = DBRecordSet.Fields("user_name").Value
    str &= " - " & DBRecordSet.Fields("user_score").Value
    str &= " - " & DBRecordSet.Fields("user_ranking").Value
    Debug.Print(str)
    DBRecordSet.MoveNext()
Next
DBRecordSet.Close()

지난번 강좌에서도 썼지만 VB.NET에서 DB에 접근해서 이 데이터를 사용하는 방법은 여러가지가 있다. DB 사용뿐만 아니라 어떤 코딩도 방법은 다양하다. 여기서는 그중 하나의 방법을 사용했을 뿐이고, 기타 다른 방법으로 사용하기를 원하시는 분들은 해당 글을 검색해서 사용하시면 되겠다.

몇가지 짚고 넘어가보자.

Dim sql = "SELECT * FROM Student ORDER BY user_ranking DESC"

SQL 쿼리문을 sql이라는 변수에 넣는다. 쿼리문은 그 자체만으로도 몇권의 책이 나올 정도로 복잡하니 이건 따로 확인해보도록 하고, 여기서는 Student라는 테이블의 모든 내용을 user_ranking이라는 필드를 기준으로 역순정렬하는 쿼리문을 작성했다.

DBRecordSet.Open(sql, DBConnection)

위 쿼리문의 결과 레코드를 DBRecordSet이라는 변수에 넣어준다. 커서가 Client이기 때문에 이 변수에 모든 레코드를 다 가져오게 된다.

Dim cnt = DBRecordSet.RecordCount

레코드의 개수를 cnt라는 변수에 넣는다. 위와 동일하게 커서를 Client로 설정했기 때문에 RecordCount라는 Property를 이용해서 전체 레코드 개수를 구할 수 있다.
이제 이 레코드를 하나씩 커서를 이동시키며 결과를 각각 불러와서 변수에 넣고 필요한 조작을 해주면 된다.

DBRecordSet.Fields("user_name").Value

현재 커서가 가리키고 있는 레코드에서 user_name이라는 필드의 값을 가져온다.

DBRecordSet.MoveNext()

이게 참 실수를 많이하는 구문인데 레코드 하나의 작업이 완료됐으니 커서를 다음 레코드로 옮겨줘야 한다. 이 한줄을 사용하지 않으면 전체 레코드 개수만큼 루프는 돌지만, 커서가 계속 제자리라 첫번째 레코드의 값만 계속 나올것이다.

DBRecordSet.Close()

이것도 엄청 중요한데 DBRecordSet은 한번 사용했으니 반드시 닫아줘야 다음번에 다른 쿼리로 작업을 할때도 사용이 가능하다. 즉, DBConnection 객체는 프로젝트 시작시 한번 연결해주고, 프로젝트 종료시 한번 닫아주면 되지만, DBRecordSet 객체는 매번 쿼리문을 실행하거나 DB를 대상으로 어떤 작업을 해줄때마다 해당 쿼리로 Open하고, 해당 레코드 작업이 끝나면 반드시 닫아줘야 한다. 안닫고 다른 쿼리를 다시 넣으려고 하면 무조건 에러가 나니 몇번 쓰다보면 익숙해질 것이다.

장황하게 설명했는데 사실 웬만한 내용은 다 나왔다. 이외에 Insert, Update, Delete는 거의 비슷하니 잠깐 설명하고 넘어가자.

몇번 강조했지만 방법은 다양하다. 여기서 사용하는 방법만 있는게 아니라는걸 다시한번 강조하며... 내가 즐겨쓰는 방법은 Select는 레코드셋으로, 그외 Insert, Update, Delete는 Connection 객체로 직접 Execute 해주는 방법을 사용한다. 그렇기에, 이들 구문은 DBRecordSet 객체는 필요없고, DBConnection 객체만 있으면 된다. 예를 들면 아래와 같은 방식이다.

위 DB에서 랭킹이 3위인 이순신을 삭제해보자.

sql = "DELETE * FROM Student WHERE user_ranking = '3'"
DBConnection.Execute(sql)

이렇게 DBconnection의 Execute 함수를 사용해서 직접 쿼리를 실행해주면 된다. 조건에 맞는 레코드를 삭제하는 구문이니 레코드셋은 필요가 없는것이다.

Insert, Update도 거의 동일하게 작업이 가능하니 직접 확인해보도록 하자.