Sign in to follow this  
Followers 0
Myicq

[Solved] Complete example executing Query in MS Access 2007/2010 ?

4 posts in this topic

#1 ·  Posted (edited)

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.

<rant>

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

</rant>

Edited by Myicq

I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites



Update: looking closer at the Microsoft page, they specify that above code uses ADODB.

If I change the code to use ODBC, it works:

; this uses ODBC
$adoCon.Open('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' & $dbname & ';')

While that is good for me, I am very curious about what the reason for this is ? There must be something I need to do to make ADODB work.


I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Some databases doesnt support .RecordCount. Change this:

with $adoRs
if .RecordCount then
while not (.EOF)
$_output = $_output & .Fields("Address").Value & @CRLF
.MoveNext
WEnd
msgbox(0,"RC", .RecordCount & " records found..")
endif
EndWith

To this:

$i = 0
with $adoRs
while not (.EOF)
$_output = $_output & .Fields("Address").Value & @CRLF
.MoveNext
$i = $i +1
WEnd
msgbox(0,"RC", $i & " records found..")
endif
EndWith
Edited by Juvigy

Share this post


Link to post
Share on other sites

@Juvigy,

thank you for your response! Your comment lead me to do the research I should have done in advance :(

Turns out that by looking at the documentation Microsoft provides, the ability to use .RecordCount depends on the cursortype. I copied a snippet from somewhere else, and actually set the cursortype.

So, with comments:

; ========
; CursorType and LockType below are for read-only access, f.ex for a drop-down-box or report.
; not for adding content

;http://msdn.microsoft.com/en-us/library/windows/desktop/ms681771%28v=vs.85%29.aspx
;$adoRs.CursorType = 1 ; 1= OpenKeySet, like a dynamic but you can't see records others add
$adoRs.CursorType = 3 ; 1= adOpenStatic, static copy of records...

;http://msdn.microsoft.com/en-us/library/windows/desktop/ms680855%28v=vs.85%29.aspx
;$adoRs.LockType = 3 ; locktype 3 = optimistic.
$adoRS.LockType = 1 ; Read-Only

; You can only use RecordCount property on some cursors..
; http://msdn.microsoft.com/en-us/library/windows/desktop/ms676701%28v=vs.85%29.aspx
; The cursor type of the [b]Recordset[/b] object affects whether the number of records can be determined.
; The [b]RecordCount[/b] property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor;
; and either -1 or the actual count for a dynamic cursor, depending on the data source.

So consider this topic solved.. thank you again for your pointer in the right direction.


I am just a hobby programmer, and nothing great to publish right now.

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

  • Similar Content

    • Valnurat
      By Valnurat
      I hope my title is good enough.
      I'm using the ADO UDF and I have question regarding editing SQL records with this UDF.
      The owner of the UDF suggested an idea, but maybe there is another trix.
    • Bowmore
      By Bowmore
      A few months ago someone posted a function that scanned a PC registry and returned a list of all the available ADODB connections installed. I've been searching on and off all afternoon trying to locate it. Does anyone remember which topic this was in? As far as my failing memory goes it was a post part way through an ADO related topic.
      Thanks
       
    • mLipok
      By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       

      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       

       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
       
       
    • mLipok
      By mLipok
      Currently I'm working on MS SQL >> PostgreSQL migration.
      Here are some of interesting Video tutorials about PostgreSQL
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
    • mLipok
      By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      Support topic is here: http://www.autoitscript.com/forum/index.php?showtopic=180850
       
      This UDF is modifed version of _sql.au3 UDF.

      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH