MS Access Toolbars

[Icarii]

Here is my task:

1. download ftp file (once a week to update price guide in database) - No problem got that.

2. Extract file to specific location - No Problem, got that

3. Backup Database - Have not attempted yet, should be easy

4. Open Access in Exclusive Mode - No Problem, got that

5. Select Navigation Bar and choose "Query" - Have not figured it out yet

6. Run 2 different Queries to update table - Have not figured it out yet

7. Run Compact and Repair on database after update - have not figured it out yet

I have searched and searched and searched the forum looking for a good solution. Most of what I have found is for accessing a MS Access database without using Access. I tried to use _Lib_PopupGetHwnd() to get the handle of the toolbar, that didn't work, I tried to use controlgethandle but that didn't work either. Does anybody have any suggestion so I now which way would be better and maybe a nudge in the right direction for using it. Any Help would be great.

This is the code I used trying to get the handles of the navigation bar, it opens Access and clicks on the navigation toolbar, using the recorder, than i added the rest trying to get the handles so I can click them using Auto3lib. But no luck.

#include <A3LMenu.au3>

Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",4)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
WinWait("Program Manager","")
If Not WinActive("Program Manager","") Then WinActivate("Program Manager","")
WinWaitActive("Program Manager","")
MouseMove(42,362)
MouseDown("left")
MouseUp("left")
MouseClick("left",42,362,2)
Send("{SHIFTDOWN}")
WinWait("Microsoft Access - Smart : Database (Access 2002 - 2003 file format)","")
If Not WinActive("Microsoft Access - Smart : Database (Access 2002 - 2003 file format)","") Then WinActivate("Microsoft Access - Smart : Database (Access 2002 - 2003 file format)","")
WinWaitActive("Microsoft Access - Smart : Database (Access 2002 - 2003 file format)","")
Send("{SHIFTUP}")
MouseMove(119,161)
MouseDown("right")
MouseUp("right")
$hMenu = _Lib_PopupGetHwnd()
for $iI = 0 to _Menu_GetItemCount($hMenu) - 1
  ConsoleWrite(_Menu_GetItemText($hMenu, $iI) & @CR)
next

[Zedna]

You can do it with GUI manipulation

but more clever is direct access to Access

Look

here

here

here

here

[daashag]

I need to do something similar. Seeing I have limited programming skills, I found using send keys works pretty well for manipulating the gui. For instance the following code will open a database in access and run a query.

Run(@ComSpec & " /c " & '\\Cypress\Database\OldCopiesofDatabses\scrapbookmatesdb1.mdb', "", @SW_HIDE)
Sleep(500)
Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",4)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
WinWait("Microsoft Access","")
If Not WinActive("Microsoft Access","") Then WinActivate("Microsoft Access","")
WinWaitActive("Microsoft Access","")
;Navigate to Objects Panel
Send("{TAB}m{ENTER}")
;Make sure Querries is selected
Send("{TAB}q{ENTER}")
;Run specifide query
Send("RetailerSalesSumm{ENTER}")

I know it's not sexy but it works.

[Icarii]

Thanks for the reply.

Here is my problem:

I got the script working by taking the backend file of the database, creating a button that runs a macro (the button is on a form that opens by default, and is the only button), send enter to start the macro, and a few other keys to allow the queries to run. Works great when I manually run it, but fails when ran through task scheduler and windows is lock. I know it fails (I know now any ways) because you can not send WinWaitActive or the Send function when windows is locked.

My solution possibilities:

1)have the script check if windows is locked (not sure how to do this), if locked unlock and run the script, than lock windows

2)send the command using controlclick or something similiar (have not been able to figure out a way to do this through access)

3) use one of the above examples (the queries are deleting 175000 entries and adding 175000 entries, to update the part table, not sure using one of the above examples is feasiable)

I already have a macro is Access to run the already built queries and I would rather use Access to run them since it is editing alot of entries. So solutions 1 or 2 looks like the best way to go. Not sure if 2 is even possible.

So, I am going to persue solution one, but how would I check to see if the computer is locked. Sending cntl+alt+del to unlock and then send user info is no problem. Just not sure on how to check and see if the computer is locked.

Any help would be great. Thanks

Icarii

P.S. My speling is horable, sorry.

[herewasplato]

...7. Run Compact and Repair on database after update - have not figured it out yet

I'm not sure if this is what you are after, but I always set up MS Access to compact on close. It keeps that database file from needlessly growing. Go to Tools > Options > General tab and check "Compact on close".