Sign in to follow this  
Followers 0
SpookMeister

Working With An Access Database In Autoit

19 posts in this topic

#1 ·  Posted (edited)

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

; 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 by SpookMeister

[u]Helpful tips:[/u]If you want better answers to your questions, take the time to reproduce your issue in a small "stand alone" example script whenever possible. Also, make sure you tell us 1) what you tried, 2) what you expected to happen, and 3) what happened instead.[u]Useful links:[/u]BrettF's update to LxP's "How to AutoIt" pdfValuater's Autoit 1-2-3 Download page for the latest versions of Autoit and SciTE[quote]<glyph> For example - if you came in here asking "how do I use a jackhammer" we might ask "why do you need to use a jackhammer"<glyph> If the answer to the latter question is "to knock my grandmother's head off to let out the evil spirits that gave her cancer", then maybe the problem is actually unrelated to jackhammers[/quote]

Share this post


Link to post
Share on other sites



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

Help me, please!!!

:">

Share this post


Link to post
Share on other sites
Fox try using AutoIt Beta version, you can get it here....

AutoIt Scripts:NetPrinter - Network Printer UtilityRobocopyGUI - GUI interface for M$ robocopy command line

Share this post


Link to post
Share on other sites

Any further updates to this?

maybe turn it into a UDF and add some functions?

anyone interested in that?

Share this post


Link to post
Share on other sites

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

with a listview or something ?

thanks jpam

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

My Programs:AInstall - Create a standalone installer for your programUnit Converter - Converts Length, Area, Volume, Weight, Temperature and Pressure to different unitsBinary Clock - Hours, minutes and seconds have 10 columns each to display timeAutoIt Editor - Code Editor with Syntax Highlighting.Laserix Editor & Player - Create, Edit and Play Laserix LevelsLyric Syncer - Create and use Synchronised Lyrics.Connect 4 - 2 Player Connect 4 Game (Local or Online!, Formatted Chat!!)MD5, SHA-1, SHA-256, Tiger and Whirlpool Hash Finder - Dictionary and Brute Force FindCool Text Client - Create Rendered ImageMy UDF's:GUI Enhance - Enhance your GUIs visually.IDEA File Encryption - Encrypt and decrypt files easily! File Rename - Rename files easilyRC4 Text Encryption - Encrypt text using the RC4 AlgorithmPrime Number - Check if a number is primeString Remove - remove lots of strings at onceProgress Bar - made easySound UDF - Play, Pause, Resume, Seek and Stop.

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

anybody get this to work?

running office 2k

Edited by blitzkrg

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

Hi,

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

Best, Randall

That did the trick..

Thanks.......

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

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

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