麻辣家族討論版版 - Powered by Discuz! Board

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

複製代碼