problem with simple loop

[aaron.kempf]

so i've got this problem with a simple ADO loop and it's been driving
me crazy..

all i want to do is open a SQL statement and loop through the
recordset... i guess i'm just confused on what im doing wrong.

basically the problem that im facing is this:

1) when i take this S_SQL statement out of the immediate window and put
it in a query; it works like a charm.

2) when I open this recordset against a MDB; it returns .EOF = True
right off the bat.. even though there should be 10 records in the
recordset.

What am i doing wrong??

thanks let me know if you need more information
it just looks like it's returning .EOF = True even though the SQL
statement works in access just perfectly.


Thanks! a ton


rst.Open S_SQL, CurrentProject.Connection

' If rst.EOF = True Then
' MsgBox "unable to find any queries with this sql statement: "
& vbCrLf & S_SQL, vbOKOnly
' GoTo cleanexit
' End If

Dim i As Integer
Dim qryName As String
i = 0
MsgBox thisParagraph, vbOKOnly
While (Not rst.EOF)
qryName = rst.Fields("Name").Value
FlashQF (qryName)
'should we check for valid DAO.querydef.type for each
phase?

i = i + 1
rst.MoveNext
Wend

 

[Someone]

What's the value of these Connection properties?

ConnectionString. Remove any passwords before posting it.
CursorLocation

What's the value of these Recordset properties?

CursorType
LockType

Also, after your "rst.Open", add the following lines and post the output
here:

Debug.Print rst.CursorLocation
Debug.Print rst.CursorType
Debug.Print rst.LockType

The DB Engine is free to change the last 2 properties of the recordset if it
doesn't support them without issuing any errors or warnings. After opening
the recordset, these properties reflect the changes made by the DB Engine if
any. See my replies in the following thread.

http://groups.google.com/group/micr...read/thread/2ea285682492a034/3ce345c929d21e6b

 

[aaron.kempf]

i just use currentproject.connection-- it's a pointer in a simple MDB
vba environment to a simple ADO connection.. it works like a charm in
ADP. I dont know why this is giving me a hard time in MDB.

I'll check those settings; thanks.. and post them if that doesnt help
thanks..

or maybe i'll try a real ADO connection

 

[Someone]

I assumed you have VB6, not VBA, still my answers are relevant to some
degree.

 

[dbahooker]

yeah.. the problem actually ended up being the LIKE clause against the
name of the msysobjects table.. dont know what it was; but i changed it
to left(name, 4) and it started working better

i was expecting to find something that i need to wrap in brackets; i
just didnt find it

-aaron