Jump to content
Sign in to follow this  
Grax

AutoIT and MS Access

Recommended Posts

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

Share this post


Link to post
Share on other sites
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


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites
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.

;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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
amokoura

Remember to check out COM errors.

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

Share this post


Link to post
Share on other sites
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 by dorit30

Share this post


Link to post
Share on other sites
amokoura

BTW you can always grow up.

Share this post


Link to post
Share on other sites
dorit30

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

Share this post


Link to post
Share on other sites
amokoura

Growing up is something else than a number

Share this post


Link to post
Share on other sites
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!)

Share this post


Link to post
Share on other sites
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)

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  

×