Connecting to a MS Access Database

[ss3vegeta]

What I want to do to me seems like it should be very simple, but I can't seem to find any good information on doing so. I've looked at some tutorials online, but I can't seem to find the basic information for actually connecting to a specific database and reading all the data from a specific table to an array. If that is to vague, this is what I'd like to learn how to do.

Open a specific MS Access database that is closed.

Pull all the information from a specific table into an array (a single record as an element)

Then close the database

I'll then use the array to do what I need. I don't need to do any database manipulation, I just need to read a single table from it. I even found an MSAccess.au3 file, but unfortunately it wasn't much help to me.

Any advice is greatly appreciated.

ss3

[ss3vegeta]

So I've kind of figured this out, but I'd like a little input if anyone is willing. Am I missing anything important? Should I include any kind of error checking. (The print function is just to test that the array gets populated correctly.)

Global $outputArrayRS [1][1]
Global $fieldCount
Global $recordCount

$tableName = "FormData"
$dataBase = "FormFill.mdb"
$query = "Select * From " & $tableName

ReadDBDataToArray($query, $dataBase)

printOutput()

Func ReadDBDataToArray($_sql, $_dbname)
    $adoCon = ObjCreate ("ADODB.Connection")
    $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
    $adoRs = ObjCreate ("ADODB.Recordset")
    $adoRs.CursorType = 1
    $adoRs.LockType = 3
    $adoRs.Open ($_sql, $adoCon)
    $fieldCount = $adoRs.Fields.Count
    $recordCount = $adoRs.RecordCount
    ReDim $outputArrayRS[$recordCount][$fieldCount]
    $outputArrayRS = $adoRs.GetRows()
    $adoCon.Close
EndFunc  

; just a tiny function to test if array is populated correctly.
Func printOutput()
    For $x = 0 to $recordCount -1 Step 1
        For $y = 0 to $fieldCount - 1 Step 1
            MsgBox(0, "test", $outputArrayRS[$x][$y])
        Next
    Next
EndFunc

I tried to pass the array, but it doesn't seem to like the ReDim

Thanks,

ss3

Edited May 29, 2009 by ss3vegeta

[TaPuZ]

Hi.

Have you got any positive solution?

I've got the same problem.

Best Regards,

T@PµZ

[GreenCan]

Hi.

Have you got any positive solution?

I've got the same problem.

Best Regards,

T@PµZ

This code workes fine.

GreenCan

expandcollapse popup

; this script will read data from an access database

; By Greencan

;Prerequisites:
; Using ODBC
; You need to create a system DSN (or file DSN) that points to the .mdb that you want to access.

#include <Array.au3>

Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

Opt("TrayIconDebug", 1)        ;0=no info, 1=debug line info
Opt("ExpandEnvStrings", 1)     ;0=don't expand, 1=do expand
Opt("ExpandVarStrings", 1)     ;0=don't expand, 1=do expand
Opt("GUIDataSeparatorChar","|") ;"|" is the default

; ODBC System DSN definition
$DSN="DSN=mdbquery";    MSAccess database as defined in ODBC

; call SQL
$out=getData($DSN)

; display array
; array element 0 will hold the row titles
_ArrayDisplay($out,"Result of query")

Exit
#FUNCTION# ==============================================================
Func getData($DSN)
    ; ODBC 
    $adoCon = ObjCreate ("ADODB.Connection")
    $adoCon.Open ($DSN)
    $adoRs = ObjCreate ("ADODB.Recordset") ; Create a Record Set to handles SQL Records - SELECT SQL
    $adoRs2 = ObjCreate ("ADODB.Recordset") ; Create a Record Set to handles SQL Records - UPDATE SQL
    
    ; create the SQL statement
    $adoSQL = "SELECT CODE, SHORT_EXPLANATION, LONG_EXPLANATION FROM example_table"

    $adoRs.CursorType = 2
    $adoRs.LockType = 3

    ; execute the SQL
    $adoRs.Open($adoSql, $adoCon)

    DIM $Result_Array[1][1]
    
    With $adoRs
        $dimension = .Fields.Count 
        ConsoleWrite($dimension & @cr)
        ReDim $Result_Array[1][$dimension]
        ; Column header
        $Title = ""
        For $i = 0 To .Fields.Count - 1
            $Title = $Title &  .Fields( $i ).Name  & @TAB
            $Result_Array[0][$i] = .Fields( $i ).Name ; set the array elements
        Next
        ConsoleWrite($Title & @CR & "----------------------------------" & @CR)

        ; loop through the records
        $element = 1
        If .RecordCount Then
            While Not .EOF
                $element = $element + 1
                ReDim $Result_Array[$element][$dimension]
                $Item = ""
                For $i = 0 To .Fields.Count - 1
                    $Item = $Item &  .Fields( $i ).Value & @TAB
                    $Result_Array[$element - 1][$i] = .Fields( $i ).Value ; set the array element
                Next
                ConsoleWrite($Item & @CR)

                .MoveNext
            WEnd
        EndIf
    EndWith
    
    $adoCon.Close ; close connection
    return $Result_Array
EndFunc
#FUNCTION# ==============================================================
; Com Error Handler
Func MyErrFunc()
    dim $oMyRet
    $HexNumber = Hex($oMyError.number, 8)
    $oMyRet[0] = $HexNumber
    $oMyRet[1] = StringStripWS($oMyError.description,3)
    ConsoleWrite("### COM Error !  Number: " & $HexNumber & "   ScriptLine: " & $oMyError.scriptline & "   Description:" & $oMyRet[1] & @LF)
    SetError(1); something to check for when this function returns
    Return
EndFunc ;==>MyErrFunc
#FUNCTION# ==============================================================

[TorZar]

I know this is 372 years old... but just needed to say, this is legit and thanks for it.. UDF's I'd seen acted buggy as shit. I'd written an entire application for work based on a much simpler method. Until I realized that it was only pulling back the last row as the result.. Couldn't find documentation anywhere.. saved me from scrapping the whole project..

Edited April 5, 2015 by TorZar

[dah]

I agree TorZar - this is bad ass!  Thank you GreenCan!!