DAO에서 MDB 파일 생성과정 예제
페이지 정보
작성자
오원장
쪽지보내기
메일보내기
자기소개
아이디로 검색
전체게시물
댓글 0건
조회 5,082회
작성일 12-08-03 22:22
본문
''** DBF File 의 내용을 읽어 들여 MDB File 을 생성함.
Private Sub mnuDBFToMDB_Click()
On Error GoTo ErrorRTN
Dim llngLoop As Long
Dim lstrDBFPath As String
Dim lstrDBFFileName As String
Dim ldaoDBFromDBF As Database
Dim ldaoRSFromDBF As Recordset
Dim lstrSQL As String
Dim lstrMDBFullPath As String
Dim ldaoWS As Workspace
Dim ldaoDB As Database
Dim ldaoTBL As TableDef
Dim ldaoIDX As Index
With Me.dlgCommon
.DialogTitle = "Select DBF File !!!"
.Filter = "DBF File (*.DBF) | *.DBF"
.InitDir = App.Path
.FileName = ""
.ShowOpen
If Trim(.FileName) = "" Then
Call mclsM.msdConfirmMsgBox("작업을 취소하셨습니다.")
Exit Sub
Else
If Dir(.FileName) = "" Then
Call mclsM.msdConfirmMsgBox("파일이 존재하지 않습니다." & vbCrLf & vbCrLf & "작업이 취소되었습니다.")
Exit Sub
End If
End If
For llngLoop = Len(.FileName) To 1 Step -1 ''** 선택한 DBF 파일의 Path 와 File Name 을 구한다.
If Mid(.FileName, llngLoop, 1) = "\" Then
lstrDBFPath = Left(.FileName, llngLoop - 1)
lstrDBFFileName = Mid(.FileName, llngLoop + 1)
Exit For
End If
Next llngLoop
End With
Set ldaoDBFromDBF = OpenDatabase(lstrDBFPath, False, True, "Dbase 5.0;") ''** DBF File 로 부터 데이타를 읽어온다.
lstrSQL = ""
lstrSQL = lstrSQL & " " & ""
lstrSQL = lstrSQL & " " & "select *"
lstrSQL = lstrSQL & " " & "from " & Left(lstrDBFFileName, Len(lstrDBFFileName) - 4)
Set ldaoRSFromDBF = ldaoDBFromDBF.OpenRecordset(lstrSQL)
lstrMDBFullPath = App.Path & "\dboZipCode.mdb" ''** 기존에 존재하는 MDB File 을 삭제하고 새로 생성함.
If Dir(lstrMDBFullPath) <> "" Then Kill lstrMDBFullPath
Set ldaoWS = DBEngine.Workspaces(0)
Set ldaoDB = ldaoWS.CreateDatabase(lstrMDBFullPath, dbLangKorean)
Set ldaoTBL = ldaoDB.CreateTableDef("tblZipCode") ''** MDB File 에 Table 생성.
With ldaoTBL
.Fields.Append .CreateField("fldSEQ", dbLong)
.Fields.Append .CreateField("fldZIPCODE", dbText, 7)
.Fields.Append .CreateField("fldSIDO", dbText, 4)
.Fields.Append .CreateField("fldGUGUN", dbText, 15)
.Fields.Append .CreateField("fldDONG", dbText, 52)
.Fields.Append .CreateField("fldBUNJI", dbText, 17)
.Fields("fldSEQ").Required = True ''** 각 필드의 속성 설정 및 인덱스를 생성한다. 필수 입력 항목인가?
Set ldaoIDX = .CreateIndex("idxSEQ") ''** 인덱스명 생성.
ldaoIDX.Fields.Append .CreateField("fldSEQ") ''** 인덱스 필드 지정.
ldaoIDX.Required = True ''** 필수 입력 항목인가?
ldaoIDX.Unique = True ''** 중복 입력 불가 항목인가?
.Indexes.Append ldaoIDX ''** 인덱스 추가.
Set ldaoIDX = Nothing
.Fields("fldZIPCODE").Required = True
.Fields("fldZIPCODE").AllowZeroLength = True ''** 빈문자열 입력을 허용할것인가?
Set ldaoIDX = .CreateIndex("idxZIPCODE")
ldaoIDX.Fields.Append .CreateField("fldZIPCODE")
ldaoIDX.Required = True
ldaoIDX.Unique = False
.Indexes.Append ldaoIDX
Set ldaoIDX = Nothing
.Fields("fldSIDO").Required = True
.Fields("fldSIDO").AllowZeroLength = True
Set ldaoIDX = .CreateIndex("idxSIDO")
ldaoIDX.Fields.Append .CreateField("fldSIDO")
ldaoIDX.Required = True
ldaoIDX.Unique = False
.Indexes.Append ldaoIDX
Set ldaoIDX = Nothing
.Fields("fldGUGUN").Required = True
.Fields("fldGUGUN").AllowZeroLength = True
Set ldaoIDX = .CreateIndex("idxGUGUN")
ldaoIDX.Fields.Append .CreateField("fldGUGUN")
ldaoIDX.Required = True
ldaoIDX.Unique = False
.Indexes.Append ldaoIDX
Set ldaoIDX = Nothing
.Fields("fldDONG").Required = True
.Fields("fldDONG").AllowZeroLength = True
Set ldaoIDX = .CreateIndex("idxDONG")
ldaoIDX.Fields.Append .CreateField("fldDONG")
ldaoIDX.Required = True
ldaoIDX.Unique = False
.Indexes.Append ldaoIDX
Set ldaoIDX = Nothing
.Fields("fldBUNJI").Required = True
.Fields("fldBUNJI").AllowZeroLength = True ''** 데이타가 빈문자열일수 있음을 설정함.
Set ldaoIDX = .CreateIndex("idxPrimaryKey") ''** Primary Key 생성.
ldaoIDX.Fields.Append .CreateField("fldSEQ")
ldaoIDX.Fields.Append .CreateField("fldZIPCODE")
ldaoIDX.Fields.Append .CreateField("fldSIDO")
ldaoIDX.Fields.Append .CreateField("fldGUGUN")
ldaoIDX.Fields.Append .CreateField("fldDONG")
ldaoIDX.Required = True
ldaoIDX.Primary = True
ldaoIDX.Unique = True
.Indexes.Append ldaoIDX
Set ldaoIDX = Nothing
.Indexes.Refresh
End With
ldaoDB.TableDefs.Append ldaoTBL
ldaoDB.TableDefs.Refresh
ldaoRSFromDBF.MoveLast
ldaoRSFromDBF.MoveFirst
With frmProgress
llngLoop = 0
.prgProgress.Min = 0
.prgProgress.Max = ldaoRSFromDBF.RecordCount
.prgProgress.Value = llngLoop
.lblTitle(1).Caption = CStr(llngLoop) & " / " & CStr(ldaoRSFromDBF.RecordCount)
Me.Refresh
.Show
.Refresh
End With
Do Until ldaoRSFromDBF.EOF
lstrSQL = ""
lstrSQL = lstrSQL & " " & ""
lstrSQL = lstrSQL & " " & "insert into tblZipCode"
lstrSQL = lstrSQL & " " & " ("
lstrSQL = lstrSQL & " " & " fldSEQ,"
lstrSQL = lstrSQL & " " & " fldZIPCODE,"
lstrSQL = lstrSQL & " " & " fldSIDO,"
lstrSQL = lstrSQL & " " & " fldGUGUN,"
lstrSQL = lstrSQL & " " & " fldDONG,"
lstrSQL = lstrSQL & " " & " fldBUNJI"
lstrSQL = lstrSQL & " " & " )"
lstrSQL = lstrSQL & " " & "values ("
lstrSQL = lstrSQL & " " & " " & CStr(ldaoRSFromDBF("SEQ")) & ","
lstrSQL = lstrSQL & " " & " '" & ldaoRSFromDBF("ZIPCODE") & "',"
lstrSQL = lstrSQL & " " & " '" & ldaoRSFromDBF("SIDO") & "',"
lstrSQL = lstrSQL & " " & " '" & ldaoRSFromDBF("GUGUN") & "',"
lstrSQL = lstrSQL & " " & " '" & ldaoRSFromDBF("DONG") & "',"
lstrSQL = lstrSQL & " " & " '" & ldaoRSFromDBF("BUNJI") & "'"
lstrSQL = lstrSQL & " " & " )"
ldaoDB.Execute lstrSQL
With frmProgress
DoEvents
.SetFocus
llngLoop = llngLoop + 1
.prgProgress.Value = llngLoop
.lblTitle(1).Caption = CStr(llngLoop) & " / " & CStr(ldaoRSFromDBF.RecordCount)
End With
ldaoRSFromDBF.MoveNext
Loop
Unload frmProgress
Call mclsM.msdConfirmMsgBox( _
"From : " & lstrDBFPath & "\" & lstrDBFFileName & vbCrLf & vbCrLf & _
"To : " & lstrMDBFullPath & vbCrLf & vbCrLf & _
"작업을 마쳤습니다." _
)
Set ldaoTBL = Nothing
ldaoDB.Close
Set ldaoDB = Nothing
Set ldaoWS = Nothing
ldaoRSFromDBF.Close
Set ldaoRSFromDBF = Nothing
ldaoDBFromDBF.Close
Set ldaoDBFromDBF = Nothing
DBEngine.CompactDatabase App.Path & "\dboZipCode.mdb", App.Path & "\dboZipCodeBackUp.mdb"
Kill App.Path & "\dboZipCode.mdb"
Name App.Path & "\dboZipCodeBackUp.mdb" As App.Path & "\dboZipCode.mdb"
Exit Sub
ErrorRTN:
Call mclsM.msdVBErrorMsgBox("mnuDBFToMDB_Click")
End Sub
댓글목록
등록된 댓글이 없습니다.