Working With An Access Database In Autoit

[SpookMeister]

I've been playing around with using autoit to interact with databases. I found several older posts about the subject and they were very helpfull. Specifically these:

http://www.autoitscript.com/forum/index.php?showtopic=20736

http://www.autoitscript.com/forum/index.php?showtopic=12281

I've combined info from those and added a few twists of my own.

Hope someone finds it usefull (feel free to add your own)

-Spook

P.S. You need to use the beta version to do this stuff

expandcollapse popup

; Modify the variables below as desired or applicable
; ******************************************************
$dbname = "c:\test.mdb"
$tblname = "table1"
;$fldname = "txt1"
;$format = "Text(50)"
;$data = "Blah Blah Blah"
;$fldname = "num1"
;$format = "Number"
;$data = 99
$fldname = "date1"
$format = "Date"
$data = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)

; Un-comment the action you want to do
; ******************************************************
;_CreateDB($dbname)
;_CreateTBL($dbname, $tblname)
;_CreateFLD($dbname, $tblname, $fldname, $format)

; Example of how to add data to a single field of a table
; ******************************************************
;_AddData($dbname, $tblname, $fldname, $data)

; Example of how to list all of the Fields in a particular table
; ******************************************************
;$str = _FieldNames($dbname, $tblname)
;MsgBox(0, "Fields in " & $tblname, $str)

; Example of how to read the info from all records in one field of a table as a string
; ******************************************************
;$query = "SELECT * FROM " & $tblname
;$strData = _ReadOneFld($query, $dbname, $fldname)
;MsgBox(0, $fldname, $strData)

; Example of how to get entire rows of pattern matched data
; This returns a two dimensional array where [0][0] holds the number of matching
; records found, [0][1] holds the first field name [0][2] holds the second etc.
; [1][1] is the data from the first record - first field
; [2][3] is the data from the 2nd record - 3rd field
; ******************************************************
;$query = "SELECT * FROM " & $tblname & " WHERE " & $fldname & " = 99"
;$rows = _RecordSearch($dbname, $query)
;_2dArrayDisp($rows)

; Example of how to add an entire redord into a table at one time
; For this example:
; First make sure you have a database created including a table
; and also that the table has the fields that you want to use.
; Then create a 2 dimensional array populated much like the one above
; ******************************************************

;Dim $row[2][4]
;$row[0][0] = UBound($row, 1) - 1
;$row[0][1] = "num1"
;$row[1][1] = 251
;$row[0][2] = "txt1"; match the field name
;$row[1][2] = "Denver, CO"; with the data you are going to use
;$row[0][3] = "date1"
;$row[1][3] = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)
;_AddEntireRecord($dbname, $tblname, $row)

; this will show the entire table
;$query = "SELECT * FROM " & $tblname
;$rows = _RecordSearch($dbname, $query)
;_2dArrayDisp($rows)

Func _CreateDB($dbname)
    $newMdb = ObjCreate ("ADOX.Catalog")
    $newMdb.Create ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
    $newmdb.ActiveConnection.Close
EndFunc  ;==>_CreateDB

Func _CreateTBL($dbname, $tblname)
    $addtbl = ObjCreate ("ADODB.Connection")
    $addTbl.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
    $addTbl.Execute ("CREATE TABLE " & $tblname)
    $addtbl.Close
EndFunc  ;==>_CreateTBL

Func _CreateFLD($dbname, $tblname, $fldname, $format)
    $addfld = ObjCreate ("ADODB.Connection")
    $addfld.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
    $addfld.Execute ("ALTER TABLE " & $tblname & " ADD " & $fldname & " " & $format)
    $addfld.Close
EndFunc  ;==>_CreateFLD

Func _AddData($dbname, $tblname, $fldname, $value)
    $adoCon = ObjCreate ("ADODB.Connection")
    $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname & ";")
    $adoRs = ObjCreate ("ADODB.Recordset")
    $adoRs.CursorType = 2
    $adoRs.LockType = 3
    $adoRs.Open ("SELECT * FROM " & $tblname, $adoCon)
    $adoRs.AddNew
    $adoRs.Fields ($fldname).Value = $value
    $adoRs.Update
    $adoRs.Close
    $adoCon.Close
EndFunc  ;==>_AddData

Func _FieldNames($dbname, $tblname)
    $adoCon = ObjCreate ("ADODB.Connection")
    $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
    $adoRs = ObjCreate ("ADODB.Recordset")
    $adoRs.Open ("SELECT * FROM " & $tblname, $adoCon)
    $name = ""
    For $i = 1 To $adoRs.Fields.Count
        $name = $name & $adoRs.Fields ($i - 1).Name & @CRLF
    Next
    Return $name
EndFunc  ;==>_FieldNames

Func _ReadOneFld($_sql, $_dbname, $_field)
    Dim $_output
    $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)
    
    With $adoRs
        If .RecordCount Then
            While Not .EOF
                $_output = $_output & .Fields ($_field).Value & @CRLF
                .MoveNext
            WEnd
        EndIf
    EndWith
    $adoCon.Close
    Return $_output
EndFunc  ;==>_ReadOneFld

Func _RecordSearch($_dbname, $_query)
    $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 ($_query, $adoCon)
    
    With $adoRs
        Dim $_output[.Fields.Count + 1][.RecordCount + 1]
        $_output[0][0] = .RecordCount
        For $i = 1 To .Fields.Count
            $_output[$i][0] = .Fields ($i - 1).Name
        Next
        If $adoRs.RecordCount Then
            $z = 0
            While Not .EOF
                $z = $z + 1
                For $x = 1 To .Fields.Count
                    $_output[$x][$z] = .Fields ($x - 1).Value
                Next
                .MoveNext
            WEnd
        EndIf
    EndWith
    $adoCon.Close
    Return $_output
EndFunc  ;==>_RecordSearch

Func _AddEntireRecord($_dbname, $_tblname, $_array)
    $adoCon = ObjCreate ("ADODB.Connection")
    $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname & ";")
    $adoRs = ObjCreate ("ADODB.Recordset")
    $adoRs.CursorType = 2
    $adoRs.LockType = 3
    $adoRs.Open ("SELECT * FROM " & $tblname, $adoCon)
    $adoRs.AddNew
    $records = $_array[0][0]
    For $i = 1 To $records
        For $x = 1 To UBound($_array,2) - 1
            $adoRs.Fields ($_array[0][$x]).Value = $_array[1][$x]
        Next
    Next
    $adoRs.Update
    $adoRs.Close
    $adoCon.Close
EndFunc  ;==>_AddEntireRecord

Func _2dArrayDisp($array)
    $report = "Number of records matching = " & $array[0][0] & @CRLF
    For $x = 1 To UBound($array, 2) - 1
        For $i = 1 To UBound($array, 1) - 1
            $report = $report & $array[$i][$x] & "|"
        Next
        $report = StringTrimRight($report, 1) & @CRLF
    Next
    MsgBox(0, "Display", $report)
EndFunc  ;==>_2dArrayDisp

Edited April 12, 2006 by SpookMeister

[Fox]

Hi, i tray to use u script, but i have a problem: autoit told me "Unknown function name - ObjCreate".

Help me, please!!!

:">

[Danny35d]

Fox try using AutoIt Beta version, you can get it here....

[blitzkrg]

Any further updates to this?

maybe turn it into a UDF and add some functions?

anyone interested in that?

[jpam]

can you give use a example how to implement this in a gui

with a listview or something ?

thanks jpam

[RazerM]

you should make this a udf definetely. Very good work

Edit: doesn't work, all the lines with "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" cause an error, maybe i have a different version?

I have Microsoft Office 2003

Edited May 9, 2006 by RazerM

[blitzkrg]

you should make this a udf definetely. Very good work

Edit: doesn't work, all the lines with "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" cause an error, maybe i have a different version?

I have Microsoft Office 2003

I'm having trouble getting this working

$query = "SELECT * FROM " & $tblname & " WHERE " & $fldname & " = 99"

(obviously i changed 99 to whatever i'm searching for)

$query = "SELECT * FROM " & $tblname

this works though..

[blitzkrg]

anybody get this to work?

running office 2k

Edited May 11, 2006 by blitzkrg

[randallc]

Hi,

I had to give up on Jet; don't know why....?

[although your query might not have had a "99" in the table?... don't know....]

This works for me; [need your own DB file, table, pwd, usrname]

If password,

change;

Func _AccessConnect($dbname, $tblname,byref $adoCon,byref $adoRs,$adoMDB=1,$USRName="",$PWD="")

removed

Best, Randall

{EDIT - attachment removed; see next post}

Edited May 12, 2006 by randallc

[randallc]

Hi,

1. I have all but the add records at once func working with Jet 4.0 now (the other driver won't create database, or I don't know the syntax)

*****You will need to use the "DropTable" in line 17 after the first time you run it

2. This is not really easy to use except for the simplests SQL tasks unless we know the enum Table commands, find if they exist etc.etc

Anyone...?

Best, randall

[blitzkrg]

Hi,

1. I have all but the add records at once func working with Jet 4.0 now (the other driver won't create database, or I don't know the syntax)

*****You will need to use the "DropTable" in line 17 after the first time you run it

2. This is not really easy to use except for the simplests SQL tasks unless we know the enum Table commands, find if they exist etc.etc

Anyone...?

Best, randall

cant get this line to work

$s_query = "SELECT * FROM " & $s_tblname & " WHERE " & $s_fldname & " = OPEN"

(OPEN was what i was searching for)

[randallc]

Hi,

If you put the text "OPEN" in a text field, you will need quotes perhaps?

$query = "SELECT * FROM " & $s_tblname & " WHERE " & $fldname0 & " = 'OPEN'"

Best, Randall

[randallc]

can you give use a example how to implement this in a gui

with a listview or something ?

thanks jpam

here's a simple GUI for display and edit; using my old Array2D included already.

[functions renamed a little]

[now tests for "tableExists", so no need to edit out the "droptable etc "]

Randall

Edited May 14, 2006 by randallc

[blitzkrg]

Hi,

If you put the text "OPEN" in a text field, you will need quotes perhaps?

Best, Randall

That did the trick..

Thanks.......

[blitzkrg]

@randallc

how do you specify a row with the _AddData function?

right now it seems to be just adding it to the end of the table

thanks

[randallc]

Hi,

You are corect; the SQL functions in this need revising; you could write your own "Insert" function looking at @cdkid's "MySQL.au3"

_AddRecord($oConnectionObj, $sTable, $vRow, $vValue = "")

or [simpler, but less refined and accurate] looking at similar commands in "SQLiteExe.au3"

Func _SQL_InsertAddtoTable(byref $sDB, $sQueryTable,$s_ValuesOrSelectLine,$sHeader,$i_Execute=1,$i_Strings=1)

Local $sOutput,$sInput ;$sHeader,

if StringInStr($s_ValuesOrSelectLine,"SELECT") then

$sInput &= "INSERT INTO "&$sQueryTable&" "& $s_ValuesOrSelectLine & " " ; no ";" for select option; already in?

Else

if $i_Strings then $s_ValuesOrSelectLine=StringReplace($s_ValuesOrSelectLine,",","','")

$sInput &= "INSERT INTO "&$sQueryTable&"( "&$sHeader&") VALUES('" & $s_ValuesOrSelectLine & "');"

EndIf

if $i_Execute then

_SQL_BeginAndCommit( $sDB, $sInput)

return ""

Else

return $sInput

EndIf

EndFunc ;==>_SQL_InsertAddtoTable

Func _SQL_PrepareToInsert($sQueryTable,$s_ValuesLine,$sHeader)

if not StringInStr($s_ValuesLine,"','") then $s_ValuesLine=StringReplace($s_ValuesLine,",","','")

$sInput = "INSERT INTO "&$sQueryTable&"( "&$sHeader&") VALUES('" & $s_ValuesLine & "');"

return $sInput

EndFunc ;==>_SQL_PrepareToInsert

I think the author here [@SpookMeister] has just shown the basics to show it is possible rather than making a fully-fledged UDF; perhaps PM him too?

Best, Randall

Edited May 16, 2006 by randallc

[auratus]

Could someone please help me with why this is not working? I am using the Beta version of AutoIT.

$dbname = "auratusDB.mdb"
$tblname = "cards"
$fldname = "price"
    
$query = "SELECT * FROM " & $tblname & " WHERE " & $fldname & " = " & $userName
$rows = _RecordSearch($dbname, $query)

The error, "The requested action with this object has failed". comes from the _RecordSearch function on the line that reads

$adoRs.Open($_query, $adoCon)

Thanks for any help you can provide.

EDIT: Oh, well, I finally figured it out through trial and error. Here is my solution

$query = "SELECT * FROM " & $s_tblname & " WHERE " & $fldname1 & " = '" & $userName & "'"

Edited May 21, 2006 by auratus

[KenNichols]

Thank You!

[JRowe]

Congrats, you resurrected a 1088 day old thread.