Access Database and Mouse Click Automation

[RossIV]

I am working on a script to replace a page worth of instructions for a process I do daily.

What I am doing is opening an Access 2010 database, deleting a table, then switching to another (already open and logged in) program. In the second program, I am navigating through a few menus which then export to the aforementioned Access database (adding a table). Then I swap back to Access and print a report based on the table that was just inserted.

I have looked all over this forum and on Google for the past hour and found >this which (should) take care of opening the DB, but even when the AutoIt script is in the same folder as the .accdb file, it says the file doesn't exist.

Is this doable or am I wasting my time?

Thanks in advance!

[water]

Welcome to AutoIt and the forum!

I think it should be possible to do what you want to do.

Please post the code you use to open the database. We will have a look at it and - hopefully - can provide a solution.

[RossIV]

Thanks for the welcome!

I guess I can't attach files yet since I'm new, so I've put the two relevant files on PasteBin.

Script: http://pastebin.com/n47sCT5Q

Supporting File: http://pastebin.com/BQWu50HG

In the script right now, it's just reading the Access database and is supposed to spit out a list of tables and fields. The example that I'm using doesn't have a provision for dropping a table, so I was trying to figure out how to do that too.

Thanks!

[water]

I'm not sure the UDF can handle the "new" ACCDB extension. Can you manually create a MDB database and try to open it with the UDF?

When you run your script from SciTE do you get any additional error information in the output pane?

[water]

Your problem is described >here. The UDF doesn't seem to support the new ACCDB file format.

[RossIV]

I am not using anything special that requires .accdb so I converted it to a .mdb and the script runs just fine after I edited in the proper table name that was throwing some errors.

Left to do:

1. Drop the table

2. Navigate through the menus on the second application and export the report

3. Re-open Access and print the generated report

Ideas for those? In the code, it says that dropping a table is not on the to-do list, so I guess I"ll have to find some other way to do that.

As for navigating through the menus, from what I've seen it's likely going to be X,Y coordinates or if I'm lucky I can use the actual button name. The program that I need to click through is written in VB (I think VB6.) I can't post too many screenshots because of what the program contains, but I can post screenshots of AutoIT Info when run on the program and shots of the menus themselves.

Edit: Here's a screenshot of the menu of the program with AutoIt selecting the menu area. Button I want is highlighted.

Edit 2: PasteBin of AutoIt Info Summary http://pastebin.com/TDy6q0Jg

Edited February 2, 2014 by RossIV

[RossIV]

Updated code:  (Decided code tags would be easier than PasteBin)

expandcollapse popup

AutoItSetOption ("MustDeclareVars", 1)

#include "Access.au3"

MsgBox (0, "Welcome", "Click OK to start printing the new callback log.")
    OpenDatabase()
MsgBox (0, "Information", "Checking if Table exists.")
    CheckForTable()
MsgBox (0, "Information", "Now Opening Front Desk. DO NOT MOVE THE MOUSE OR TYPE ANYTHING!!!")
    OpenFrontDesk()

Func OpenDatabase()
    Local $o_DataBase = _AccessOpen(@ScriptDir & "\NewCallbackLog.mdb")
    If $o_DataBase = 0 Then
        MsgBox(0, "Information", "Database file can not be found :-" & @CR & @ScriptDir & "\NewCallbackLog.mdb")
        Return
    Else
        MsgBox(0, "Information", "Database file was opened :-" & @CR & @ScriptDir & "\NewCallbackLog.mdb")
    EndIf

    _AccessClose($o_DataBase)
 EndFunc   ;==>OpenDatabase

Func CheckForTable()
    Local $o_DataBase = _AccessOpen(@ScriptDir & "\NewCallbackLog.mdb")
    Local $O_TableObject

    If $o_DataBase = 0 Then
        MsgBox(0, "Information", "Database file can not be found :-" & @CR & @ScriptDir & "\NewCallbackLog.mdb")
        Return
    EndIf

    $O_TableObject = _AccessTableExists ($o_DataBase, "CREXPORT")
    If $O_TableObject = 0 Then
        MsgBox(0, "Information", "Table does not exist. Proceeding. ")
        Return
    Else
        MsgBox(0, "Information", "Table exists. Need to delete. ")
        DeleteTable()
    EndIf

    _AccessClose($o_DataBase)
EndFunc   ;==>CheckForTable

Func OpenFrontDesk()
   Local $frontDeskEXE = "C:\Program Files (x86)\HPMS\HPMS_FD.exe"
   Local $iPID = Run($frontDeskEXE, "", @SW_SHOWMAXIMIZED)
   Local $hWnd = WinWait("[CLASS:Front Desk]", "", 10)
   WinActivate($hWnd)
EndFunc

Func DeleteTable()
    Local $title,$adoCon,$dbname,$adoRs,$_output,$query
    ; create connection
    $adoCon = ObjCreate("ADODB.Connection")
    $dbname = @ScriptDir & "\" & "Nwind2007.accdb"
    Local $dbname = 'NewCallbackLog.mdb'
    $adoCon.Open('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' & $dbname & ';')
    $adoRs = ObjCreate ("ADODB.Recordset")
    ; this query is copied from MS Access designer
    ; should return something
    $query = "DROP TABLE CREXPORT;"
    ; open query
    $adoRs.Open ($query, $adoCon)
    $adoCon.Close

EndFunc ;==>DeleteTable


Func _ErrFunc($oError)
    if $oError <> "0" then
    MsgBox(0, "COM error", "err.number is: " & @TAB & $oError.number & @CRLF & _
    "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
    "err.description is: " & @TAB & $oError.description & @CRLF & _
    "err.source is: " & @TAB & $oError.source & @CRLF & _
    "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
    "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
    "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
    "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
    "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
    endif
EndFunc ;==>_ErrFunc

I have it working as follows:

It opens the DB, checks if the table exists or not and if so, deletes the DB. Then it opens the second application. Problem is - the delete doesn't work. No errors thrown at all - but the table never gets deleted. Followed >this post for executing the query to delete.

[water]

I've never used the Access UDF, so can't help with this.

What I know about databases is that you need to commit changes. Don't know if the UDF supports that.