Board logo

標題: [發問] Excel工作表單與Access資料庫的VBA互動問題 [打印本頁]

作者: c_c_lai    時間: 2013-12-8 19:44     標題: Excel工作表單與Access資料庫的VBA互動問題

說明:
原本這是前陣子一位麻辣網友的提問,於日前完成撰寫後,
卻忘了該提問是哪一篇,所以再次代為提文。

A.  Excel Data  -  從 "data" 工作表單直接存取資料、      
    SQL Data    -  從 Access 資料庫 DataSrc 存取資料
    當輸入 "名字" 後,按下 "輸入確定",
    程式即根據 "Excel Data" 或 "SQL Data" 做資料篩選,
    如資料不存在,則右上端顯示 "新增資料", 反之 "更新資料"。
B.  無論是選擇 "Excel Data" 或 "SQL Data" 於資料儲存時,均會
    同步寫入至 Excel "data" 工作表單,以及對應之 Access 資料庫。
C.  目前只能處裡 Access MDF 的資料庫, 2010 的 ACCDB 資料庫在
    存取上遇到瓶頸,會於 rs.Open strSQL, cnn, 1, 3 讀取處出現
    錯誤訊息,一直無解,尚請各位大大鼎力協助解決,謝謝大家!
D.  簽證費用、機票費用的加總計算處理,因卡在 C 項的問題,故尚未處裡。
E.  這是一篇我個人認為蠻能玩味的程式撰寫,希望大家共襄盛舉。
[attach]17012[/attach]
[attach]17013[/attach]
作者: c_c_lai    時間: 2013-12-8 19:46

回復 1# c_c_lai
附上程式:
[attach]17014[/attach]
作者: c_c_lai    時間: 2013-12-8 19:48

回復 2# c_c_lai
Acess 資料庫:
機票記錄明細表.mdb
機票記錄明細表檔案.accdb  (For 2010)
[attach]17015[/attach]
尚請各位大大鼎力協助解決,謝謝大家!
作者: c_c_lai    時間: 2013-12-9 08:54

本帖最後由 c_c_lai 於 2013-12-9 08:55 編輯

回復 3# c_c_lai
因為有些會員因目前尚無法下載,所以將程式碼貼上,
尚請各位大大鼎力幫忙,解決處理 Access 2010 VBA 連結問題,
在此先行說聲謝謝幫忙!
  1. '  [ThisWorkbook]
  2. Option Explicit

  3. Private Sub Workbook_Open()
  4.     With Sheets("機票作業")
  5.         .ExcelData.Value = True
  6.         .SQLData.Value = False
  7.         .CallID.Value = ""
  8.         .DateTime.Value = ""
  9.         .RecordExisted.Caption = ""
  10.         .Activate
  11.         .SaveData.Enabled = False
  12.         .ResetData.Enabled = False
  13.         .ResetData_Click
  14.     End With
  15. End Sub
複製代碼

作者: c_c_lai    時間: 2013-12-9 08:58

回復 4# c_c_lai
  1. '  [Sheet1 (機票作業)]
  2. Option Explicit
  3. Option Base 1

  4. Dim cnn As Object      '  New ADODB.Connection
  5. Dim cmd As Object      '  New ADODB.Command
  6. Dim rs As Object       '  New ADODB.Recordset
  7. Dim strSQL As String
  8. Dim editMode As Boolean
  9. Dim State, CursorLocation

  10. Sub OpenDB()
  11.     If ExcelData.Value = True Then    '  [data$]
  12.         Set cnn = CreateObject("ADODB.Connection")
  13.         Set rs = CreateObject("ADODB.Recordset")
  14.         Set cmd = CreateObject("ADODB.Command")
  15.     Else                              '  機票紀錄
  16.         Set cnn = New ADODB.Connection
  17.         Set rs = New ADODB.Recordset
  18.         Set cmd = New ADODB.Command
  19.     End If
  20.     '  ObjectStateEnum Values
  21.     '  Returns a value describing if the connection is open or closed
  22.     '  Specifies whether an object is open or closed, connecting to a data source,
  23.     '  executing a command, or retrieving data.
  24.     '  --------------------------------------------------------------------------
  25.     '  Constant             Value     Description
  26.     '  --------------------------------------------------------------------------
  27.     '  adStateClosed          0       The object is closed
  28.     '  adStateOpen            1       The object is open
  29.     '  adStateConnecting      2       The object is connecting
  30.     '  adStateExecuting       4       The object is executing a command
  31.     '  adStateFetching        8       The rows of the object are being retrieved
  32.     With cnn
  33.         If .State = 1 Then .Close    '  adStateOpen
  34.         
  35.         If ExcelData.Value = True Then
  36.             .ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
  37.                                 ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
  38.         Else
  39.             .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  40.                               ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表.mdb" & ";"
  41.             '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  42.             '  .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  43.             '                    ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表檔案.accdb" & ";"
  44.         End If
  45.         .Open
  46.     End With
  47. End Sub

  48. Sub closeRS()
  49.     With rs
  50.         If ExcelData.Value = False Then
  51.             If .State = 1 Then .Close    '  adStateOpen
  52.             '  The CursorLocation property is used to set or return the location of the cursor.
  53.             '  This property can be set to one of the CursorLocationEnum constants listed in the following table.
  54.             '  Enumeratio0n     Value   Description
  55.             '  adUseNone          1     This value indicates no cursor location.
  56.             '                           This value is not supported by the MicrosoftR OLE DB Provider for AS/400 and VSAM.
  57.             '  adUseServer        2     This value indicates that the data provider or driver-supplied cursor is used.
  58.             '  adUseClient        3     This value indicates that a client-side cursor supplied by a local cursor library
  59.             '                           is to be used.
  60.             '  adUseClientBatch   3     For backward compatibility, this value indicates that a client-side cursor supplied by
  61.             '                           a local cursor library is to be used.
  62.             .CursorLocation = 3          '  adUseClient
  63.         Else
  64.             If State = 1 Then .Close    '  adStateOpen
  65.             CursorLocation = 3          '  adUseClient
  66.         End If
  67.     End With
  68. End Sub

  69. Private Sub ExcelData_Click()
  70.     '  MsgBox "Option is Excel Data "
  71. End Sub

  72. Private Sub SQLData_Click()
  73.     '  MsgBox "Option is SQL Data "
  74. End Sub

  75. Private Sub UpdateDropDowns()
  76.     If ExcelData.Value = True Then
  77.         strSQL = "Select Distinct [簽證內容] From [簽證項目$] Order by [簽證內容]"
  78.     Else
  79.         strSQL = "Select Distinct 簽證內容 From 簽證項目 Order by 簽證內容"
  80.     End If
  81.    
  82.     '  closeRS
  83.     OpenDB
  84.     License1.Clear
  85.     License2.Clear
  86.    
  87.     rs.Open strSQL, cnn, 1, 3     '  adOpenKeyset, adLockOptimistic
  88.     If rs.RecordCount > 0 Then
  89.         Do While Not rs.EOF
  90.             License1.AddItem rs.Fields(0)    '  Select Distinct [簽證內容] From
  91.             License2.AddItem rs.Fields(0)    '  Select Distinct [簽證內容] From
  92.             rs.MoveNext
  93.         Loop
  94.     Else
  95.         MsgBox "I was not able to find any unique 簽證內容.", vbCritical + vbOKOnly
  96.         Exit Sub
  97.     End If
  98. End Sub

  99. Private Sub Confirm_Click()
  100.     Dim tblName As String, fld As String
  101.     Dim nCode As Range
  102.    
  103.     If CallID.Text = "" Then RecordExisted.Caption = "": Exit Sub
  104.    
  105.     RecordExisted.Caption = ""
  106.     DateTime.Value = ""
  107.     DataCear

  108.     DateTime.Enabled = True
  109.         
  110.     UpdateDropDowns
  111.         
  112.     If ExcelData.Value = False And SQLData.Value = False Then ExcelData.Value = True
  113.         
  114.     If ExcelData.Value = True Then      '  從 Sheets("data") 中擷取資料
  115.         tblName = "[data$]"
  116.         fld = "[名字]"

  117.         Set nCode = Sheets("data").[B:B].Find(CallID.Text, , , 1)
  118.         If Not nCode Is Nothing Then
  119.             editMode = True
  120.             RecordExisted.ForeColor = &HFF0000
  121.             RecordExisted.Caption = "更新目前資料"
  122.             DeleteData.Visible = True
  123.                
  124.             With nCode
  125.                 DeptNo.Text = .Offset(, -1)
  126.                 DateTime.Text = .Offset(, 1)
  127.                 CreditDate.Text = .Offset(, 2)
  128.                 routinefrom.Text = .Offset(, 3)
  129.                 routineto.Text = .Offset(, 4)
  130.                 contents.Text = .Offset(, 5)
  131.                 cabin.Text = .Offset(, 6)
  132.                 License1.Text = .Offset(, 7)
  133.                 LicenseFee1.Text = .Offset(, 8)
  134.                 License2.Text = .Offset(, 9)
  135.                 LicenseFee2.Text = .Offset(, 10)
  136.                 ticketfee.Text = .Offset(, 11)
  137.                 totalfee.Text = .Offset(, 12)
  138.                 remarks.Text = .Offset(, 13)
  139.             End With
  140.         Else
  141.             editMode = False
  142.             RecordExisted.ForeColor = 255
  143.             RecordExisted.Caption = "新增一筆資料"
  144.             DeleteData.Visible = False
  145.             '  DateTime.Enabled = True
  146.             DateTime.Text = Now()
  147.         End If
  148.     Else                               '  從 Access 機票紀錄 中擷取資料
  149.         tblName = "機票紀錄"
  150.         fld = "名字"
  151.    
  152.         '  Populate data
  153.         strSQL = "SELECT * FROM " & tblName & " WHERE " & fld & " = '" & CallID.Text & "'"
  154.             
  155.         '  Now extract data
  156.         closeRS
  157.         OpenDB
  158.                
  159.         rs.Open strSQL, cnn, 1, 3     '  adOpenKeyset, adLockOptimistic
  160.         '  rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
  161.         '  rs.Open strSQL, cnn, , adOpenDynamic
  162.         
  163.         If rs.RecordCount > 0 Then
  164.             editMode = True
  165.             RecordExisted.ForeColor = &HFF0000
  166.             RecordExisted.Caption = "更新目前資料"
  167.             DeleteData.Visible = True
  168.                
  169.             '  將搜尋到隻資料寫入各相關欄位內 (0 To rs.Fields.Count - 1)
  170.             With rs
  171.                 DeptNo.Value = .Fields(0).Value
  172.                 DateTime.Value = .Fields(2).Value
  173.                 CreditDate.Value = .Fields(3).Value
  174.                 routinefrom.Value = .Fields(4).Value
  175.                 routineto.Value = .Fields(5).Value
  176.                 contents.Value = .Fields(6).Value
  177.                 cabin.Value = .Fields(7).Value
  178.                 License1.Value = .Fields(8).Value
  179.                 LicenseFee1.Value = .Fields(9).Value
  180.                 License2.Value = .Fields(10).Value
  181.                 LicenseFee2.Value = .Fields(11).Value
  182.                 ticketfee.Value = .Fields(12).Value
  183.                 totalfee.Value = .Fields(13).Value
  184.                 remarks.Value = .Fields(14).Value
  185.             End With
  186.         Else
  187.             editMode = False
  188.             RecordExisted.ForeColor = 255
  189.             RecordExisted.Caption = "新增一筆資料"
  190.             DeleteData.Visible = False
  191.             '  DateTime.Enabled = True
  192.             DateTime.Text = Now()
  193.         End If
  194.     End If
  195.     Confirm.Enabled = False
  196.     SaveData.Enabled = True
  197.     ResetData.Enabled = True
  198.     DateTime.Enabled = False
  199. End Sub
複製代碼

作者: c_c_lai    時間: 2013-12-9 08:59

回復 5# c_c_lai
  1. Private Sub DeleteData_Click()
  2.     Dim nCode As Range, ret As Boolean
  3.    
  4.     With Sheets("data")
  5.         Set nCode = .[B:B].Find(CallID.Text, , , 1)
  6.         .Rows(Val(Mid(nCode.Address, 4))).EntireRow.Delete Shift:=xlUp
  7.     End With
  8.    
  9.     ret = ExcelData.Value
  10.     ExcelData.Value = False
  11.    
  12.     closeRS
  13.     OpenDB
  14.    
  15.     strSQL = "DELETE FROM 機票紀錄 WHERE 名字 = '" & CallID.Text & "'"
  16.     cmd.CommandText = strSQL
  17.    
  18.     cmd.ActiveConnection = cnn
  19.     cmd.Execute
  20.     cnn.Close
  21.    
  22.     Confirm.Enabled = True
  23.     ExcelData.Value = ret
  24.     ResetData_Click
  25. End Sub

  26. Sub ResetData_Click()
  27.     CallID.Text = ""
  28.     RecordExisted.Caption = ""
  29.     Confirm.Enabled = True
  30.     DataCear
  31. End Sub

  32. Private Sub DataCear()
  33.     DeptNo.Text = ""
  34.     DateTime.Text = ""
  35.     CreditDate.Text = ""
  36.     License1.Text = ""
  37.     LicenseFee1.Text = "0"
  38.     License2.Text = ""
  39.     LicenseFee2.Text = "0"
  40.     cabin.Text = ""
  41.     ticketfee.Text = "0"
  42.     totalfee.Text = "0"
  43.     routinefrom.Text = ""
  44.     routineto.Text = ""
  45.     contents.Text = ""
  46.     remarks.Text = ""
  47. End Sub

  48. Private Sub SaveData_Click()
  49.     Dim totalRows As Long, ret As Boolean
  50.     Dim nCode As Range
  51.    
  52.     ret = ExcelData.Value
  53.     ExcelData.Value = True
  54.             
  55.     With Sheets("data")
  56.         '  寫入 Sheets("data")
  57.         If editMode = True Then
  58.             Set nCode = Sheets("data").[B:B].Find(CallID.Text, , , 1)
  59.             
  60.             With nCode
  61.                 .Offset(, -1) = DeptNo.Text
  62.                 .Offset(, 2) = CreditDate.Text
  63.                 .Offset(, 3) = routinefrom.Text
  64.                 .Offset(, 4) = routineto.Text
  65.                 .Offset(, 5) = contents.Text
  66.                 .Offset(, 6) = cabin.Text
  67.                 .Offset(, 7) = License1.Text
  68.                 .Offset(, 8) = LicenseFee1.Text
  69.                 .Offset(, 9) = License2.Text
  70.                 .Offset(, 10) = LicenseFee2.Text
  71.                 .Offset(, 11) = ticketfee.Text
  72.                 .Offset(, 12) = totalfee.Text
  73.                 .Offset(, 13) = remarks.Text
  74.             End With
  75.         Else
  76.             With Sheets("data")
  77.                 strSQL = "SELECT * FROM [data$] WHERE [名字] ='" & CallID.Text & "'"
  78.             
  79.                 '  Now extract data
  80.                 closeRS
  81.                 OpenDB
  82.                
  83.                 rs.Open strSQL, cnn, 1, 3     '  adOpenKeyset, adLockOptimistic
  84.                
  85.                 If rs.RecordCount = 0 Then  ' 先判斷資料是否已經存在,如果不存在,則 ...
  86.                     Set nCode = .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1)
  87.                     
  88.                     With nCode
  89.                         .Offset(, 0) = CallID.Text
  90.                         .Offset(, -1) = DeptNo.Text
  91.                         .Offset(, 1).NumberFormat = "m/d/yyyy hh:mm:ss"
  92.                         .Offset(, 1) = Format(DateTime.Text, "m/d/yyyy hh:mm:ss")
  93.                         .Offset(, 2) = CreditDate.Text
  94.                         .Offset(, 3) = routinefrom.Text
  95.                         .Offset(, 4) = routineto.Text
  96.                         .Offset(, 5) = contents.Text
  97.                         .Offset(, 6) = cabin.Text
  98.                         .Offset(, 7) = License1.Text
  99.                         .Offset(, 8) = LicenseFee1.Text
  100.                         .Offset(, 9) = License2.Text
  101.                         .Offset(, 10) = LicenseFee2.Text
  102.                         .Offset(, 11) = ticketfee.Text
  103.                         .Offset(, 12) = totalfee.Text
  104.                         .Offset(, 13) = remarks.Text
  105.                     End With
  106.                 End If
  107.             End With
  108.         End If
  109.         
  110.         ExcelData.Value = False
  111.         '  Now extract data
  112.         closeRS
  113.         OpenDB
  114.             
  115.         '  寫入 Access 資料庫
  116.         If editMode = True Then
  117.             strSQL = "Update 機票紀錄 SET 名字 = '" & CallID.Text & "', 單位 = '" & DeptNo.Text & _
  118.                      "', 刷卡日期 = '" & CreditDate.Text & "', 行程日期從 = '" & routinefrom.Text & _
  119.                      "', 行程日期到 = '" & routineto.Text & "', 行程內容 = '" & contents.Text & _
  120.                      "', 艙等 = '" & cabin.Text & "', 簽證內容1 = '" & License1.Text & "', 簽證費用1 = " & _
  121.                      LicenseFee1.Text & ", 簽證內容2 = '" & License2.Text & "', 簽證費用2 = " & LicenseFee2.Text & _
  122.                      ", 機票費用 = " & ticketfee.Text & ", 總計 = " & totalfee.Text & ", 備註 = '" & _
  123.                      remarks.Text & "' WHERE 名字 = '" & CallID.Text & "';"
  124.         Else
  125.             strSQL = "INSERT INTO 機票紀錄 (單位,名字,日期,刷卡日期,行程日期從,行程日期到,行程內容," & _
  126.                      "艙等,簽證內容1,簽證費用1,簽證內容2,簽證費用2,機票費用,總計,備註) VALUES ('" & DeptNo.Text & "','" & _
  127.                      CallID.Text & "','" & DateTime.Text & "','" & CreditDate.Text & "','" & routinefrom.Text & "','" & _
  128.                      routineto.Text & "','" & contents.Text & "','" & cabin.Text & "','" & License1.Text & "'," _
  129.                      & LicenseFee1.Text & ",'" & License2.Text & "'," & LicenseFee2.Text & "," & ticketfee.Text & "," & _
  130.                      totalfee.Text & ",'" & remarks.Text & "') ;"
  131.         End If
  132.         '  Sheets("data").[A20] = strSQL     '  檢查 strSQL 語法內容是否正確
  133.         cmd.CommandText = strSQL
  134.    
  135.         cmd.ActiveConnection = cnn
  136.         cmd.Execute
  137.         cnn.Close
  138.         
  139.         Confirm.Enabled = True
  140.         SaveData.Enabled = False
  141.         ResetData.Enabled = False
  142.         ExcelData.Value = ret
  143.         ResetData_Click
  144.     End With
  145. End Sub
複製代碼

作者: aa7551    時間: 2013-12-9 18:34

將data放在Access,為何不直接在Access完成?
作者: c_c_lai    時間: 2013-12-9 18:46

將data放在Access,為何不直接在Access完成?
aa7551 發表於 2013-12-9 18:34

我之所以要同時處裡 Excel 工作表單與 Access 資料庫的道理是希望
竊由此範例去讓人了解、應用、透過 ADO 如何去處理工作表單與
Access 資料庫的實作。
這原本是一位網友的提問,想透過這個議題讓她了解兩造的處理方式,
並同時能去理解如何處理雙工的作業。
作者: stillfish00    時間: 2013-12-10 23:43

回復 1# c_c_lai
accdb 中,表單名稱變了所以查不到,
機票記錄<>機票紀錄。
作者: c_c_lai    時間: 2013-12-11 07:12

回復 9# stillfish00
太感謝你了!我原本一直在找原因(百思不解),
但千想萬想就是沒注意到一字之差 (紀 -> 記),
再次言謝,本程式終於能於 Office 2003、
Office 2007、2010 順利地自如作業了。
作者: ML089    時間: 2013-12-11 09:33

回復 10# c_c_lai

我發現stillfish00大真得很細心,上次timer的事也是他幫忙的

我想問 使用ACCESS 資料檔時,可以多人同時存取嗎?(突然想起以前用DBASE的一些資料鎖定存取問題,問得有點籠統請勿介意)

我跑一些結構分析,以前是看TXT檔,現在也多了ACCESS的MDB可以使用,看到你這篇讓我在想使用MDB處理會比TXT檔存取快嗎?
作者: c_c_lai    時間: 2013-12-11 13:38

回復  c_c_lai

我發現stillfish00大真得很細心,上次timer的事也是他幫忙的

我想問 使用ACCESS 資料 ...
ML089 發表於 2013-12-11 09:33

以往早期 dBase/dBaseII 在資料存取時,需考慮多使用者 (Multi User) 之資料存取的鎖定與釋放,
以避免檔案被鎖死 (Dead Lock),後期演變的 Access/Clipper 等資料庫在 I/O 上已有改進及處理。
且都遵行 ANSI SQL 的標準規範。所以目前一般程式人員 (Programmer) 均有一共識,即在處理
有關資料庫作業,一執行完畢,便隨手關閉資料庫。如此,塞車的瓶頸問題便行而降低。   
現今要考量的,是資料的實際能處理儲存量的多寡,索引的執行速度,最大的儲存資料筆數等。
為何有些企業會去考量採用 MS SQL、更勝者會去評估 PosgreSQL、MySQL、Informix、InterBase、
Oracle 等知名專業資料庫研發廠商,亦是此道理。
也真謝謝 stillfish00大大,他真得很細心,這也許是當局者迷吧!
作者: ML089    時間: 2013-12-14 23:40

回復 12# c_c_lai


謝謝你,說明的很清楚。(PS 我記得上次我已經回覆過,我好像有老人癡呆症了 哈哈)

EXCEL資料多人同時使用的介紹很少,或應該用ACCESS吧
作者: c_c_lai    時間: 2013-12-15 08:27

回復 13# ML089
之前曾經將 Excel 檔案上傳放置於 Google Chrome 個人檔案紀錄內,
提供多位作業人員隨時記錄及修正,記憶中好像也不曾有發生異狀隻情事。

以往在職前實作的資料處理,幾乎都記錄於 Clipper, MS SQL、
以及 PostgreSQL 上。因極少接觸 Office所以只有在練習時才
會去試圖操作 Access,但不是很熟。
其實資料庫的作業處理模式都是很雷同的,很容易舉一反三。。
作者: c_c_lai    時間: 2013-12-15 08:28

回復 14# c_c_lai
2014年機票記錄:
本程式之簽證費用、機票費用的加總計算處理我已經將它
加入了 (利用字典物件的處理),使用者於瞭解 Excel 表單處理、
以及 Access 的資料存取後,便可從程式中將它們予以分開,
而只擷取使用者其本身需求的部分。 目前程式包含 A、B 兩部分:
A、純粹 Excel 工作表單 (本身) 儲存作業,資料儲存於工作表單中;
B、將所有處理資料結果儲存至 Access 資料庫中,方便日後存取作業。
作者: ML089    時間: 2013-12-15 12:02

回復 14# c_c_lai

謝謝你的詳細說明

EXCEL 檔案兩個人同時開啟就會有問題,除非設為共用模式,共用模式我試過會越操作檔案會越來越大操作也會越來越慢,可能是EXCEL版本不同時的問題(還在測試)

講到 Clipper 是我的最愛也是最恨,真是往事只能回味

其實資料庫處理應該要使用ACCESS來處理比EXCEL好,只是公司大部分是做工程計算工程師只會用EXCEL,雖目前工程計算程式也支援MDB,我還是想由MDB將資料搬至EXCEL大家比較好使用。
你的程式可以讓我好好研究一下,可能需要花點時間消化。

其實這些不我的工作內容,我只是有興趣研究EXCEL,所以腳步慢一點,等過一些時日有問題再來請教。
作者: bear0925900003    時間: 2013-12-17 16:26

回復 1# c_c_lai


    讚
作者: c_c_lai    時間: 2013-12-26 08:54

我將 acdx 大大於 2013-12-11 16:52 提問的
"如何讓ComboBox可以用滑鼠滾動?" 解決方案
加以應用加入到 "簽證內容1"、"簽證內容2"
內,可作為範例引用,如尚有更佳的處理方法
尚請各位大大指教!
[attach]17110[/attach]
作者: JEAN    時間: 2013-12-26 09:42

本帖最後由 JEAN 於 2013-12-26 09:51 編輯

回復 1# c_c_lai


   您好:
這個2014年機票記錄檔案是我做的,裡面多一個"機票作業"...這個東西好像有一點怪怪的
請問你現在是寫機票作業的VBA的程式,還是裡面的"機票"的VBA程式。

我看不懂裡面在寫什麼...機票作業裡面的表格都不能用
作者: c_c_lai    時間: 2013-12-26 10:28

本帖最後由 c_c_lai 於 2013-12-26 10:53 編輯

回復 19# JEAN
我就是一時忘了當初是誰提問的,實在
太好了!
當初妳的提問是 "機票",我看了之後便
試著將它改成 工作表單、以及 Access 資料庫
都能執行的方式 (同步示範如何作業)。舉例來說:
如果單純地只想在 Excel 做輸入畫面,儲存時將
資料儲存到  Access 資料庫,那麼便將 Excel "data" 儲存
資料的部分移除,只保留儲存 Access 資料庫的程式碼。
反之、亦然。妳一定會問為何是綜合的,這純粹是為了
測試、及瞭解,如何使用 Excel 應用 ADO 處理資料問題,
以及如何同時處理 Access 資料庫的讀取、查詢、刪除、以及
寫入的語法應用而做出來的範例。所以才將妳當初的提問
均寫在 "機票作業" 、以及 "data" 內之故。如此、亦回復了
工作表單 "機票" 的處裡提問。這麼說明是否解答了妳的問題?
作者: c_c_lai    時間: 2013-12-26 10:41

本帖最後由 c_c_lai 於 2013-12-26 10:50 編輯

回復 19# JEAN
至於 "機票作業"裡面的表格都不能用
請參考附件:
[attach]17112[/attach]
[attach]17113[/attach]
如果妳那端是開啟 Access MDF 的檔案 (Office 2003) :
  1. Sub OpenDB()
  2.     If ExcelData.Value = True Then    '  [data$]
  3.         Set cnn = CreateObject("ADODB.Connection")
  4.         Set rs = CreateObject("ADODB.Recordset")
  5.         Set cmd = CreateObject("ADODB.Command")
  6.     Else                              '  機票紀錄
  7.         Set cnn = New ADODB.Connection
  8.         Set rs = New ADODB.Recordset
  9.         Set cmd = New ADODB.Command
  10.     End If

  11.     With cnn
  12.         If .State = 1 Then .Close    '  adStateOpen
  13.         
  14.         If ExcelData.Value = True Then
  15.             .ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
  16.                                 ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
  17.         Else
  18.             '  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  19.             '                    ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表.mdb" & ";"
  20.             '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  21.             .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  22.                               ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表檔案.accdb" & ";"
  23.         End If
  24.         .Open
  25.     End With
  26. End Sub
複製代碼
則將上列程式碼之 Else 的部分改成如下:
  1.         Else
  2.            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  3.                              ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表.mdb" & ";"
  4.              '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  5.             '   .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  6.             '                   ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表檔案.accdb" & ";"
  7.         End If
複製代碼
即可。
此程式碼可以處理 MDF (For Excel 2003) 或者是 ACCDB (For Excel 2007、2010) 等資料庫格式。
作者: JEAN    時間: 2013-12-26 13:50

回復 21# c_c_lai


您好:

你做的機票作業,做的很好。 ;P
不過我開啟都不能始用....
[attach]17114[/attach]
作者: c_c_lai    時間: 2013-12-26 15:46

回復 22# JEAN
一、 將 2014年機票記錄.xls、機票記錄明細表.mdb (抑或是 機票記錄明細表檔案.accdb)
     全部都放置於同一目錄下。 例如: 我為了方便測試起見,而將它們全數放在一個名為
     "2014年機票記錄" D 磁碟機的次目錄下。
二、 如果妳那端是開啟 Access MDF 的檔案 (Office 2003),哪麼妳必須先將 21# 所說的
     程式碼 (Else 部分) 先行修正。
     目前我是預設開啟的是 Access ACCDB 的資料庫,如果要使用 Access MDF 的檔案,請將
     ACCDB 的部分予以 Mark 起來,而將原本 Mark 之 MDF 的部分打開 (移除 Mark ' ),
     如此即可使用 MDF 的檔案了。
     (兩種資料庫我都一齊放置於 D:\2014年機票記錄\ 目錄下,以方便測試及觀察資料變化)
三、 點選該次目錄 D:\2014年機票記錄\2014年機票記錄.xls 便可執行。
[attach]17115[/attach]
[attach]17116[/attach]
作者: c_c_lai    時間: 2013-12-26 15:47

回復 22# JEAN
[attach]17117[/attach]
[attach]17118[/attach]
作者: c_c_lai    時間: 2013-12-26 15:56

回復 22# JEAN
[attach]17119[/attach]
[attach]17120[/attach]
作者: JEAN    時間: 2013-12-27 13:32

回復 25# c_c_lai


您好:
謝謝您的機票作業,請問我是否可以改為把名字用選項方式輸入.....然後自動帶入"單位"這個欄位。
就像我"機票"裡面的名字....會自動帶入"單位"。

請問SQL是登入到ACCESS的意思嗎??  可是我用都不行~~
作者: c_c_lai    時間: 2013-12-27 14:49

回復  c_c_lai


您好:
謝謝您的機票作業,請問我是否可以改為把名字用選項方式輸入.....然後自動帶入 ...
JEAN 發表於 2013-12-27 13:32

當然可行,只要是 VBA 語法能處裡的,幾乎都有實現之可能。
另外我摘錄了 SQL 一詞的解釋:
結構化查詢語言(Structured Query Language,縮寫為SQL),一種程式語言,用於資料庫中的標準資料查詢語言,IBM公司最早使用在其開發的資料庫系統中。1986年10月,美國國家標準學會(ANSI)對SQL進行規範後,以此作為關聯式資料庫管理系統的標準語言(ANSI X3. 135-1986),1987年得到國際標準組織的支援下成為國際標準。不過各種通行的資料庫系統在其實踐過程中都對SQL規範作了某些編改和擴充。所以,實際上不同資料庫系統之間的SQL不能完全相互通用。
看完了這段說明,你應該便能了解到 SQL 它扮演的只是一般程式語言與資料庫間的一個介面語言。例如:
本程式碼裡的:
  1.     If ExcelData.Value = True Then
  2.         '  strSQL = "Select Distinct [簽證內容] From [簽證項目$] Order by [簽證內容]"
  3.         strSQL = "Select * From [簽證項目$] Order by [簽證內容]"
  4.     Else
  5.         '  strSQL = "Select Distinct 簽證內容 From 簽證項目 Order by 簽證內容"
  6.         strSQL = "Select * From 簽證項目 Order by 簽證內容"
  7.     End If
  8.    
  9.     '  closeRS
  10.     OpenDB
  11.   
  12.     rs.Open strSQL, cnn, 1, 3     '  adOpenKeyset, adLockOptimistic
  13.     If rs.RecordCount > 0 Then
  14.        .
  15.        .
  16.     End If
  17.       .
複製代碼
等與資料庫存取有關語法皆屬之。
作者: c_c_lai    時間: 2013-12-27 14:52

回復 26# JEAN
又、妳說用都不行?
此話怎說?
妳是指直接點選 "機票記錄明細表.mdb" 都無法進入到 Access 嗎?
作者: JEAN    時間: 2013-12-27 15:18

回復 28# c_c_lai


  您好:

我的意思是說:如果選擇SQL DATA方式 輸入資料....他的儲存明細表應該是在ACCESS吧....可是我開啟ACCESS明細表,必沒有我輸入的資料。

我會想用到ACCESS資料庫裡面是方便查詢,而且可以一直增加年度...譬如明年是2014年用完,我還可以在ACCESS明細表直接在加上2015年度就可以了。

這樣我就不用每年想要如何去做,而且我打算今年把表格搞定好把所有的缺點和優點整合出來。  以後也可以提供其他網友做使用。  

另外真的很感謝 c_c_lai 幫助才能完成表格。
作者: JEAN    時間: 2013-12-27 15:25

回復  c_c_lai


您好:
謝謝您的機票作業,請問我是否可以改為把名字用選項方式輸入.....然後自動帶入 ...
JEAN 發表於 2013-12-27 13:32


Private Sub DeleteData_Click()
    Dim nCode As Range, ret As Boolean
   
    With Sheets("data")
        Set nCode = .[B:B].Find(CallID.Text, , , 1)
        .Rows(Val(Mid(nCode.Address, 4))).EntireRow.Delete Shift:=xlUp
    End With
   
    ret = ExcelData.Value
    ExcelData.Value = False
   
    closeRS
    OpenDB
   
    strSQL = "DELETE FROM 機票記錄 WHERE 名字 = '" & CallID.Text & "'"
    cmd.CommandText = strSQL
   
    cmd.ActiveConnection = cnn
    cmd.Execute
    cnn.Close
   
    Confirm.Enabled = True
    ExcelData.Value = ret
    ResetData_Click
End Sub


請問是改這邊嗎?
作者: c_c_lai    時間: 2013-12-27 18:00

本帖最後由 c_c_lai 於 2013-12-27 18:03 編輯

回復 30# JEAN
這個是刪除資料錄的程式碼,
其內容是首先會刪除 Sheets("data") 內對應的被刪除資料,
接著、回頭又繼續去刪除儲存在 Access 的對應的資料,以保持兩者資料同步之故。
首先、妳幫我檢查一項動作:
A、  先勾選  "SQL Data"。然後在 "名字" 欄隨便輸入一名字、如: LuLu。
B、  輸完 LuLu 後,點選 "輸入確定" 按鈕,接著點選 "簽證內容1"、或者是
         "簽證內容2",然後瞧瞧裡面有沒有 "簽證選項",如果答案是 "Yes",
        那便要恭喜妳已找到(連結到) Access 資料庫了。
C、  最後妳才告訴我,妳實際想做的是甚麼?
作者: c_c_lai    時間: 2013-12-28 09:29

回復 26# JEAN
這裡,我已再加入一個新的工作表單 Sheets("機票登錄"),在此表單內
"機票登錄" 只單純處理資料新增、更新、刪除、重置等輸入作業,相關的
資料儲存作業則全交由 Access 機票記錄明細表.mdb 資料庫存取紀錄。
至於原 Sheets("data") 的紀錄則依然保留給工作表單 "機票作業",
作為雙向儲存範例之用。也就是說、工作表單 "機票登錄" 或許就是
妳的需求 (程式碼在其"機票登錄"本身工作表單內),妳可以將它與
"機票作業"內之程式碼相互比較,便可領會兩者之間處理上的差異,
進而可作為妳個人領域上之探討及研究。
[attach]17142[/attach]
[attach]17143[/attach]
作者: c_c_lai    時間: 2013-12-28 09:45

回復 26# JEAN
順序應先從 ThisWorkbook 的程式碼看起,然後再分別去瞭解
Sheets("機票作業")、以及 Sheets("機票登錄") 內的程式碼,
妳的重點可以先放在 ("機票登錄") 上。 理解後再行去體會
("機票作業")、如此妳的進展才會順利。最後才去瞭解 "模組"
裡如何處裡 ComboBox 內如何應用滑鼠滾軸的運作(Hook)、
以及如何釋放 (UnHook)。至於資料庫結構保持不變(Mdf、Accdb)。
最後、祝妳一帆風順!
[attach]17144[/attach]
作者: JEAN    時間: 2013-12-30 11:47

回復 33# c_c_lai


    您好:   我有你的方法...會出現這個錯誤~~~
      [attach]17155[/attach]

    您好:   我把檔案二個放在一起上傳給你~~請幫我看看..謝謝您
     [attach]17156[/attach]
作者: c_c_lai    時間: 2013-12-30 12:11

回復 34# JEAN
請問妳是要執行 MDF 資料庫、抑或是 ACCDB 資料庫?
因這兩種資料庫的驅動程式是不同的。
For Access MDF (2003):
  1.     .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  2.                           ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表.mdb" & ";"
複製代碼
For Access Accdb (2007、2010):
  1.     '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  2.     .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  3.                          ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表檔案.accdb" & ";"
複製代碼
當妳要使用 Accdb 資料庫時,妳程式碼的驅動程式便要啟動 For Access Accdb、反之亦然。
作者: JEAN    時間: 2013-12-30 12:25

回復 35# c_c_lai


    您好: 公司的電腦是用office 2007的    不過excel儲存的方式是2003   因為其他人是用2003的版本。
               不過 access 2007的  所以會用2007的方式儲存。

               請問你說的MDF資料庫是什麼??   沒有學過  .不真好意思
作者: c_c_lai    時間: 2013-12-30 13:52

回復  c_c_lai


    您好: 公司的電腦是用office 2007的    不過excel儲存的方式是2003   因為其他人是 ...
JEAN 發表於 2013-12-30 12:25

因為看到妳附件的檔案型態是 .accdb 才會有此一問。
MDF 是 Access 2003 的儲存檔的存檔類型,
ACCDB 則是 Access 2007、2010 的儲存檔的存檔類型。
在  Excel 2007、2010 的環境下可以存取 2003、及其 (2003) 以上版本
的資料庫。也就是說因為貴公司其他人均使用 2003 版本的 Office,
雖然妳的是 Office 2007, 在此條件情況下, 你便可以考量全體均
使用 mdf 的檔案。就不要使用 accdb 型態的資料庫檔案,否則其他
使用 2003 版本的同事都無法使用,太突出的表現也是不好的。
如此解說可否解決了妳的困惑? 此時驅動方式則要用:

  1.        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  2.                           ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表.mdb" & ";"
複製代碼

作者: c_c_lai    時間: 2013-12-30 14:18

回復 34# JEAN
原本語法在 2010 執行 OK,為避免困擾請修正如下:
[attach]17158[/attach]
作者: c_c_lai    時間: 2013-12-30 14:23

回復 36# JEAN
2014年機票記錄.xls 我稍稍修正如下:
[attach]17159[/attach]
[attach]17160[/attach]
[attach]17161[/attach]
作者: JEAN    時間: 2013-12-30 16:24

回復 39# c_c_lai


  您好:  謝謝您的幫忙~~~我有一點想放棄了,因為一直發生錯誤,問工程師,他也說不知道。
作者: c_c_lai    時間: 2013-12-30 17:45

本帖最後由 c_c_lai 於 2013-12-30 17:52 編輯

回復 40# JEAN
妳是使用 39# 的 "2014年機票記錄.xls" 執行還有問題嗎?
我還蠻好奇的,錯誤訊息為何?  資料庫目前妳是使用
"機票記錄明細表.mdb"、還是 "機票記錄明細表檔案.accdb"?
妳測試的電腦環境是在 Office 2003、亦或 Office 2007?
Excel 版本差異性真有那麼大嗎???
作者: JEAN    時間: 2014-1-2 09:36

回復 41# c_c_lai


你好:
我決定用以前的方法...還是用EXCEL方式。
不過我想請你幫我改一個東西...就是幫我把名字用成選項。
還有一個地方請你幫我改一下....就是行程日期  我想改為我只要輸入1/4日  他就會自動寫入2014/1/4日。
我把裡面的機票那個活頁簿刪除了。
謝謝您的幫忙~~
[attach]17178[/attach]
作者: goodnight    時間: 2014-1-4 20:29

回復 1# c_c_lai


    感謝您提供程式碼, 很實用啊, 我也可以參考這個程式碼, 日後考慮用在公司裡, 以excel來讀取 sql 資料
作者: imingho    時間: 2014-1-5 08:43

回復 34# JEAN

您看一下引用是否有問題,可以參考底下這一篇.

如何解決Excel VBA出現找不到專案或程式庫
http://discuz.bestdaylong.com/thread-30848-1-1.html
(出處: 彰化一整天的論壇)




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)