Sign in to follow this  
Followers 0
ss3vegeta

Connecting to a MS Access Database

6 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by ss3vegeta

Share this post


Link to post
Share on other sites

Hi.

Have you got any positive solution?

I've got the same problem.

Best Regards,

T@PµZ

Share this post


Link to post
Share on other sites

Hi.

Have you got any positive solution?

I've got the same problem.

Best Regards,

T@PµZ

This code workes fine.

GreenCan

; 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# ==============================================================

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by TorZar

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0