Acces Database error (37)

[motormad]

$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

[hannes08]

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

[motormad]

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

[GEOSoft]

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

[DarkestHour]

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.

[motormad]

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

[DarkestHour]

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.

[motormad]

thanx,

Il try it.

Ill have to look up how to do it first.

Maybe you can give an example string ?

[DarkestHour]

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.

[motormad]

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

[DarkestHour]

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.

[motormad]

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

[DarkestHour]

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

TRA_BACKEND.zip

[motormad]

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

[DarkestHour]

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.

[motormad]

Thanx DarkestHour :-)

[bacobampense]

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.