excel2access

Excel2Access

وحدة فئة Microsoft Excel التي تتيح لك توصيل قواعد بيانات Microsoft Access ومعالجتها.

يمكنك التفاعل مع قاعدة بيانات الوصول إلى MS دون أي معرفة 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)