excel2access

excel2access

Microsoft Excel类模块，可让您连接和操纵Microsoft Access数据库。

您可以在没有任何MS SQL知识的情况下与MS Access数据库进行交互。数据库类别在其中生成SQL并为您执行。

从数据库中选择数据的示例：

 ' Let's say you have a MS Access database which has a table called "TestTable":

' ID  text_field  binary_field  number_field
' 1   text-1      Yes           10
' 2   text-2      No            20
' 3   text-3      Yes           30
' 4   text-4      No            40
' 5   text-5      Yes           50


Dim db As New DatabaseUtility
Dim criteriaFields As New Collection, criteriaValues As New Collection, _
        selectionFields As New Collection
Dim results As New ADODB.Recordset
Dim dbName as String, dbPath as String

dbName = "NameOfTheMsAccessDatabase.accdb"
dbPath = "/Path/to/MS/Access/Database/"

' Establish connection with database
Call db.ConnectToDB(dbName, dbPath)

' Enter the fields you want to select in a collection
selectionFields.Add "text_field"
selectionFields.Add "binary_field"
selectionFields.Add "number_field"

' Enter criterias for your query in a collection
criteriaFields.Add "binary_field"
operators.Add "="
criteriaValues.Add "No"
    
criteriaFields.Add "number_field"
operators.Add ">"
criteriaValues.Add "20"

' Get the results in a ADODB.Recordset object
Set results = db.SelectRecords("TestTable", selectionFields, operators, criteriaFields, criteriaValues)

Call db.disconnectFromDB
Set db = Nothing

' results will have
' ID  text_field  binary_field  number_field
' 3   text-3      Yes           30
' 5   text-5      Yes           50

您也可以执行自己的查询

 ExecuteSql(theSql As String) as ADODB.Recordset

在集合中的数据库文件中获取所有表的名称：

 GetTableNames() As Collection

插入记录：

 InsertRecord(theTable As String, theSetFields As Collection, theSetValues As Collection)

插入一个记录：

 InsertRecord(theTable As String, theSetFields As Collection, theSetValues As Collection)

选择多个记录：

 SelectRecords(theTable As String, _
    Optional theSelectionFields As Collection, _
    Optional theCriteriaFields As Collection, Optional theCriteriaValues As Collection, _
    Optional theOperators As Collection, _
    Optional theDistinct As Boolean = False, _
    Optional theLimitBy As Double = 0, _
    Optional theOrderBy As String = "", Optional theAsc As Boolean = False) As ADODB.Recordset

在一个字段中获取多个（或所有）值：

 SelectField(theTable As String, _
    theSelectionField As String, _
    Optional theCriteriaFields As Collection, Optional theCriteriaValues As Collection, _
    Optional theOperators As Collection, _
    Optional theDistinct As Boolean = False, _
    Optional theLimitBy As Double = 0, _
    Optional theOrderBy As String = "", Optional theAsc As Boolean = False, _
    Optional isBlankIncluded As Boolean = True) As Collection

更新记录：

 UpdateRecords(theTable As String, _
    theSetFields As Collection, _
    theSetValues As Collection, _
    theCriteriaFields As Collection, _
    theCriteriaValues As Collection, _
    Optional theOperators As Collection)

在字段中选择一个单元格

 SelectFieldCell(theTable As String, _
    theSelectionField As String, _
    theCriteriaFields As Collection, _
    theCriteriaValues As Collection, _
    Optional theOperators As Collection, _
    Optional theOrderBy As String = "", _
    Optional theAscending As Boolean) As Variant

根据标准获得数字字段的单元格的总和

 SelectFieldSum(theTable As String, _
    theSelectionField As String, _
    theCriteriaFields As Collection, _
    theCriteriaValues As Collection, _
    Optional theOperators As Collection, _
    Optional theLimitBy As Double = 0, _
    Optional theOrderByFieldName As String = "", _
    Optional theAscending As Boolean) As Double

根据标准获取数字字段的单元格

 SelectFieldCount(theTable As String, _
    theSelectionField As String, _
    theCriteriaFields As Collection, _
    theCriteriaValues As Collection, _
    Optional theOperators As Collection, _
    Optional theLimitBy As Double = 0, _
    Optional theOrderByFieldName As String = "", _
    Optional theAscending As Boolean) As Double

删除记录

 DeleteRecords(theTable As String, _
    theCriteriaFields As Collection, _
    theCriteriaValues As Collection, _
    Optional theOperators As Collection)