AutoIT to automate accdb files (MSACCESS)

[TheOne23]

Hi Guys and Experts,

May I ask your kind assistance to check where I am doing wrong on below scripts.

I tried 2 methods below.

1st method:

method 2:

[faustf]

use this udf

 

and  when you past a code use tag no foto

 

Edited May 7, 2020 by faustf

[TheOne23]

Hi faustf,

I actually tried to post in code format but I am blocked since I am using my corp laptop.

I will check on this. 

[TheOne23]

Hi Faustf,

It has error in the below line.

_ADO_Connection_OpenConString($oConnection, $sConnectionString)

If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

Error is 

 

"D:\Automations\Hackathon\Access UDF\Access UDF\ADO_EXAMPLE__PostgreSQL.au3" (27) : ==> Variable used without being declared.:
_ADO_Connection_OpenConString($oConnection, $sConnectionString)
_ADO_Connection_OpenConString(^ ERROR

[faustf]

adodb driver you have installed ?

[TheOne23]

Hi Faustf,

Can you suggest where I can download it please? 

Thank you.

[faustf]

try this

google  is our friends 

https://www.youtube.com/watch?v=ehVFtmhPwxs

look and  this 

https://www.youtube.com/watch?v=biSjA8ms_Wk

Edited May 7, 2020 by faustf

[TheOne23]

Hi Faustf,

I already installed adodb driver but still same error.

[faustf]

i did do this configuration  ?

use 32bit version or 64 ??

 

 

[TheOne23]

Hi faustf,

yes I did the configuration using 64 bit as my laptop is in 64 bit.

[faustf]

show  me the code

use tag

[faustf]

now  this  code  work

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=Microsoft Access db";    MSAccess database as defined in ODBC
;$DSN="DSN=Microsoft Access Driver (*.mdb, *.accdb)";    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 tabella1"
    $adoSQL = "SELECT * FROM tabella1"

    $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[4]

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

after install adodb

you must create a system DSN  and call Microsoft Access db (if you want run my code witout modify databese call Database11.accdb, and table call tabella1 )

after create a system DSN you must attach a database in system data source , cclick in configure , database box  click in selection  and select your database (in my case i select Database11.accdb)

 

[TheOne23]

Hi Faustf,

 

Thank you very much. This worked.