BryanVest Posted April 3, 2012 Posted April 3, 2012 I have a database built in MS Access 2007. I am trying to import the data from the GUI to access as a new record and cant seem to find the answer anywhere. Heres what I have. Case $Button2 $PalletID = GUICtrlRead($Input1) $Product = GUICtrlRead($Combo2) $Lot = GUICtrlRead($Input2) $Country = GUICtrlRead($Input3) $Expire = GUICtrlRead($Date1) $Quantity = GUICtrlRead($Input4) $dbname = "p:\Pallet Tag\Pallet Tracking_be.accdb" $tblname = "Naked Juice" $query = "INSERT INTO 'Naked Juice' (*) VALUES ('Test')" $adoCon = ObjCreate("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname) $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($query, $adoCon) I get the following error. I seem like I connect to the database alright but cant seem to add a record. Pallet Creator.au3 (65) : ==> The requested action with this object has failed.: $adoRs.Open ($query, $adoCon) $adoRs.Open ($query, $adoCon)^ ERROR
water Posted April 3, 2012 Posted April 3, 2012 Add a COM error handler so you get more information about what happens. Something like: Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") ; Your code goes here Func _ErrFunc($oError) 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) EndFunc ;==>_ErrFunc My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
BryanVest Posted April 3, 2012 Author Posted April 3, 2012 (edited) This is where I am at now. While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button2 Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") ; Your code goes here Func _ErrFunc($oError) 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) EndFunc ;==>_ErrFunc $PalletID = GUICtrlRead($Input1) $Product = GUICtrlRead($Combo2) $Lot = GUICtrlRead($Input2) $Country = GUICtrlRead($Input3) $Expire = GUICtrlRead($Date1) $Quantity = GUICtrlRead($Input4) $dbname = "p:Pallet TagPallet Tracking_be.accdb" $tblname = "Naked Juice" $adoCon = ObjCreate("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname) $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 3 $sql = "INSERT INTO NJ ([PalletID], [LOTNumber], [ProductID], [ExpirationDate], [Quantity], [Customer], [CreatedTime], [CreatedDate],[Country])" & " VALUES ('" & $PalletID & "','" & $Lot & "')" $adoRS.Execute $sql EndSwitch WEnd The error with the handler is -2147352570 and the description is Unknown Name. The error line is $adoRS.Execute $sql Edited April 3, 2012 by BryanVest
BrewManNH Posted April 3, 2012 Posted April 3, 2012 You can't have your function inside the While loop, move the Global line to before the While statement, and move the function to after the Wend statement. That's the first thing you'll need to do. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator
water Posted April 3, 2012 Posted April 3, 2012 Shouldn't it be$adoRS.Execute($sql) My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
BryanVest Posted April 3, 2012 Author Posted April 3, 2012 Same error though even with the suggested changes. expandcollapse popupGlobal $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button2 $PalletID = GUICtrlRead($Input1) $Product = GUICtrlRead($Combo2) $Lot = GUICtrlRead($Input2) $Country = GUICtrlRead($Input3) $Expire = GUICtrlRead($Date1) $Quantity = GUICtrlRead($Input4) $dbname = "p:Pallet TagPallet Tracking_be.accdb" $tblname = "NJ" $adoCon = ObjCreate("ADODB.Connection") $adoCon.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname) $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 3 $sql = "INSERT INTO NJ ([PalletID], [LOTNumber], [ProductID], [ExpirationDate], [Quantity], [Customer], [CreatedTime], [CreatedDate],[Country])" & " VALUES ('" & $PalletID & "','" & $Lot & "')" $adoRS.Execute ($sql) EndSwitch WEnd ; Your code goes here Func _ErrFunc($oError) 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) EndFunc ;==>_ErrFunc
water Posted April 3, 2012 Posted April 3, 2012 I don't think you need the [ and ].Check this. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
BryanVest Posted April 3, 2012 Author Posted April 3, 2012 I don't think you need the [ and ]. Check this. Yeah I had them without originally, but I added them when I saw the brackets on some other site. I still have the same error.
water Posted April 3, 2012 Posted April 3, 2012 For me it sounds like you want to insert a field into table "NJ" that isn't defined in this table. Did you check the database definition for table "NJ"? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
BryanVest Posted April 3, 2012 Author Posted April 3, 2012 I figured it out Changed $adoRS.Execute ($sql) To $adoCon.Execute ($sql) and everything imported perfectly. Thank you!
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now