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
  • Like 1

Share this post

Link to post
Share on other sites

Thanks Danp2,

This solved the problem.

VALUES ( '" & $UserName & "')" )

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  

  • Similar Content

    • SnArF
      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?
    • kcvinu
      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
    • Casey
      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.
    • cramaboule
      By cramaboule
      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 !
    • Myicq
      By Myicq
      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.

      Why must MS Access be SO difficult to work with when SQLite or MySQL are piece of cake ?