Problem with OpenRecordset and SQL Statement

Runey

New member
Local time
Tomorrow, 04:20
Joined
Jan 11, 2005
Messages
5
Hi there,

Hope someone can help

I am opening a recordset like this, where strDatabase is an SQL Statement

Set rstDatafile = dbDatafile.OpenRecordset(strDatabase, dbOpenSnapshot)

SQL Statement =[tblName] ORDER [Name]

It opens and everything is seemingly OK. However I then run a loop that deletes records as per a set of criteria. If I run this on the same unaltered table I get different results each time. It's almost like the SQL statement is not ordering it the same each time.

Am I doing something worng or is there another way to open a table order it's contents and modify it?

Thanks for your time
 
What is the actual SQL statement? It's a bit easier to write it than us having to guess it. ;)

Runey said:
I then run a loop that deletes records as per a set of criteria.

Why not just use a DELETE Query?
 
What you posted as the SQL string isn't a real query. Can you paste the actual SQL statement? Are you certain that it has an order by clause? Is the field it is ordering on unique?

Recordsets are unordered sets. The ONLY time you can predict their order is if they are made from a query that contains an Order By clause that orders on a UNIQUE identifier. If the Order By clause only groups the rows, the detail order may vary from execution to execution. That is the way a sort works and there's nothing you can do about it.
 
Hi, Thanks for repsponding.

Ooops I didn't cut and pastse the SQL statement properly. It's a basic one that goes like this

SELECT * FROM [tblGames] ORDER [tblGames].Name

But I think Pats hit it on the head. The Name field could very well have multiple entries that are the same. Can I order by two fields, say Name and Description? The two together would definately be unique.

Thanks for your time

PS: The criteria is pretty compilcated, I'm not sure if I could do it in a DELETE Query. But I'll have a look.
 

Users who are viewing this thread

Back
Top Bottom