Search the Community

Hi all, This is an UDF for working with Access(2007 above) databases. I would like to say thanks to @spudw2k for his wonderful ADODB udf. That is my inspiration. And i borrowed three functions from him. The difference of this UDF is that it is using SQL statements as parameters. So if you know the SQL, then you can easily work with this UDF. Functions in this UDF 1. _Start_Connection 2. _Close_Connection 3. _Create_Table 4. _Delete_Table 5. _Alter_Table 6. _Delete_FromTable 7. _Insert_Data - You can use this to update or delete data 8. _Get_Records This is an example. This example uses an access database named Test. It is packed with the zip file #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.14.0 Author: kcvinu Date : sep 2015 Script Function: Examples of Access_UDF Template AutoIt script. #ce ---------------------------------------------------------------------------- #include "Access_UDF.au3" #include <Array.au3> ; only for displaying data Example1() Func Example1() Local $MsgBox1 = MsgBox(4, "Access UDF Examples", " Starting Connection example, Ready ?") If $MsgBox1 = 6 Then ; We are starting a connection Local $Connection = _Start_Connection(@ScriptDir & "\Test.accdb;") ConsoleWrite($Connection & " Started" & @CRLF) ; Look for the function status Else Exit EndIf Local $MsgBox2 = MsgBox(4, "Access UDF Examples", " Starting Create Table example, Ready ?") If $MsgBox2 = 6 Then ; We need to create a new table Local $CreateTable = _Create_Table("CREATE TABLE TestTable(Column1 Text, Column2 Text);") ConsoleWrite($CreateTable & " Table Created" & @CRLF) Else Exit EndIf Local $MsgBox3 = MsgBox(4, "Access UDF Examples", " Starting Alter Table example, Ready ?") If $MsgBox3 = 6 Then ; We need to add a column to that table Local $AlterTable = _Alter_Table("ALTER TABLE TestTable ADD Column3 int;") ConsoleWrite($AlterTable & " Table Alterd" & @CRLF) Else Exit EndIf Local $MsgBox4 = MsgBox(4, "Access UDF Examples", " Starting Insert data example, Ready ?") If $MsgBox4 = 6 Then ; Insert some data Local $InsertData = _Insert_Data("INSERT INTO Table1([Col1], [Col2], [Col3]) VALUES ('AutoIt', 'Coding is Fun', 'AutoIt Rocks');") ConsoleWrite($InsertData & " Data inserted" & @CRLF) Else Exit EndIf Local $MsgBox5 = MsgBox(4, "Access UDF Examples", " Starting Update table example, Ready ?") If $MsgBox5 = 6 Then ; Let us update the table Local $UpdateData = _Insert_Data("UPDATE Table1 SET Col1 = 'Autoit Is Great', Col2 = 'Coding is Really Fun' WHERE Col3 = 'AutoIt Rocks';") ConsoleWrite($UpdateData & " Table Updated" & @CRLF) Else Exit EndIf Local $MsgBox6 = MsgBox(4, "Access UDF Examples", " Starting Get records example, Ready ?") If $MsgBox6 = 6 Then ; Now, collect some data Local $GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Rocks' ;") _ArrayDisplay($GetData) Else Exit EndIf Local $MsgBox7 = MsgBox(4, "Access UDF Examples", " Starting Delete from Table example, Ready ?") If $MsgBox7 = 6 Then ; Let us delete the whole data from that table, but not the table Local $DeleteAll = _Delete_FromTable("DELETE FROM Table1;") ConsoleWrite($DeleteAll & " All data deleted from Table" & @CRLF) Else Exit EndIf Local $MsgBox8 = MsgBox(4, "Access UDF Examples", " Starting Delete the entire table example, Ready ?") If $MsgBox8 = 6 Then ; Now, we are going to delete the entire table Local $DeleteTable = _Delete_Table("DROP TABLE TestTable;") ConsoleWrite($DeleteTable & " Table deleted" & @CRLF) Else Exit EndIf ; Last but not least, close the connection. _Close_Connection() ConsoleWrite("Examples Over...." & @CRLF) EndFunc ;==>Example1 Here is the files. Access UDF.rar Access UDF.zip

Excel VBA's IDE registers a Control-y as "cut this line of code". For those prone to Undo/Redo (Ctrl+Z/Ctrl+Y) you may find frustration when your code in the editor does not redo, but in fact clears your active line of code while killing redo history. Though not perfect, I keep this tool running in background on startup. The purpose is to allow Cltr+Y to act normally throughout Windows and Office and only interact *differently* with the "Microsoft Visual Basic for Applications" window that is active. If the Standard Menu bar exists, it'll try to click the ReDo (Blue Arrow to the right), else "Alt+e, r" keystrokes (less desired). Here's the code: Opt('MustDeclareVars', 1) Opt("WinTitleMatchMode", 1) HotKeySet("^y", "TriggerRedo") While 1 Sleep(10) WEnd Func TriggerRedo() ConsoleWrite("TriggerRedo()" & @CRLF) Local $title = "Microsoft Visual Basic for Applications - " Local $hWnd If WinExists($title) And WinActive($title) Then ;~ Parent Window Handle $hWnd = WinGetHandle($title) Local $aWindowPos = WinGetPos($hWnd) ;~ Control Bar Handle, Position and If Visible Local $sControlID = "[CLASS:MsoCommandBar; TEXT:Standard;]" Local $hStandardBar = ControlGetHandle($hWnd, "", $sControlID) Local $bIsVisible = ControlCommand($hWnd, "", $sControlID, "IsVisible") If $hStandardBar And $bIsVisible Then ConsoleWrite("Using Mouse Click." & @CRLF) ;~ Determine Redo button location on visible Control Bar Local $aBarPos = ControlGetPos($hWnd, "", $sControlID) Local $mX = $aWindowPos[0] + $aBarPos[0] + 217 + Int(23/2) Local $mY = $aWindowPos[1] + $aBarPos[1] + 27 + Int(22/2) MouseClick("Left", $mX, $mY, 1, 0) Else ConsoleWrite("Using VBA Send Keys." & @CRLF) $sControlID = "[CLASS:MsoCommandBar; TEXT:Menu Bar;]" Local $hMenuBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hMenuBar, "!e") ;~ Send("r") $sControlID = "[CLASS:MsoCommandBarPopup; TEXT:Edit;]" Local $hPopupBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hPopupBar, "r") EndIf Else ConsoleWrite("Using NATIVE Send Keys." & @CRLF) HotKeySet("^y") Send("^y") ;~ may cause "yyy..." when held HotKeySet("^y", "TriggerRedo") EndIf EndFunc ;==>TriggerRedo Hope this inspires someone.

Hey there! I'm gonna try to get access to an RichEdit Control in PSPad with Autoit which is scripted with Delphi. I wrote the commands out ouf the RichEdit.au3 UDF to can change them, because it always fails or crashs, when i call it. I Just want to set the Textcolor for the RichEdit. It seems to be a Problem with $EM_SETCHARFORMAT... (maybe protected?) I can use e.g. $EM_SETBKGNDCOLOR or other messages with no error. Sadly it doesn't exist a message constant to just set the textcolor... This is what i already tried: use $EM_GETCHARFORMAT but it crashs/doesn't work too. I saw the riched20.dll is linked to GDI+ but i couldn't make something work with it... (GetDc and so on but maybe there is another way?) Tried to set some other style on it (setWindowLong) I read the MSDN about $EM_SETCHARFORMAT up and down, but i can't find any other solution. The RichEdit should be version 2 ( error message point to riched20.dll ) Here is the PSPad that will be required for testing: https://autoit.de/wcf/attachment/87712-pspad4autoit3-v1-0-0-beta-without-setup-zip/ You need to start PSPad.exe first! ; = Info =========================================================================================== ; Title : RichEdit_Test ; -------------------------------------------------------------------------------------------------- ; ; Version (vnots) : 0.0.1 ; Date : 2020-05-13 ; Author(s) : Aelc ; ; ================================================================================================== #include <File.au3> #include <Misc.au3> #include <Date.au3> #include <String.au3> #include <GuiListBox.au3> #include <WinAPI.au3> #include <MsgBoxConstants.au3> #include <Color.au3> #include <GuiRichEdit.au3> #include <EditConstants.au3> AutoItSetOption("MustDeclareVars", 1) RichEdit_Test_Main() Func RichEdit_Test_Main() Local $hPSPad = Null Local $tLogCtrls Local $hLB_hwnd Local $hLB_ID $hPSPad = _WinAPI_GetForegroundWindow() If Not WinExists("[CLASS:TfPSPad]") Then MsgBox ( 48,"","PSPad not found." ) Exit EndIf Sleep(1000) $hPSPad = WinGetHandle("[CLASS:TfPSPad]") ;~ If $hPSPad = Null Then ;~ MsgBox($MB_SYSTEMMODAL + $MB_ICONWARNING, 'AutoIt3_CompilerRunner: - Error -', 'No handle found for PSPad!') ;~ Exit (2) ; 2 = PSPad-hwnd not found ;~ EndIf If _WinAPI_IsClassName($hPSPad, "TfPSPad") Then ; -------------------------------------------------------------------------- If _Singleton("PSPad->RichEdit_Test " & String($hPSPad), 1) = 0 Then MsgBox($MB_SYSTEMMODAL + $MB_ICONERROR, "RichEdit_Test: - Error -", _ '"RichEdit_Test" is already running') Exit EndIf ; -------------------------------------------------------------------------- $tLogCtrls = PSPad_GetLogControls($hPSPad, 'Log') $hLB_hwnd = $tLogCtrls.hWnd_ListBox $hLB_ID = $tLogCtrls.ID_ListBox If $hLB_hwnd > 0 Then _SendMessage($hLB_hwnd, 0xCF,False) ;Set $READONLY to False Local $tagCHARFORMAT_changeable = "struct;uint cbSize;long dwMask;long dwEffects;long yHeight;long yOffset;INT crTextColor;" & _ "byte bCharSet;byte bPitchAndFamily;wchar szFaceName[32];endstruct" Local $aColor[3] = [0xFF, 0x00, 0xFF] Local $nColor = _ColorSetCOLORREF($aColor) Local $tCharFormat = DllStructCreate($tagCHARFORMAT_changeable) DllStructSetData($tCharFormat, 1, DllStructGetSize($tCharFormat)) DllStructSetData($tCharFormat, 2, $CFM_COLOR) DllStructSetData($tCharFormat, 6, $nColor) MsgBox ( 64,"",_SendMessage($hLB_hwnd, $EM_SETCHARFORMAT, $SCF_SELECTION, $tCharFormat, 0, "wparam", "struct*")) Else ; Log-Fenster konnte nicht gefunden werden. MsgBox($MB_SYSTEMMODAL + $MB_ICONERROR, "AutoIt3_CompilerRunner: - Error -", _ "The log window of PSPad could not be found.") Exit EndIf Else EndIf EndFunc ;==>RichEdit_Test_Main ;~ Func _RichEditGetTextLength($hWnd) ;~ If Not _WinAPI_IsClassName($hWnd, $__g_sRTFClassName) Then Return SetError(101, 0, 0) ;~ Local $bChars = True ;~ Local $bExact = True ;~ Local $tGetTextLen = DllStructCreate($tagGETTEXTLENGTHEX) ;~ Local $iFlags = BitOR($GTL_USECRLF, ($bExact ? $GTL_PRECISE : $GTL_CLOSE)) ;~ $iFlags = BitOR($iFlags, ($bChars ? $GTL_DEFAULT : $GTL_NUMBYTES)) ;~ DllStructSetData($tGetTextLen, 1, $iFlags) ;~ DllStructSetData($tGetTextLen, 2, ($bChars ? $CP_ACP : $CP_UNICODE)) ;~ Local $iRet = _SendMessage($hWnd, $EM_GETTEXTLENGTHEX, $tGetTextLen, 0, 0, "struct*") ;~ Return $iRet ;~ EndFunc ;==>_RichEditGetTextLength ;~ Func _RichEditGetBKColor($hWnd) ;~ If Not _WinAPI_IsClassName($hWnd, $__g_sRTFClassName) Then Return SetError(101, 0, 0) ;~ Local $iBkColor = _SendMessage($hWnd, $EM_SETBKGNDCOLOR, False, 0) ;~ _SendMessage($hWnd, $EM_SETBKGNDCOLOR, False, $iBkColor) ;~ Return $iBkColor ;~ EndFunc ;==>_RichEditGetBKColor ;~ Func _RichEditSetBKColor($hWnd, $iBkColor) ;~ If Not _WinAPI_IsClassName($hWnd, $__g_sRTFClassName) Then Return SetError(101, 0, 0) ;~ _SendMessage($hWnd, $EM_SETBKGNDCOLOR, False, $iBkColor) ;~ Return $iBkColor ;~ EndFunc ;==>_RichEditSetBKColor Func PSPad_GetLogControls($_hPSPad, $_sSheetText = 'Log') Local $sStruct_LogCtrls = _ 'hwnd hWnd_PageControl;' & _ ; TPageControl 'int ID_PageControl;' & _ 'hwnd hWnd_TabSheet;' & _ ; TTabSheet 'int ID_TabSheet;' & _ 'hwnd hWnd_ListBox;' & _ ; TListBox 'int ID_ListBox;' Local $tResult = DllStructCreate($sStruct_LogCtrls) Local $aEnum Local $sSheet Local $i, $x $aEnum = _WinAPI_EnumChildWindows($_hPSPad, False) If IsArray($aEnum) Then For $i = 1 To $aEnum[0][0] If $aEnum[$i][1] = "TTabSheet" Then $sSheet = ControlGetText($_hPSPad, '', _WinAPI_GetDlgCtrlID($aEnum[$i][0])) If $sSheet = $_sSheetText Then ; TabSheet gefunden. $tResult.hWnd_TabSheet = $aEnum[$i][0] $tResult.ID_TabSheet = _WinAPI_GetDlgCtrlID($tResult.hWnd_TabSheet) $tResult.hWnd_PageControl = _WinAPI_GetParent($tResult.hWnd_TabSheet) $tResult.ID_PageControl = _WinAPI_GetDlgCtrlID($tResult.hWnd_PageControl) For $x = $i + 1 To $aEnum[0][0] If $aEnum[$x][1] = "TRichEdit" Then $tResult.hWnd_ListBox = $aEnum[$x][0] $tResult.ID_ListBox = _WinAPI_GetDlgCtrlID($tResult.hWnd_ListBox) Return $tResult ; Wenn gefunden, dann raus aus der Funktion. EndIf Next EndIf EndIf Next EndIf Return $tResult EndFunc ;==>PSPad_GetLogControls I don't have any ideas anymore But maybe someone else has? I would be happy for every idea Thanks in advance

Hello, I,m connecting to a access mdb, it works fine except when i use a select query and the requested data does not exists, then i get an error. ; Example Local $dbName = @ScriptDir & "\test.mdb" $dbCon = ObjCreate("ADODB.Connection") ; Create DataBase connection $dbCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbName) $sQuery = "select * from sourcefiles where Text='test'" $result = $dbCon.Execute($sQuery) MsgBox(0, "", $result.Fields( "ID" ).Value)If "test" exists in column Text then i get the ID number from column ID But if it doesn't exist i get an error: MsgBox(0, "", $result.Fields( "ID" ).Value) MsgBox(0, "", $result.Fields( "ID" )^ ERROR If the value "test"does not exist i just want $result to be 0 or "" Someone an idea?

The problem with not knowing the answer is not knowing the correct question to ask to find the answer. This is the exact predicament that I find myself at this moment so I will do my best to put order to some abstract thoughts. Given the function ControlGetHandle by definition retrieves the internal handle of a control. Let’s assume for the sake of argument that I have turned off UAC on both my test system and my problem system and that both systems are Windows 7 x86.That regardless of whether I have #RequireAdmin at the beginning of my script, that the results are the same on my test system. That the lines of code that I am having difficulty with are as follows $d = 0 Do $TestX = ControlFocus("Pxl", "Database has expired for", "Button1") $TestXa = ControlGetHandle("Pxl", "Database has expired for", "Button1") If $TestXa <> 0 Then ControlClick("Pxl", "Database has expired for", "Button1") Sleep(2000) EndIf $d = $d + 1 Until $d = 7What I am seeing happen is that on a test system, this works and I am able to interact with this button. However, if I take this same code to another system that has this client software installed, I cannot. I checked to see what was going on by doing the following: $Hope=WinExists("Pxl", "Database has expired for") MsgBox(0,"","If equals one window can be seen = " & $Hope) MsgBox(0,"",WinGetText("Pxl", "Database has expired for"))The result was a zero with no text being returned on the problem system. I then had the client software uninstalled and reinstalled making sure that elevated permissions were used. Following the reinstall, WinExists returned a 1, the text was returned, and the automation was successful. Sunshine, puppies and unicorns all day long. Two days later, on this same system, WinExists went back to being a zero for some unexplained reason. After doing an uninstall / reinstall routine again, WinExists still returns a zero. The fairy tale ended. Part of me wants to believe that it is an issue with the client installer but the other part fails to understand why it would just stop working or why on another system of the same OS persuasion, it runs flawlessly for 5,000 executions. To take this a step farther, if I run the Client software on the problem system as an administrator, WinExists returns a 1. So what would make a control that was previously available to a standard user session suddenly only want to give up its information when run as administrator? Why would one install work for 2 days and then break? Then have another install not work? So if I am making sense to this point, I have to ask, what I am missing? I have a reliable test where neither the script nor the client application need to be run as administrator yet I have this problem on other systems. Are there other factors that would create this behavior in AutoIt other than UAC? If I wanted to search the internet to find articles on how AutoIt retrieves the handles or better yet how the handles are exposed to the user session, what would I really search for? I have looked at process explorer and I see an access column if I turn on handles but it is my understanding that this is the access that was requested at the time the handle was created and is aimed at what the control is allowed to do but not who can access the information of the control. I am guessing that this possibility of access to the handle exists as it is the only conclusion that fits with what I am seeing, or am I wrong? This last question is what drove me to ask this question in the forum as I am having no success in understanding the concept of how this happens or what to search for. Any ideas? As a side note, here is what the info tool exposes about the button: >>>> Window <<<< Title: Pxl Class: #32770 Position: 477, 412 Size: 326, 161 Style: 0x94C801C5 ExStyle: 0x00010101 Handle: 0x00200880 >>>> Control <<<< Class: Button Instance: 1 ClassnameNN: Button1 Name: Advanced (Class): [CLASS:Button; INSTANCE:1] ID: 2 Text: OK Position: 226, 96 Size: 88, 26 ControlClick Coords: 22, 14 Style: 0x50030000 ExStyle: 0x00000004 Handle: 0x00130752 >>>> Mouse <<<< Position: 728, 547 Cursor ID: 0 Color: 0xDBDBDB >>>> StatusBar <<<< >>>> ToolsBar <<<< >>>> Visible Text <<<< OK Database has expired for >>>> Hidden Text <<<< P.S. Thank you Jon for getting my access restored. V/r Casey

Hi all. Have made some google search but couldn't found a correct answer. I'm maked a GUI with only 2 buttons (for now) START and STOP and, after pressed they will post the time of the action in a access DB. The script is made to, when I press start it keeps the time in a string and when I press stop it will insert the time of start and the time of the stop in a new record. My problem is to use the string in the insert command. I'm at home and don't have the complete code here but tomorrow I'll post. Thanks in advace.

Hello I need to open with autoit a MS access database! This database is opening with this shortcut: "C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE" /wrkgrp \\server\folder\sys.mdw \\server\folder\database.mdb /user MyUser /pwd MyPass How do I open this in Autoit. (using COM object) $adoCon = ObjCreate("ADODB.Connection") $adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname&"; SystemDB="&$wkname&"; " , $user, $pwd) If Not(IsObj($adoCon)) Then MsgBox(0,"error","error") Exit EndIf ; create recordset $adoRs = ObjCreate("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $sQuery = 'SELECT * FROM Address;' ; open query $adoRs.Open($sQuery, $adoCon) ... I got an error with the Query ! Need help Thanks in advance ! Cramaboule

I need to provide an example of a form showing query result from MS Access. Preferably Access 2007/2010 (accdb format) I have read several posts / UDFs but just can't seem to find the missing piece. This is what I have so far: ; register error handler for displaying errors Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") Local $title,$adoCon,$dbname,$adoRs, $_output ; create connection $adoCon = ObjCreate("ADODB.Connection") $dbname = @ScriptDir & "\" & "Nwind2007.accdb" ; this is for Access 2007 / 2010 according to Microsoft $adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname) ; create recordset $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 ; this query is copied from MS Access designer ; should return something $query = "SELECT * FROM Customers;" ; open query $adoRs.Open ($query, $adoCon) with $adoRs if .RecordCount then while not (.EOF) $_output = $_output & .Fields("Address").Value & @CRLF .MoveNext WEnd msgbox(0,"RC", .RecordCount & " records found..") endif EndWith $adoCon.Close MsgBox(0,"result",$_output) 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 For the demo purpose I use the NorthWind database, converted to Access2007. I did install the MSAccess2007 componens from MicroSoft. The NorthWind database is available here: http://ge.tt/35lU43M/v/0 All seems to work, but I never have a .RecordCount property, so the with/endwith is not executed. All I need is a simple forking example, then I can roll with the ball. <rant> Why must MS Access be SO difficult to work with when SQLite or MySQL are piece of cake ? </rant>

Note: This is completely unrelated to the program offered on TuCows and CNET, I named it without ever checking the title, and I may decide to change it at will. What Does it do? This program allows you to require a specific drive (USB preferably) to be connected to the computer to gain access while the program is running. Downloads: USBLock.zip (Includes Source code, Compiled version, and English .LANG file) UPDATES: (click "see post" to see notes on changes) Version 6: see post Version 5: see post Version 4: see post Version 3: see post Version 2: see post ----------------------------------- -How does it do it? 1a. On a normal run, the program will ask you for a drive letter to assign as a "Key Drive". 1b. A drive letter is assigned by command-line parameter. 1c. The "auto" parameter is used, and we skip to Step 4. 2. The Program will then generate a random keyfile, save a hash of the keyfile locally and save the actual keyfile on the root of the chosen/validated drive. 3. Waits until the keydrive is removed. 4. BlockInput is called 5. Then the program then checks repeatedly for the existence of the keyfile on all drives 6. The keyfile is validated, if it is valid, BlockInput is released and we return tp Step 2. 7. Assuming the keyfile is invalid, it is deleted and we return to step 5. Note: There is a special keyfile that can be saved on a drive that will shut down the program to allow emergency access. (if you edit the emergency text in the script, you will need to edit your own emergency keyfile) USBLock_Emergency_Keyfile.zip Requirements: WindowsConstants.au3 GUIConstants.au3 StaticConstants.au3 String.au3 Misc.au3 myToast.au3 - by me (given) MD5.au3 - which is either by: - Ward (Using CallWindowProc; I suggest this one.) - Frez Systems Ltd and SvenP (all AutoIt) - Other Credits: Monitor On/Off Example - MrCreatoR MD5 - SvenP or Ward ... let me know if you were missed for credit. EDIT: fixed the "steps" explanation 5-5-13 Edit: Re-added images to post