Jump to content
Sign in to follow this  

Insert string in access DB

Recommended Posts

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.

Share this post

Link to post
Share on other sites

Suggest that you review >this thread. I have been using this UDF to successfully read from and update an Access database.

Edit: Here's some code showing how to insert a record where $UserName is a string containing the value to be written --

; Try inserting a new record
$Result = _SQL_Execute(-1, "insert into usersettings (Name) VALUES ( '" & $UserName & "')" )
Edited by Danp2

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Page2PagePro
      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.
    • By Aelc
      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:
      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
    • By SnArF
      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?
    • By kcvinu
      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: 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
    • By Casey
      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 = 7 What 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.
  • Create New...