[무작정 따라하기] Ms Acess + Ms-Sql server 연결하기 #1(내부접속편)

글이 길어 글을 나눠놨습니다.
중간에 링크타고 빠지셔도 가장 하단에 이 글로 회귀하는 링크를 넣어뒀습니다. 목차대로 나눠놨으니 필요한 부분 채워가시면서 보시길 바랍니다~


개발 환경 및 조건

• sql 서버 구동을 원하시는 분
• MS-Access가 설치되어 구동중인분
• 소프트웨어 버전들은 21년 12월 5일 기준 모두 최신 버전을 이용했습니다.
• 각 다운로드 링크에 사용한 버전이 기록되어 있습니다. 한번씩 버전 체크 해봐주시길 바랍니다.

SQL server가 설치가 안되신 분들은 아래 링크에서 설치를 하고 접속확인을 해보시기 바랍니다.
시도 단계가 다른 분들을 위해서 진행하는데 필요한 기본적인 사항들은 링크로 대체합니다.

목차

• 0. MS-SQL Server + SSMS 다운로드 및 설치하기 (express)
• 1. 테이블 만들기(테스트 파일)
• 2. MSSQL, SA 계정 활성화 하기
• 3. MS-SQL SSMS에 계정 추가하기
• 4. IP입히기
• 5. 접속(IP) 테스트
• 6. ODBC 구성하기
• 7. 테이블 내보내기와 가져오기
• 8. 참조 목록 추가와 테스트

깨끗하게 지우기와 설치하기

0. MS-SQL Server + SSMS 다운로드 및 설치하기 (EXPRESS)

1. 엑세스에서 파일, 테이블 만들기 (테스트할 파일이 있으시면 2번으로): 특별한 파일만들기가 아닙니다. mssql상에서 읽기, 쓰기, 수정, 리스팅 되는 파일입니다.

위는 Ms Access의 새 파일 만들때 나오는 화면입니다. 노란줄 친곳을 보시면 빈 데이터 베이스 만들기라는 걸 보실수 있습니다. MS Access는 DB를 구동하는 프로그램이란걸 알수 있지요.

로컬에서 DB를 파일 안이 아닌 SQL서버에서 사용하는걸 보여드리겠습니다.

예시를 위해서 간단한 테이블을 만들겠습니다. 필드 형식은 위와 같고 데이터 내용은 아래와 같습니다.

이제 위의 내용을 가지고 실험할 간단한 폼을 만들겠습니다.

이런식으로 폼을 만들었습니다. 각 오브젝트의 이름은 텍스트 박스는 라벨에 씌여있는 대로, 버튼은 저장버튼의 경우 ‘버저장’ 이런 식으로 앞에 ‘버’붙였고, 리스트는 ‘리스트’입니다.
다시 말씀드리지만 당연히 폼 만드는게 중요한건 아니고, "쓰기, 읽기, 지우기, 리스팅"이 가장 기본이 되기 때문에 그런 폼을 만들었습니다. 아마도 이미 가지고 계실겁니다. 코드는 아래에 붙입니다.

Option Compare Database Private Sub 리스트_AfterUpdate() ID = 리스트.Column(0) 이름 = 리스트.Column(1) 숫자 = 리스트.Column(2) End Sub Private Sub 버리스트초기화_Click() 리스트.RowSource = "select * from 테이블1" ID = Null 이름 = Null 숫자 = Null 리스트 = Null End Sub Private Sub 버삭제_Click() If Nz(ID, 0) = 0 Then Exit Sub End If CurrentDb.Execute "Delete * from 테이블1 where ID = " & ID & "", dbseechanges 버리스트초기화_Click End Sub Private Sub 버수정_Click() If Nz(ID, 0) = 0 Then Exit Sub End If If Nz(이름, "") = "" Or Nz(숫자, "") = "" Then MsgBox "이름 또는 숫자 없음" Exit Sub End If CurrentDb.Execute "update 테이블1 set 이름 = '" & 이름 & "', 숫자 = " & 숫자 & " where id = " & ID & " ", dbseechanges 버리스트초기화_Click End Sub Private Sub 버저장_Click() If Nz(ID, 0) <> 0 Then Exit Sub End If If Nz(이름, "") = "" Or Nz(숫자, "") = "" Then MsgBox "이름 또는 숫자 없음" Exit Sub End If CurrentDb.Execute "insert into 테이블1 (이름,숫자) values('" & 이름 & "'," & 숫자 & ")", dbseechanges 버리스트초기화_Click End Sub

혹시 서버 SA계정이 활성화 안되신분들은 아래의 링크를 보시고 활성화를 먼저 하시기 바랍니다.

2. MSSQL, SA 계정 활성화 하기

MSSQL, SA 계정 활성화 하기

SA계정이 활성화되면 내가 아닌 다른 사용자가 생길경우를 생각해서 로그인 계정을 별도로 만들어줘야 합니다.
(본인만 사용한다면 SA계정만 사용해도 관계없습니다.)
MSSQL에 다른 계정 생성을 해야한다면 아래 링크를 참조해주세요. 아니신분은 이어서 읽어 내려가시면 됩니다.

3. MS-SQL SSMS에 계정 추가하기

사용계정 sa2(임의의 이름)를 추가했습니다.

4. IP입히기

윈도우에서 SQL server 2019 구성관리자를 찾아 실행합니다.

• SQL EXPRESS에 대한 프로토콜 클릭
• TCP/IP 더블클릭
• IP 주소탭
• 스크롤 가장 아래로 내려서 IPALL 부분의 TCP 포트 1433 적고 적용

• 다시 TCP/IP 부분 더블 클릭
• 프로토콜 탭 클릭
• 사용부분을 아니오 -> 예 로 변경
• 하단의 적용, 확인

• SQL Server 서비스 클릭
• 우측 SQL Server 우클릭
• 다시 시작 클릭

5. 접속(IP) 테스트

• 서버이름: 127.0.0.1,1433
• 로그인: 생성계정
• 암호: 생성암호

접속 테스트 완료.

6. ODBC 구성하기

ODBC 구성하기

ODBC를 구성한 후

7. 테이블 내보내기와 가져오기

MS ACCESS로 가보겠습니다.

테이블 (우클릭)> 내보내기 > ODBC 데이터 베이스

확인

만들어 놓은 ODBC 파일을 클릭하고 확인

암호 넣고 확인

체크 신경 쓰지 마시고 닫기 눌러주세요.

이번엔 가져오기입니다. 그림의 경로로 ODBC 데이터베이스를 클릭합니다.

아래쪽 '연결 테이블 만들어 데이터 원본에 연결'을 선택합니다. 그리고 확인.

마찬가지로 ODBC 파일 선택하고 확인.

암호 넣고 확인

이미 올렸던 테이블 선택하고, 암호저장 체크 하고 확인 누릅니다.

암호저장

ID 선택 하고 확인

테이블이 2개가 됐습니다. 아래에 있는 '화살표가 달린 동그란 지구'모양의 테이블은 SQL서버상의 테이블입니다.
dbo_테이블1을 우클릭해서 이름을 테이블1로 변경해주면, 덮어쓰기가 되면서 로컬 테이블이 사라집니다.

예를 선택해줍니다.

테이블이 제대로 들어왔습니다.

* 처음에 보여드렸던 파일은 사무실에서 작성했었고, 지금집에서 엑세스 파일을 새로 작성해서 4번 데이터가 빠져있지만, 올렸던 파일 그대로 적용됐습니다.

8. 참조 목록 추가와 테스트

엑세스 프로그램에서 alt+f11을 누르면 VBA창이 열립니다,

도구에 참조를 누르시고 아래의 목록들을 모두 채워주세요.

엑세스를 사용하시는데 거의 기본 목록이니 파일 생성하시게 되면 바로바로 참조 챙겨주시기 바랍니다.

자 이제 두가지만 테스트 해보면 되겠습니다.
1) SSMS상에서 테이블 데이터확인과 키 설정
2) 추가, 삭제, 수정 : 폼을 열어서 추가를 하고, 삭제를 하나하고, 수정은 고길동 숫자를 8로 바꾸겠습니다.

8.1) SSMS상에서 테이블 데이터확인과 키 설정

DB이름에 우클릭해서 새로고침을 한번 해줍시다(습관적으로 해줍시다)

올린 테이블1인 dbo_테이블1에 우클릭해서 디자인을 클릭해줍니다.

ID에 우클릭후 기본키 설정을 클릭합니다.

ctrl + S 나 마우스 우클릭으로 테이블을 저장해줍니다.

8.2) 추가, 삭제, 수정

고길동은 숫자가 8로 바꿨고, 3,4번은 생성했다가 삭제한 흔적이 됐고, 5번은 새로 생성한 김나라입니다.

이로써 개발에 있어 가장 기초적인, 개발자 환경에서
MS-SQL 로컬서버를 돌리고 DB를 사용해 봤습니다.

이어지는 포스트

(따라하기) Ms Acess + Ms-Sql server 연결하기 #2(IIS 확장편+외부 접속)

추신 1, #1편이 가장 기초적인 내용이기 때문에 특히나 처음 서버를 접하시는 엑온 회원분들은 꼭 알아두시길 바랍니다.

추신 2. 나머지 사용자 확장과 기타등등은 이 1편이 메인이 되고, 추가로 뭔가를 더하는 내용입니다.

추신 3. 들뜬 마음에 바로 적용시키는 분들이 계실텐데, 원하는 형태의 시스템이 완성되기 전까지는 가급적 시스템 구성에 몰두하시고 완성후 적용시키시길 바라며, 시험적용을 하시고 싶으시다면 꼭 DB백업을 해놓고 적용하시길 바랍니다. 아시겠지만 프로그램은 만드는것보다 고치는게 더 힘듭니다.

추신4. 여러분께 제 부족한 노트가 도움이 됐길 바랍니다. 도움이 되셨다면, 광고 한번씩 클릭해주시면 감사드리겠습니다. 참고로 여러분께서 보는 광고는 구글 자동광고로 평소에 여러분들이 즐겨 찾으시던 것들이므로 누가 보느냐에 따라서 광고가 달라집니다. 들어보셨지요? 구글 알고리즘? 그겁니다 ㅎㅎ