Sign in to follow this  
Followers 0
motormad

Acces Database error (37)

17 posts in this topic

$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

Share this post


Link to post
Share on other sites



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]

Share this post


Link to post
Share on other sites

:)

37 = > $adoRs.Open ($query, $adoCon)

;)

Share this post


Link to post
Share on other sites

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!"

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (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 by motormad

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

thanx,

Il try it.

Ill have to look up how to do it first.

Maybe you can give an example string ? :)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 :-(

#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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#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

Share this post


Link to post
Share on other sites

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. :)

#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)
EndFunc

TRA_BACKEND.zip

Share this post


Link to post
Share on other sites

:)

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 :-)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Thanx DarkestHour :-)

Share this post


Link to post
Share on other sites

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.

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  
Followers 0