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

[Myicq]

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:

expandcollapse popup

; 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 August 16, 2012 by Myicq

[Myicq]

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.

[Juvigy]

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 August 16, 2012 by Juvigy

[Myicq]

@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.