返回列表 上一主題 發帖

如何把EXCEL資料輸入到Access

如何把EXCEL資料輸入到Access

各位大大我用EXCEL VB寫了程式後,不懂怎麼把Access當作EXCEL的DB,該如何將EXCEL資料自動拋到ACCESS
感恩
hello

回復 1# tsengs


    附上檔案
做該做的事是智慧,做不該做的事是愚癡。

TOP

本帖最後由 c_c_lai 於 2018-1-2 06:15 編輯

回復 1# tsengs
提供你參考:
  1. Option Explicit   
  2. ' ACCDB 版
  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 cnn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
  8. Dim strSQL As String
  9. Dim editMode As Boolean
  10. Public dic3 As Object   '  在 ThisWorkbook 引用,此處要宣告 Public

  11. '  設定引用項目 - VBAProject -> 可引用的項目 (A):
  12. '  Visual Basic For Applications
  13. '  Microsoft Excel 14.0 Object Library
  14. '  OLE Automation
  15. '  Microsoft Office 14.0 Object Library
  16. '  Microsoft Forms 2.0 Object Library
  17. '  Microsoft DAO 3.6 Object Library
  18. '  Microsoft ADO Ext 6.0 for DDL and Security
  19. '  Microsoft ActiveX Data Object 6.1 Library

  20. Sub OpenDB()
  21.     Set cnn = New ADODB.Connection            
  22.     Set rs = New ADODB.Recordset
  23.     Set cmd = New ADODB.Command
  24.     '  ObjectStateEnum Values
  25.     '  Returns a value describing if the connection is open or closed
  26.     '  Specifies whether an object is open or closed, connecting to a data source,
  27.     '  executing a command, or retrieving data.
  28.     '  --------------------------------------------------------------------------
  29.     '  Constant             Value     Description
  30.     '  --------------------------------------------------------------------------
  31.     '  adStateClosed          0       The object is closed
  32.     '  adStateOpen            1       The object is open
  33.     '  adStateConnecting      2       The object is connecting
  34.     '  adStateExecuting       4       The object is executing a command
  35.     '  adStateFetching        8       The rows of the object are being retrieved
  36.     With cnn
  37.         If .State = 1 Then .Close    '  adStateOpen
  38.         
  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.         .Open
  45.     End With
  46. End Sub

  47. Sub closeRS()
  48.     With rs
  49.         If .State = 1 Then .Close    '  adStateOpen
  50.         '  The CursorLocation property is used to set or return the location of the cursor.
  51.         '  This property can be set to one of the CursorLocationEnum constants listed in the following table.
  52.         '  Enumeratio0n     Value   Description
  53.         '  adUseNone          1     This value indicates no cursor location.
  54.         '                           This value is not supported by the MicrosoftR OLE DB Provider for AS/400 and VSAM.
  55.         '  adUseServer        2     This value indicates that the data provider or driver-supplied cursor is used.
  56.         '  adUseClient        3     This value indicates that a client-side cursor supplied by a local cursor library
  57.         '                           is to be used.
  58.         '  adUseClientBatch   3     For backward compatibility, this value indicates that a client-side cursor supplied by
  59.         '                           a local cursor library is to be used.
  60.         .CursorLocation = 3          '  adUseClient
  61.     End With
  62. End Sub
複製代碼
提問問題時,請同時將你的附件一併帶上,否則你的提問會石沉大海的。

TOP

經ADODB.Connection 再直接用SQL 控制DB

TOP

        靜思自在 : 真正的愛心,是照顧好自己的這顆心。
返回列表 上一主題