rst.EOF when there is data - ?

[hwkranger]

The question is: Why would a recordset be EOF when the source SQL when queried has records.

Immediate window:

	?rst.Source
	Select path, file_name, missing, password 
	from Files_To_Parse

	?rst.EOF
	True

Database query source for: Files_To_Parse

SELECT Database.*
FROM [Database]
WHERE (((Database.File_Name) Like "*dump*") AND ((Database.Missing)=False) AND ((Database.ErrorNumber) Is Null) AND ((Database.Delete)=False) AND ((Database.Skip)=False))
ORDER BY Database.Path DESC;

returns 1 record. Why is my rst.EOF?

I've compacted and repaired. I'm not sure what's wrong with this.

Thanks for the help!

Randall Vollen

 

[AlexCuse]

I'm not sure this will help you, but I always throw an rs.MoveFirst in right after creation of the recordset. I'm not sure why the cursor would be at EOF, but maybe this will make it work?

Good Luck!

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[acent]

If your'e sure there are in fact records, try calling rst.movefirst before the code. You may also want to check for rst.bof Have you tried looking at what the recordcount gives you? This value will only be populated if you have moved through the recordset.

Just a thought.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[hwkranger]

I tried the movefirst thing and it didn't work. I thought of that, since sometimes Access f's up.

Anyways. I couldn't figure it out, so a colleague suggested using DAO. It works with DAO.

This makes me wonder if it isn't either

1) The project (perhaps it's corrupt)
2) My ADO library (but it works in other cases)

I'm a bit baffled by this occurance.

 

[JoeAtWork]

In ADO, the operators you use for LIKE are different than Access.
Instead of

Like "*dump*"

it should be

Like "%dump%"

 

[hwkranger]

Joe,

You're thinking MSSQL. Even with that part removed (where the query returns HUNDREDS of records) I get an empty recordset.

 

[JoeAtWork]

hwkranger - ADO uses MSSQL syntax rather than Jet SQL syntax - regardless of whether you are using the Jet provider

 

[AlexCuse]

Interesting (I don't really use ADO to connect to Access very often). If it uses SQL Server syntax, wouldn't it be

like '%dump%'

though?

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[JoeAtWork]

AlexCuse - yes, I think you're right.

 

[TheAceMan1]

hwkranger . . .

In the answer wizard of VBE help enter [blue]bof,eof properties[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[hwkranger]

TheAceMan1, if it was that simple I would have figured it out. This isn't my first day on the job.



Joe,

The problem persisted when I removed the criteria. I originally thought of that. Since the query is a SAVED object from access, I left it in the ACCESS format.

Alex,

Thanks for the tid-bit. It made me laugh, since I've stated that the problem wasn't with the selection criteria. Everyone wants to jump on the selection criteria the answer.

As a solution: I think it was the db or my computer or something. b/c today when I try it -- it works fine. (Same data, different project)

rst.Open "Select * from Files_to_Parse", cn, adOpenKeyset, adLockReadOnly
Debug.Print rst.RecordCount
Debug.Print rst.EOF

returns

 784 
False

either way. I got it to work with DAO previously. Today I followed up after making a new project to see if I got the same issues... which I didn't... I chalk this up to Access crapola.

 

[TheAceMan1]

hwkranger . . .

You have to declare and set a recordset object:

[blue] Dim db As DAO.Database, rst As DAO.Recordset

   Set db = Currentdb
   Set rst = db.OpenRecordset("source", dbOpenDynaset)

   Debug.Print rst.EOF[/blue]

In your immediate window secnario:

[blue] ?rst.Source[/blue]

Doesn't set a recordset object!

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[hwkranger]

1> ?rst.source is just to show that is the source of the record set. I think at this point (in our careers) I can just skip SHOWING everyone how I declared a variable.

a> You can't have EOF unless it's open. Ergo I had to have (declared and) opened it or i would have had a run time error.

2> I prefer to use ADO. DAO is imho dated. It's old and needs to be retired. I was confused why my ADO wasn't working properly. Today I ran the code I had from the other week and it ran fine. Which means: Since I Didn't change anything in the code, then something ELSE had to have changed.

3> And -- technically -- Option Explicit is what forces the declarations. I could just NEVER declare anything and just do whatever I want with it. (True I would have to OPEN it, but declarations are optional)

Best Regards!

 

[TheAceMan1]

hwkranger . . .

[blue]Enjoy your life more! . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[JoeAtWork]

I didn't realize you were opening a saved object in Access, I thought you were doing something like:

rs.Open "SELECT * FROM tblTable WHERE Name LIKE "*something*", CurrentProject.Connection

In the above case you would have to use the SQL Server syntax (ADO likes to present all databases as being the same - the theory being that if you switched databases all you would need to change is the connection string).

I always use (BOF and EOF) to check for an empty recordset. I have found RecordCount to be too unpredictable. And even though if you open a recordset and if it is at EOF it usually means there are no records, technically that's not what it means.

DAO may be dated, but it's still the most efficient for Access databases. I try to still use it if I'm dealing with Access, although I have become more comfortable with ADO.

 

[TheAceMan1]

Howdy JoeAtWork . . .

[blue]What a great thing it would be if we could read minds![/blue] . . . Iwas thinking a couple of other things myself . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[JerryKlmns]

hwkranger,

For ADO, when you open a Recordset, the current record is positioned to the first record (if any), thus no need to check for empty and move first like DAO.

Your use of the Jet wild char * has no effect since it resides in a saved query. If it was an sql statement passed to the recordset's source property, then you'll had wrong results, as Joe mentions. Also the actual count of records should be concinder to be reliable only for a static or keyset cursor. Both slow the openning of the recordset. But a SELECT Count(*) FROM yourTable is always correct even with a ForwardOnly+ReadOnly+ServerSide cursor.

Also note that some methods like .GetString moves you to the next record, although MSAccess help doesn't says so nor the example shows that for getting one record at the time. I have found that the hard way. Some goes for .GetRows

 

[hwkranger]

Sorry if I sounded grumpy. I was a little pissed at Access. It pisses me off all the time. I try NOT to use it. I prefer to use MS SQL as the BE, but -- sometimes you don't have that luxury.

Anyways.. I never knew the .getstring moved to the next record. That could be problematic.... I used to use it all the time to fill a listbox. (.getstring(,,",",";")) Nice to know that though.

Have a great week everyone.

Best Regards.