motormad Posted February 21, 2011 Share Posted February 21, 2011 $dbname = "C:\TRA\TEST_FOR_AUTOIT.mdb" $tblname = "Producten" $dbname = "C:\TRA\TRA_BACKEND.mdb" $tblname = "TBL_On_Hold_Code" $query = "SELECT * FROM " & $tblname ;& " WHERE Naam_H2S = 111" Local $title $adoCon = ObjCreate("ADODB.Connection") $adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname) ;Use this line if using MS Access 2003 and lower ;~ $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) ;Use this line if using MS Access 2007 and using the .accdb file extension $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($query, $adoCon) ;~ $title = $adoRs.Fields("Afname").value ;Retrieve value by field name $title = $adoRs.Fields("NaamProduct").value ;Retrieve value by field name $title = $adoRs.Fields("OnHoldCode").value ;Retrieve value by field name ;$title = $adoRs.Fields(2).value ;Retrieve value by column number $adoCon.Close MsgBox(0,"testing",$title)I want to make a connection with a database. i used an example script. This i did on a nem database (testing it athome) $dbname = "C:\TRA\TEST_FOR_AUTOIT.mdb" $tblname = "Producten" $title = $adoRs.Fields("NaamProduct").value This works. now i want to test it on the database i need. $dbname = "C:\TRA\TRA_BACKEND.mdb" $tblname = "TBL_On_Hold_Code" $title = $adoRs.Fields("OnHoldCode").value and it will not work. the errorcode :C:\Documents and Settings\MAVR\Bureaublad\data_Test2.au3 (37) : ==> The requested action with this object has failed.: the code thanx Link to comment Share on other sites More sharing options...
hannes08 Posted February 21, 2011 Share Posted February 21, 2011 Hello motormad,we need to know the code from line 37 of your script (C:\Documents and Settings\MAVR\Bureaublad\data_Test2.au3 (37)). The example you provided does not hold the required information.Regards,Hannes Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
motormad Posted February 21, 2011 Author Share Posted February 21, 2011 37 = > $adoRs.Open ($query, $adoCon) Link to comment Share on other sites More sharing options...
GEOSoft Posted February 21, 2011 Share Posted February 21, 2011 I think you have the provider wrong for 2007. I use this function in my access UDF constants file. Func _adoProvider() Local $oProvider = "Microsoft.Jet.OLEDB.4.0; " Local $objCheck = ObjCreate("Access.application") If IsObj($objCheck) Then Local $oVersion = $objCheck.Version If StringLeft($oVersion, 2) == 12 Then $oProvider="Microsoft.ACE.OLEDB.12.0; " EndIf Return $oProvider EndFunc George Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.*** The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number. Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else. "Old age and treachery will always overcome youth and skill!" Link to comment Share on other sites More sharing options...
DarkestHour Posted February 21, 2011 Share Posted February 21, 2011 You also seem to need an error handler, Insert these lines into your script. This line at the beginning somewhere: $objErr = ObjEvent("AutoIt.Error","MyErrFunc") Then this function somewhere: Func MyErrFunc() $hexnum=hex($objErr.number,8) Msgbox(0,"","We intercepted a COM Error!!" & @CRLF & @CRLF & _ "err.description is: " & $objErr.description & @CRLF & _ "err.windescription is: " & $objErr.windescription & @CRLF & _ "err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _ "err.scriptline is: " & $objErr.scriptline & @CRLF & _ "err.number is: " & $hexnum & @CRLF & _ "err.source is: " & $objErr.source & @CRLF & _ "err.helpfile is: " & $objErr.helpfile & @CRLF & _ "err.helpcontext is: " & $objErr.helpcontext _ ) SetError(1) EndFunc Then try the program again, this may give you a more meaningful error. Link to comment Share on other sites More sharing options...
motormad Posted February 22, 2011 Author Share Posted February 22, 2011 (edited) THANX !! This is great. Its in dutch so ill try to translate later in the day (working now) but the error number is 80020009 err.description say it can not read the record because i do not have the authorization to acces the table We intercepted a COM Error!! err.description is: [Microsoft][ODBC Microsoft Access-stuurprogramma] Kan de record(s) niet lezen. U hebt geen machtiging voor TBL_On_Hold_Code om de gegevens te lezen. err.windescription is: err.lastdllerror is: 0 err.scriptline is: 33 err.number is: 80020009 err.source is: Microsoft OLE DB Provider for ODBC Drivers err.helpfile is: err.helpcontext is: 0 We intercepted a COM Error!! err.description is: [Microsoft][ODBC Microsoft Access-stuurprogramma] Kan de record(s) niet lezen. U hebt geen machtiging voor TBL_On_Hold_Code om de gegevens te lezen. err.windescription is: Niet nader omschreven fout err.lastdllerror is: 0 err.scriptline is: 35 err.number is: 80020009 err.source is: Microsoft OLE DB Provider for ODBC Drivers err.helpfile is: err.helpcontext is: 0 We intercepted a COM Error!! err.description is: Kan het item niet vinden in de collectie die overeenkomt met de gewenste naam of het gewenste nummer. err.windescription is: err.lastdllerror is: 0 err.scriptline is: 36 err.number is: 80020009 err.source is: ADODB.Recordset err.helpfile is: C:\WINDOWS\HELP\ADO270.CHM err.helpcontext is: 1240649 Edited February 22, 2011 by motormad Link to comment Share on other sites More sharing options...
DarkestHour Posted February 22, 2011 Share Posted February 22, 2011 I just ran it through google translate. Do you have user security setup on that database? If so you might need to specify a username and password in the connection string. Link to comment Share on other sites More sharing options...
motormad Posted February 22, 2011 Author Share Posted February 22, 2011 thanx, Il try it. Ill have to look up how to do it first. Maybe you can give an example string ? Link to comment Share on other sites More sharing options...
DarkestHour Posted February 22, 2011 Share Posted February 22, 2011 Hmm, suppose i should have included one, as an example, here is your edited string, replace Temp with your userid and password $adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname & ";User ID=Temp;Password=Temp") ;Use this line if using MS Access 2003 and lower Next time you post, post your updated code as you would run it so we can all be on the same page. Link to comment Share on other sites More sharing options...
motormad Posted February 22, 2011 Author Share Posted February 22, 2011 Thanx for your help. What i do not understand is... i can open the database without login or password. But acces has an option , somthing about an mdw file where you have to be in. In this mdw is a table named 'MSysUserList' here is a filed named 'name' there is my computer name 'TRA' so my code is like this. but it stil will not work :-( expandcollapse popup#cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.6.1 #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here $dbname = "C:\TRA\TEST_FOR_AUTOIT.mdb" $tblname = "Producten" $objErr = ObjEvent("AutoIt.Error","MyErrFunc") $dbname = "C:\TRA\TRA_BACKEND.mdb" $tblname = "TBL_On_Hold_Code" $query = "SELECT * FROM " & $tblname ;& " WHERE Naam_H2S = 111" Local $title $adoCon = ObjCreate("ADODB.Connection") ;$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname) ;Use this line if using MS Access 2003 and lower $adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname & ";User ID='TRA';Password=''") ;Use this line if using MS Access 2003 and lower ;~ $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname) ;Use this line if using MS Access 2007 and using the .accdb file extension $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($query, $adoCon) $title = $adoRs.Fields("NaamProduct").value ;Retrieve value by field name $title = $adoRs.Fields("OnHoldCode").value ;Retrieve value by field name ;$title = $adoRs.Fields(2).value ;Retrieve value by column number $adoCon.Close MsgBox(0,"testing",$title) Func MyErrFunc() $hexnum=hex($objErr.number,8) Msgbox(0,"","We intercepted a COM Error!!" & @CRLF & @CRLF & _ "err.description is: " & $objErr.description & @CRLF & _ "err.windescription is: " & $objErr.windescription & @CRLF & _ "err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _ "err.scriptline is: " & $objErr.scriptline & @CRLF & _ "err.number is: " & $hexnum & @CRLF & _ "err.source is: " & $objErr.source & @CRLF & _ "err.helpfile is: " & $objErr.helpfile & @CRLF & _ "err.helpcontext is: " & $objErr.helpcontext _ ) ConsoleWrite ("We intercepted a COM Error!!" & @CRLF) ConsoleWrite ("err.description is: " & $objErr.description & @CRLF) ConsoleWrite ("err.windescription is: " & $objErr.windescription & @CRLF) ConsoleWrite ("err.lastdllerror is: " & $objErr.lastdllerror & @CRLF) ConsoleWrite ("err.scriptline is: " & $objErr.scriptline & @CRLF) ConsoleWrite ("err.number is: " & $hexnum & @CRLF) ConsoleWrite ("err.source is: " & $objErr.source & @CRLF) ConsoleWrite ("err.helpfile is: " & $objErr.helpfile & @CRLF) ConsoleWrite ("err.helpcontext is: " & $objErr.helpcontext & @CRLF) ConsoleWrite (@CRLF) SetError(1) EndFunc Link to comment Share on other sites More sharing options...
DarkestHour Posted February 22, 2011 Share Posted February 22, 2011 your Query line has a comment in it. $query = "SELECT * FROM " & $tblname ;& " WHERE Naam_H2S = 111" Take out the ; Also you have 2 $title callouts to different field commands. You should comment out the first one i think it is. Also, try this code instead of the field commands. This is since your query already selects the data you want. $title = $adoRs.GetRows() _arraydisplay($title) ; This needs #include <array.au3> in the beginning of the program Let me know the errors you get again. Link to comment Share on other sites More sharing options...
motormad Posted February 22, 2011 Author Share Posted February 22, 2011 expandcollapse popup#cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.6.1 #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here #include <array.au3> $dbname = "C:\TRA\TRA_BACKEND.mdb" $tblname = "TBL_On_Hold_Code" $objErr = ObjEvent("AutoIt.Error","MyErrFunc") $query = "SELECT * FROM " & $tblname Local $title $adoCon = ObjCreate("ADODB.Connection") ;$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname) ;Use this line if using MS Access 2003 and lower $adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname & ";User ID='';Password=''") ;Use this line if using MS Access 2003 and lower $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($query, $adoCon) ;$title = $adoRs.Fields("OnHoldCode").value ;Retrieve value by field name $title = $adoRs.Fields(2).value ;Retrieve value by column number $title = $adoRs.GetRows() _arraydisplay($title) $adoCon.Close MsgBox(0,"testing",$title) Func MyErrFunc() $hexnum=hex($objErr.number,8) Msgbox(0,"","We intercepted a COM Error!!" & @CRLF & @CRLF & _ "err.description is: " & $objErr.description & @CRLF & _ "err.windescription is: " & $objErr.windescription & @CRLF & _ "err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _ "err.scriptline is: " & $objErr.scriptline & @CRLF & _ "err.number is: " & $hexnum & @CRLF & _ "err.source is: " & $objErr.source & @CRLF & _ "err.helpfile is: " & $objErr.helpfile & @CRLF & _ "err.helpcontext is: " & $objErr.helpcontext _ ) ConsoleWrite ("We intercepted a COM Error!!" & @CRLF) ConsoleWrite ("err.description is: " & $objErr.description & @CRLF) ConsoleWrite ("err.windescription is: " & $objErr.windescription & @CRLF) ConsoleWrite ("err.lastdllerror is: " & $objErr.lastdllerror & @CRLF) ConsoleWrite ("err.scriptline is: " & $objErr.scriptline & @CRLF) ConsoleWrite ("err.number is: " & $hexnum & @CRLF) ConsoleWrite ("err.source is: " & $objErr.source & @CRLF) ConsoleWrite ("err.helpfile is: " & $objErr.helpfile & @CRLF) ConsoleWrite ("err.helpcontext is: " & $objErr.helpcontext & @CRLF) ConsoleWrite (@CRLF) SetError(1) EndFunc TRA_BACKEND.zip Link to comment Share on other sites More sharing options...
DarkestHour Posted February 22, 2011 Share Posted February 22, 2011 Ok, i got it to work, i added all privledges to the admin user in access and also used JET OLEDB as the provider. You'll need to update the path to the file as i changed it for my computer. Let me know how this works for you. expandcollapse popup#cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.6.1 #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here #include <array.au3> $dbname = "C:\TRA\TRA_BACKEND.mdb" $tblname = "TBL_On_Hold_Code" $objErr = ObjEvent("AutoIt.Error","MyErrFunc") $query = "SELECT * FROM " & $tblname Local $title $adoCon = ObjCreate("ADODB.Connection") ;$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname) ;Use this line if using MS Access 2003 and lower ;$adoCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbname & ";User ID='';Password=''") ;Use this line if using MS Access 2003 and lower $adoCon.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\TRA_BACKEND.mdb') ;Use this line if using MS Access 2003 and lower $adoRs = ObjCreate ("ADODB.Recordset") $adoRs.CursorType = 1 $adoRs.LockType = 3 $adoRs.Open ($query, $adoCon) ;$title = $adoRs.Fields("OnHoldCode").value ;Retrieve value by field name ;$title = $adoRs.Fields(2).value ;Retrieve value by column number $title = $adoRs.GetRows() _arraydisplay($title) $adoCon.Close MsgBox(0,"testing",$title) Func MyErrFunc() $hexnum=hex($objErr.number,8) Msgbox(0,"","We intercepted a COM Error!!" & @CRLF & @CRLF & _ "err.description is: " & $objErr.description & @CRLF & _ "err.windescription is: " & $objErr.windescription & @CRLF & _ "err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _ "err.scriptline is: " & $objErr.scriptline & @CRLF & _ "err.number is: " & $hexnum & @CRLF & _ "err.source is: " & $objErr.source & @CRLF & _ "err.helpfile is: " & $objErr.helpfile & @CRLF & _ "err.helpcontext is: " & $objErr.helpcontext _ ) ConsoleWrite ("We intercepted a COM Error!!" & @CRLF) ConsoleWrite ("err.description is: " & $objErr.description & @CRLF) ConsoleWrite ("err.windescription is: " & $objErr.windescription & @CRLF) ConsoleWrite ("err.lastdllerror is: " & $objErr.lastdllerror & @CRLF) ConsoleWrite ("err.scriptline is: " & $objErr.scriptline & @CRLF) ConsoleWrite ("err.number is: " & $hexnum & @CRLF) ConsoleWrite ("err.source is: " & $objErr.source & @CRLF) ConsoleWrite ("err.helpfile is: " & $objErr.helpfile & @CRLF) ConsoleWrite ("err.helpcontext is: " & $objErr.helpcontext & @CRLF) ConsoleWrite (@CRLF) SetError(1) EndFuncTRA_BACKEND.zip Link to comment Share on other sites More sharing options...
motormad Posted February 23, 2011 Author Share Posted February 23, 2011 this works. $adoCon.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $dbname) I have to alter the restrictions. (make the table readable for the admin) The purpose of my script is to copy the file on my drive. Then make an array of numbers that i get out of excel file. And run this thru the acces database. Is there no other way of altering thesettings of evvery table? There are quite a lot of tables. Thanks for your help so for :-) Link to comment Share on other sites More sharing options...
DarkestHour Posted February 23, 2011 Share Posted February 23, 2011 I'm not a Wiz with access and i cant seem to find any sort of command line program that would let you do that. Perhaps you can automate the task in Autoit? None the less i'm glad you were able to atleast pull your data from access now. Link to comment Share on other sites More sharing options...
motormad Posted February 23, 2011 Author Share Posted February 23, 2011 Thanx DarkestHour :-) Link to comment Share on other sites More sharing options...
bacobampense Posted September 21, 2011 Share Posted September 21, 2011 You have defined the DB Location on the top as $dbname = "C:\TRA\TRA_BACKEND.mdb" and a different path $adoCon.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TRA\TRA_BACKEND.mdb') ;Use this line if using MS Access 2003 and lower $adoRs = ObjCreate ("ADODB.Recordset") I corrected to the same directory and it worked. Link to comment Share on other sites More sharing options...
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