- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
3#
發表於 2018-1-2 06:09
| 只看該作者
本帖最後由 c_c_lai 於 2018-1-2 06:15 編輯
回復 1# tsengs
提供你參考：- Option Explicit
- ' ACCDB 版
- Option Base 1
- ' Dim cnn As Object ' New ADODB.Connection
- ' Dim cmd As Object ' New ADODB.Command
- ' Dim rs As Object ' New ADODB.Recordset
- Dim cnn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
- Dim strSQL As String
- Dim editMode As Boolean
- Public dic3 As Object ' 在 ThisWorkbook 引用，此處要宣告 Public
- ' 設定引用項目 - VBAProject -> 可引用的項目 (A)：
- ' Visual Basic For Applications
- ' Microsoft Excel 14.0 Object Library
- ' OLE Automation
- ' Microsoft Office 14.0 Object Library
- ' Microsoft Forms 2.0 Object Library
- ' Microsoft DAO 3.6 Object Library
- ' Microsoft ADO Ext 6.0 for DDL and Security
- ' Microsoft ActiveX Data Object 6.1 Library
- Sub OpenDB()
- Set cnn = New ADODB.Connection
- Set rs = New ADODB.Recordset
- Set cmd = New ADODB.Command
- ' ObjectStateEnum Values
- ' Returns a value describing if the connection is open or closed
- ' Specifies whether an object is open or closed, connecting to a data source,
- ' executing a command, or retrieving data.
- ' --------------------------------------------------------------------------
- ' Constant Value Description
- ' --------------------------------------------------------------------------
- ' adStateClosed 0 The object is closed
- ' adStateOpen 1 The object is open
- ' adStateConnecting 2 The object is connecting
- ' adStateExecuting 4 The object is executing a command
- ' adStateFetching 8 The rows of the object are being retrieved
- With cnn
- If .State = 1 Then .Close ' adStateOpen
-
- ' .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
- ' ThisWorkbook.Path & Application.PathSeparator & "記錄明細表.mdb" & ";"
- ' For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
- .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
- ThisWorkbook.Path & Application.PathSeparator & "記錄明細表檔案.accdb" & ";"
- .Open
- End With
- End Sub
- Sub closeRS()
- With rs
- If .State = 1 Then .Close ' adStateOpen
- ' The CursorLocation property is used to set or return the location of the cursor.
- ' This property can be set to one of the CursorLocationEnum constants listed in the following table.
- ' Enumeratio0n Value Description
- ' adUseNone 1 This value indicates no cursor location.
- ' This value is not supported by the MicrosoftR OLE DB Provider for AS/400 and VSAM.
- ' adUseServer 2 This value indicates that the data provider or driver-supplied cursor is used.
- ' adUseClient 3 This value indicates that a client-side cursor supplied by a local cursor library
- ' is to be used.
- ' adUseClientBatch 3 For backward compatibility, this value indicates that a client-side cursor supplied by
- ' a local cursor library is to be used.
- .CursorLocation = 3 ' adUseClient
- End With
- End Sub
|
|
