excel2access

Excel2access

Microsoft Access 데이터베이스를 연결하고 조작 할 수있는 Microsoft Excel 클래스 모듈.

MS SQL 지식없이 MS Access 데이터베이스와 상호 작용할 수 있습니다. 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)