SQL ADODB VBA 업데이트 및 조회 연결

 

 

http://blog.naver.com/ldy2393?Redirect=Log&logNo=50150546955

 

 

 

 

 

첨부파일 (3)

아래질의에 대한 답변입니다.

안녕하세요 ^^

며칠간 인터넷 검색을해도 도저히 답을 찾을수 없어 질문을 드리게 되었습니다.

엑셀 및 엑세스는 2007버전을 쓰고 있습니다.

액세스로 부품에 대한 테이블을 만들어 놨습니다.

예를 들어 엑세스 파일명 "part.accdb", 테이블명 "부품정보"

필드내용 "machinery", "maker","type","part no","description","origin","unit","판매단가","weight","d.day","매입처","매입처단가"

테이블에는 부품 수만개 들어 가 있습니다. 이중 부품 판매단가가 있는것도 있고 없는것도 있습니다.

그리고 견적서폼을 엑셀 파일로 아래와 같이 만들었습니다.

견적서를 엑셀폼으로 만든 이유는 각 거래업체에서 견적의뢰 오는 폼이 다 제각각입니다. (word, 한글, 팩스, excel 등등)

그래서 갖다 붙이기 좋게 하기 위해서 입니다.

상기 폼에 17열 부터 부품에 대한 정보를 적고 혹은 갖다 붙이고 (description까지) 오른쪽 위에 있는 "DB조회" 버튼을 누르면 액세스 "부품정보" 테이블을 검색하여 MACHINERY, MAKER, PART NO, DESCRIPTION이 같은 열의 원하는 값 "판매단가","매입처","매입단가" 등을 원하는 셀 "J17", "N17","O17"에 자동으로 들어 가게 하고 싶습니다.

그리고 단가가 없는 아이템은 저희가 단가를 엑셀에 입력해서 "부품입력"버튼을 클릭하면 엑세스에 저장되게

하고 싶습니다.

ADO, DAO 등을 써서 해야 한다는것 까지는 알겠는데.. 인터넷에 있는 자료들은 전체를 가지고 오고 전체를 입력시크는것밖에 없어서 질문을 드리오니, 상세한 답변 부탁드립니다. (왕초보입니다 ^^;)

답변 주시는 분 복받으실겁니다!!!!

답변)

우선 데이터를 조회하려면, 하나의 엑세스화일에 조회할 내용이 들어가야 할 것 같습니다.

조회하려는 내용을 엑세스에 또 하나의 테이블을 만들어, 원본데이터랑, 조회할 데이터를 비교할 수 있도록 하여야 할 것 같습니다.

1단계로 엑세스에 조회테이블을 만들어 놓고, 엑셀의 내용을 엑세스에 보내 업데이트 시키고,

2단계로 다음 조회시에 기존 내용을 삭제하도록 만들고,

3단계로 DB테이블과 조회테이블을 비교하여 결과 값을 엑셀 시트에 표현하면 될 것 같습니다.

이 과정을 세개의 프로시저를 만들어 구현하고,

메인프로시저에서 세개의 프로시저를 순차적으로 실행시키면 가능할 것 같습니다.

첨부화일은 ACCESS 화일 Database1.accdb 를 원본으로하고, 견적폼은 조회하기 위한 엑셀화일로 구성되어 있습니다.

우선 엑세스화일에 두개의 테이블을 구축한 상태이고, 견적폼화일과 엑세스화일은 같은 경로에 있다는 전제에 만들었습니다.

경로가 다르면 나중에 그 부분만 설정해주면 될 것 같네요.

Dim Ws As Worksheet
Dim strModel As String
Dim strSQL As String
Dim vDB, vR
Const dbName = "\Database1.accdb"
Sub Main()
조회삭제
조회업데이트
자료조회
End Sub
Sub DoSQL_1()

'도구->참조에 Microsoft ActiveX data object Library 2.8를 참조후

Dim Rs As ADODB.Recordset
Dim strConn As String

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & dbName

Set Rs = New ADODB.Recordset

With Rs
.Source = strSQL
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With

End Sub
Sub DoSQL_2()

Dim Rs As ADODB.Recordset
Dim strConn As String

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & dbName

Set Rs = New ADODB.Recordset

With Rs
.Source = strSQL
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With
vR = Rs.GetRows

End Sub
Sub DoSQL_UPDATE()

Dim Rs As ADODB.Recordset
Dim strConn As String
Dim i As Integer

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & dbName

Set Rs = New ADODB.Recordset

With Rs
.Source = strSQL
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
For i = 1 To UBound(vDB, 1)
If vDB(i, 1) <> "" Then
.AddNew
.Fields("MACHINERY") = vDB(i, 1)
.Fields("MAKER") = vDB(i, 2)
.Fields("TYPE") = vDB(i, 3)
.Fields("PART NO") = vDB(i, 4)
.Update
End If
Next i
End With

End Sub
Sub 조회삭제()

strSQL = "DELETE * FROM [조회] "

DoSQL_1

End Sub
Sub 조회업데이트()
strSQL = "select * from [조회] "
vDB = Sheets("QUO").Range("b17", "e40")
DoSQL_UPDATE
End Sub
Sub 자료조회()
Dim i As Integer, r As Integer

strSQL = "SELECT B.* "
strSQL = strSQL & "FROM [조회] AS A LEFT JOIN [제품DB] AS B "
strSQL = strSQL & "ON A.MACHINERY =B.MACHINERY AND A.MAKER =B.MAKER AND A.TYPE = B.TYPE AND A.[PART NO] = B.[PART NO] "


DoSQL_2

With Sheet1
.Range("f17:g40,l17:m40,o17:s40").ClearContents
For i = 0 To UBound(vR, 2)
r = i + 17
.Cells(r, 6) = vR(4, i) 'Description
.Cells(r, 7) = vR(5, i) 'origin
.Cells(r, 12) = vR(11, i) 'weight
.Cells(r, 13) = vR(12, i) 'deliver time
.Cells(r, 15) = vR(7, i) '매입처1원가
.Cells(r, 16) = vR(8, i) '매입처1
.Cells(r, 17) = vR(9, i) '매입처2원가
.Cells(r, 18) = vR(10, i) '매입처2
.Cells(r, 19) = vR(13, i) 'Remark
Next i
End With
End Sub

 

 

 

 

 

 

 

아래 질의에 대한 답변입니다.

안녕하세요 ^^

엑셀, 엑세스 2007버젼이구요.

상기와같이 질문.ACCDB 파일에 테이블을 만들어 놓았습니다.

상기 엑셀 M열에 CHECK BOX들를 넣어 놓았고

체크박스에 V체크를 하고 DB업데이트 버튼을 누르면 행에서 MACHINERY, MAKER, TYPE, PART NO, DESCRIPTION, ORIGIN, D.DAY, 원가1, 매입처1, 원가2, 매입처2, WEIGHT, REMARK, 고유넘버가 엑세스파일의 테이블로 자동으로 들어가게 하고 싶습니다.

CHECK BOX에 CHECK가 된 것만 들어가게 하고 싶으며, 입력시 기존 엑세스 레코드에 MACHINERY, MAKER, TYPE, PART NO.가 중복되는게 있으면 덮어 쒸우고 싶습니다.

관련 파일은 http://www.marinetotal.com/DB_UBDATE.zip 클릭하시면 받으실수 있습니다.

꼭 좀 알려 주시기 바랍니다. 알려주시는분 복 왕창~ 받으실겁니다 ^^

답변)

우선 체크박스를 이용하려면, 체크박스와 셀을 연결하여야 합니다. 인쇄하고자하는 범위외에 같은 행에 셀연결을 해주어야 합니다.

- 체크박스 우클릭, 컨트롤서식-컨트롤 에서, 셀연결에 적당한 셀을 선택합니다(선택하면 해당셀 값이 TRUE, 선택해제되면 FALSE 값을 가집니다)

이렇게 해주므로써, 체크했는지 안했는지 셀값으로 파악할 수 있습니다.

전에 질문한 내용에 대한 추가 질문 같은데요.

이 체크된 데이터만 엑세스 테이블에 기존데이터가 있으면 수정, 없으면 추가 하는 내용인데요. 수정하는 부분이 잘 안되더군요. 그래서 아예 기존 데이터가 있으면 삭제하고, 추가하는 방법으로 구현했습니다.

아래 빨간색 부분이 추가된 코드입니다.

테이블명은 제품DB라고 바꾸었습니다.

코드)

Dim Ws As Worksheet
Dim strModel As String
Dim strSQL As String
Dim vDB, vR
Dim i As Integer
Const dbName = "\질문.accdb"
Sub Main()
조회삭제
조회업데이트
자료조회
End Sub
Sub DoSQL_1()

'도구->참조에 Microsoft ActiveX data object Library 2.8를 참조후

Dim Rs As ADODB.Recordset
Dim strConn As String

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & dbName

Set Rs = New ADODB.Recordset

With Rs
.Source = strSQL
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With

End Sub
Sub DoSQL_2()

Dim Rs As ADODB.Recordset
Dim strConn As String

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & dbName

Set Rs = New ADODB.Recordset

With Rs
.Source = strSQL
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With
vR = Rs.GetRows

End Sub
Sub DoSQL_UPDATE()

Dim Rs As ADODB.Recordset
Dim strConn As String
'Dim i As Integer

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & dbName

Set Rs = New ADODB.Recordset

With Rs
.Source = strSQL
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
For i = 1 To UBound(vDB, 1)
If vDB(i, 1) <> "" Then
.AddNew
.Fields("MACHINERY") = vDB(i, 1)
.Fields("MAKER") = vDB(i, 2)
.Fields("TYPE") = vDB(i, 3)
.Fields("PART NO") = vDB(i, 4)
.Update
End If
Next i
End With

End Sub
Sub 조회삭제()

strSQL = "DELETE * FROM [조회] "

DoSQL_1

End Sub
Sub 조회업데이트()
strSQL = "select * from [조회] "
vDB = Sheets("QUO").Range("b17", "e27")
DoSQL_UPDATE
End Sub
Sub 자료조회()
Dim r As Integer
Dim rsNum, cellNum, j As Integer

strSQL = "SELECT B.* "
strSQL = strSQL & "FROM [조회] AS A LEFT JOIN [제품DB] AS B "
strSQL = strSQL & "ON A.MACHINERY =B.MACHINERY AND A.MAKER =B.MAKER AND A.TYPE = B.TYPE AND A.[PART NO] = B.[PART NO] "


DoSQL_2
rsNum = Array(4, 5, 6, 7, 8, 9, 10, 12)
cellNum = Array(6, 7, 12, 14, 15, 16, 17, 19)
With Sheet1
.Range("f17:g27,l17:l27,n17:s27").ClearContents
For i = 0 To UBound(vR, 2)
r = i + 17
For j = 0 To UBound(rsNum)
.Cells(r, cellNum(j)) = vR(rsNum(j), i)
Next j
Next i
End With
End Sub

Sub DB업데이트()
vDB = Sheet1.Range("B17", "v27")
For i = 1 To UBound(vDB, 1)
If vDB(i, UBound(vDB, 2)) = True Then
strSQL = "DELETE * FROM [제품DB] WHERE MACHINERY ='" & vDB(i, 1) & "' AND MAKER ='" & vDB(i, 2) & "' AND TYPE = '" & vDB(i, 3) & "' AND [PART NO] = '" & vDB(i, 4) & "' "
DB_UPDATE
End If
Next i
End Sub

Sub DB_UPDATE()

Dim Rs As ADODB.Recordset
Dim strConn As String
'Dim i As Integer

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & dbName

Set Rs = New ADODB.Recordset

With Rs

.Source = strSQL
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open

Set Rs = Nothing
Set Rs = New ADODB.Recordset
.Source = "SELECT * FROM 제품DB "
.ActiveConnection = strConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
.AddNew
.Fields("MACHINERY") = vDB(i, 1)
.Fields("MAKER") = vDB(i, 2)
.Fields("TYPE") = vDB(i, 3)
.Fields("PART NO") = vDB(i, 4)
.Fields("DESCRIPTION") = vDB(i, 5)
.Fields("ORIGIN") = vDB(i, 6)
.Fields("DDAY") = vDB(i, 11)
.Fields("원가1") = vDB(i, 13)
.Fields("매입처1") = vDB(i, 14)
.Fields("원가2") = vDB(i, 15)
.Fields("매입처2") = vDB(i, 16)
.Fields("WEIGHT") = vDB(i, 17)
.Fields("REMARK") = vDB(i, 18)
.Update
.Close
End With

Set Rs = Nothing

End Sub