excel2access

Excel2Access

โมดูลคลาส Microsoft Excel ซึ่งช่วยให้คุณสามารถเชื่อมต่อและจัดการฐานข้อมูล Microsoft Access

คุณสามารถโต้ตอบกับฐานข้อมูล MS Access ได้โดยไม่ต้องมีความรู้ MS SQL คลาส Databaseutility สร้าง 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)