excel2access

Excel2ACCESS

Un module de classe Microsoft Excel qui vous permet de connecter et de manipuler les bases de données Microsoft Access.

Vous pouvez interagir avec une base de données MS Access sans aucune connaissance MS SQL. La classe DatabaseUtility génère SQL à l'intérieur et l'exécute pour vous.

Exemple de sélection des données dans la base de données:

 ' 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

Vous pouvez également exécuter votre propre requête

 ExecuteSql(theSql As String) as ADODB.Recordset

Obtenez des noms de toutes les tables d'un fichier de base de données dans une collection:

 GetTableNames() As Collection

Insérer des enregistrements:

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

Insérez un seul enregistrement:

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

Sélectionnez plusieurs enregistrements:

 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

Obtenez plusieurs valeurs (ou toutes) dans un seul champ:

 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

Mettre à jour les enregistrements:

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

Sélectionnez une seule cellule dans un champ

 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

Obtenez la somme des cellules d'un champ numérique selon les critères

 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

Obtenez le compte des cellules d'un champ numérique selon les critères

 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

Supprimer les enregistrements

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