AutoIT and MS Access

[Grax]

Ok I'm fairly new to AutoIT and would like to write a program that will interact with an MS Access Database; which I've never done before in any language.

Can you please point me in the right direction? Not even sure where to begin and I haven't been able to find much information that's been helpful.

This is what I'm looking to do:

Looking to read information from an access database to populate a Combo Box, Input Box, etc.

Looking to write information entered back into an access database.

Looking to read information written to the access database to create reports.

Thanks in Advance!

Andrew

[GEOSoft]

Ok I'm fairly new to AutoIT and would like to write a program that will interact with an MS Access Database; which I've never done before in any language.

Can you please point me in the right direction? Not even sure where to begin and I haven't been able to find much information that's been helpful.

This is what I'm looking to do:

Looking to read information from an access database to populate a Combo Box, Input Box, etc.

Looking to write information entered back into an access database.

Looking to read information written to the access database to create reports.

Thanks in Advance!

Andrew

I have a UDF that might help you.

Follow the website link below and then, in the left menu, click Code >> My Extra UDFs >> Access.au3

There is a download link at the bottom of the page as well as a Copy to Clipboard link.

AutoIt Central

[Grax]

I have a UDF that might help you.

Follow the website link below and then, in the left menu, click Code >> My Extra UDFs >> Access.au3

There is a download link at the bottom of the page as well as a Copy to Clipboard link.

AutoIt Central

Nice... That looks like what I need!

Thank you!

[Grax]

I have a UDF that might help you.

Follow the website link below and then, in the left menu, click Code >> My Extra UDFs >> Access.au3

There is a download link at the bottom of the page as well as a Copy to Clipboard link.

AutoIt Central

Ok, so how do I use your UDF to querry the data base?

I want to pull all records from a table within a specific field and then populate that information into a combo box.

Thanks,

Andrew

[GEOSoft]

Ok, so how do I use your UDF to querry the data base?

I want to pull all records from a table within a specific field and then populate that information into a combo box.

Thanks,

Andrew

Without seeing the table layout and data, you probably want _accessQueryLike()

$Combo = GUICtrlCreateCombo("", 10,10,150,20)
$cData = ""
$sep = Opt("GUIDataSeparatorChar")
$tData = _accessQueryLike("C:\my.mdb","mytable", "field1","*")
If IsArray($tData) Then
   For $I = 1 To Ubound($tData)-1
      $cData &= $tData[$I] & $sep
   Next
   GUICtrlSetData($Combo, StringTrimRight($cData, StringLen($sep))
EndIf

[paz]

Ok I'm fairly new to AutoIT and would like to write a program that will interact with an MS Access Database; which I've never done before in any language.

I would suggest to learn a bit of SQL to do that. I personally do not use Access much, but I do use the MDB file format a lot with AutoIT, and I'm very happy with the results so far!

Can you please point me in the right direction? Not even sure where to begin and I haven't been able to find much information that's been helpful.

I don't know if you are like me, but I tend to like learning stuff the hard way

Here is some sample code that may get you started. It doesn't rely on any UDF - so you should be able to copy-paste this code, and by changing the variable "$FULL_MDB_FILE_NAME" to hold the file path of your MDB file, and alter the $SQL_CODE to select everything (*) from (name of your database table), you should see something poping on your screen.

Of course, there is next-to-zero functionality, it's really very basic stuff.

expandcollapse popup

;some constants I use for a bit of readability...
Const $adClipString = 2
Const $field_sep = '|'
Const $row_sep = @crlf
Const $null_char = ''

;Full file path of my MDB file
$FULL_MDB_FILE_NAME = @ScriptDir & "\MY_DATABASE.MDB"

;SQL Code, to get some data from my database
$SQL_CODE = "select * from TABLE_NAME"

;--------------------------------------------
; Establish a connexion - uncomment whatever type of data source you want to use.

;Create a database connexion object
$CONN = ObjCreate("ADODB.Connection")

;Opens a MDB file
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')

;Opens CSV as database files - works WAY better if a schema.ini file is present in the same folder, to define field types
;$CONN.Open('Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=' & $TEXT_FILES_FOLDER_NAME & '\;Extensions=asc,csv,tab,txt')

;Opens a Excel workbook like a database. Excel tabs are treated like tables, but has a special syntax:
;For instance, "Sheet1" need to be referenced as "[Sheet1$]" in SQL code.
;$CONN.Open('Driver={Microsoft Excel Driver (*.xls)};Dbq=' & $FULL_XLS_FILE_NAME & '; Readonly=false;')

;Opens a Oracle connexion. I use this daily with 10g, no issues so far.
;$CONN.Open('Driver={Microsoft ODBC for Oracle};Server=' & $DATABASE & ';Uid='& $USERNAME & ';Pwd=' & $PASSWORD & ';')

;--------------------------------------------
; Run some SQL code

;First, create a recordset object.
$RecordSet = ObjCreate("ADODB.Recordset")

;Query the database. After this, $RecordSet will have my data
$RecordSet.Open($SQL_CODE, $CONN)

;--------------------------------------------
; Do some post-processing on the data

;if we are already at "End of File" - EOF, it means the query did not return any results
if $RecordSet.EOF <> true then

;Build a 1D array with the recordset fields names, should I need them
    Dim $header[$RecordSet.Fields.Count]
    for $f = 0 to $RecordSet.Fields.Count-1
        $header[$f] = $RecordSet.Fields($f).Name
    Next

;This will returns a huge string, up to 256K of data (262144 chars)
;each field will be separated by "|", and each row separated by @crlf.
    $data = $RecordSet.GetString($adClipString,262144,$field_sep,$row_sep,$null_char)


;or if I prefer a 2D array with all of the recordset data, I can use this too:
;$data = $RecordSet.GetRows()

; Note that I could iterate through the recordset instead, like this:
; While $Recordset.EOF <> True
;;do some stuff with a row of data
;;(...)
;   
;;move to the next row of data
;   $RecordSet.MoveNext
; WEnd

    
;--------------------------------------------
; ...display the data

    MsgBox(0,"Number of fields: " & UBound($header),$data)
EndIf


;to add a record to the database...
;$CONN.Execute("insert into TABLE_NAME (field1, field2) values ('string', number)")

;Always good practice to close the objects when we are done
$RecordSet.Close
$CONN.Close

[dorit30]

Paz 10nx alot this is the way to understand and not to use (stupid) UDFs the crash and u dont know If its u or the function

It took me half day to understand but 10nx u done gr8 job

IF U HAVE MORE SAMPLES IT BE GR8

[amokoura]

Remember to check out COM errors.

BTW dorit30 your 1337-language makes me puke. Thanks a lot.

[dorit30]

Remember to check out COM errors.

BTW dorit30 your 1337-language makes me puke. Thanks a lot.

BTW u can always KMA and i got big 1 (uz google 2 figure what is KMA)

another lamer

Edited May 22, 2008 by dorit30

[amokoura]

BTW you can always grow up.

[dorit30]

How old u want me 2 B is 41 is old 4 u >?

[amokoura]

Growing up is something else than a number

[paz]

Paz 10nx alot this is the way to understand and not to use (stupid) UDFs the crash and u dont know If its u or the function

It took me half day to understand but 10nx u done gr8 job

IF U HAVE MORE SAMPLES IT BE GR8

Glad to see that the sample I wrote helped you :-)

...note that I'm not 100% in agreement with your comment that UDF are stupid - I learn a lot of stuff by looking at UDFs, and often use them when they fit my needs.

As for more samples... well, I'm not going to post all the bits of code I have written that deals with databases, that would be too long ;-)

(and most of them are crap or tests that doesn't work very well anyway)

If you are more specific, maybe I can post something.

(btw, no offence, but I agree with amokoura on that "elite" thing. Unless you type from your cellphone, I don't see the point of typing like that!)

[dorit30]

(btw, no offence, but I agree with amokoura on that "elite" thing. Unless you type from your cellphone, I don't see the point of typing like that!)

IF U GOT ONLY 1 HAND AND IN IT 3 FINGERS U WILL WRITES LIKE ME , no offence (I CAN INCLUDE PIC)