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

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

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

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

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

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

  73. Private Sub ExcelData_Click()
  74.     '  MsgBox "Option is Excel Data "
  75. End Sub

  77. Private Sub SQLData_Click()
  78.     '  MsgBox "Option is SQL Data "
  79. End Sub

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

  106. Private Sub Confirm_Click()
  107.     Dim tblName As String, fld As String
  108.     Dim nCode As Range
  109.    
  110.     If CallID.Text = "" Then RecordExisted.Caption = "": Exit Sub
  111.    
  112.     RecordExisted.Caption = ""
  113.     DateTime.Value = ""
  114.     DataCear

  116.     DateTime.Enabled = True
  117.         
  118.     UpdateDropDowns
  119.         
  120.     If ExcelData.Value = False And SQLData.Value = False Then ExcelData.Value = True
  121.         
  122.     If ExcelData.Value = True Then      '  從 Sheets("data") 中擷取資料
  123.         tblName = "[data$]"
  124.         fld = "[名字]"

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

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

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

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

  12.     With cnn
  13.         If .State = 1 Then .Close    '  adStateOpen
  14.         
  15.         If ExcelData.Value = True Then
  16.             .ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
  17.                                 ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
  18.         Else
  19.             '  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  20.             '                    ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表.mdb" & ";"
  21.             '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
  22.             .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
  23.                               ThisWorkbook.Path & Application.PathSeparator & "機票記錄明細表檔案.accdb" & ";"
  24.         End If
  25.         .Open
  26.     End With
  27. 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 版本的同事都無法使用，太突出的表現也是不好的。
如此解說可否解決了妳的困惑？ 此時驅動方式則要用：

  2.        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
  3.                           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/)