Why is my vba recordset not sorted?

[MajP]

The problem was tblCnf was not sorted (as specified in the ORDER BY statement),

OOH I get it no wonder everyone was so confused. And even more importantly further emphasizes why you have to use an order by clause even if you table looks ordered.
You set the OrderBy property of the table. In 20+ years of programming Access never done it, and never needed to. I do not look at tables, I look and use queries.
So for everyone else that was as confused as I, the OrderBy property is used when opening a datasheet view in the application. It is not read by DAO. You can test this yourself. I verified it does not effect a recordset. In the below table the orderby is set to FItemID and will be sorted when viewed in Access. It is not sorted in DAO and most likely ADODB.

 

[HalloweenWeed]

OOH I get it no wonder everyone was so confused. And even more importantly further emphasizes why you have to use an order by clause even if you table looks ordered.
You set the OrderBy property of the table. In 20+ years of programming Access never done it, and never needed to. I do not look at tables, I look and use queries.
So for everyone else that was as confused as I, the OrderBy property is used when opening a datasheet view in the application. It is not read by DAO. You can test this yourself. I verified it does not effect a recordset. In the below table the orderby is set to FItemID and will be sorted when viewed in Access. It is not sorted in DAO and most likely ADODB.

It is a vba recordset from SQL (a "query" for all practical purposes), not a table. Therefore whatever was set in OrderBy in the table (nothing) is absolutely meaningless, correct? If so, please read the previous posts (code) more closely - specifically the tblCnf OpenRecordset line.

 

[Pat Hartman]

As I said early on, You CANNOT open a table in DS view and expect it to be sorted. Tables are unordered sets. The ONLY way to impose order is to open the table with a query that includes an order by clause.

If the query was not returning the records in the defined sort order, perhaps the database is corrupted. We don't have any way of knowing.

 

[MajP]

Let me get this straight, the name of your order query is "tblCnf"? Recommend you read the thread on naming conventions. That type of naming convention only adds confusion. qryCnf maybe?
So your printed the Sql string to the immediate window. Did you drop it into the query designger? Did it sort correctly? I do not know what you are doing wrong, but something is wrong with your logic or the sql. I have never seen or heard of an example where you cannot force a recordset to sort.

Although rarely used you can sort a recordset.
dim rs = dao.recordset
set rs = .....
rs.sort = "lastName, FirstName"
dim rsSorted as dao.recordset
set rsSorted = rs.openrecordset

 

[HalloweenWeed]

Let me get this straight, the name of your order query is "tblCnf"? Recommend you read the thread on naming conventions. That type of naming convention only adds confusion. qryCnf maybe?
So your printed the Sql string to the immediate window. Did you drop it into the query designger? Did it sort correctly? I do not know what you are doing wrong, but something is wrong with your logic or the sql. I have never seen or heard of an example where you cannot force a recordset to sort.

Although rarely used you can sort a recordset.
dim rs = dao.recordset
set rs = .....
rs.sort = "lastName, FirstName"
dim rsSorted as dao.recordset
set rsSorted = rs.openrecordset

Sorry, I am self-taught, don't know all the naming conventions, I just try my best to follow the examples I have seen.
Hmm, interesting, I was planning on going back in the future to redo this subroutine to use a recordset entirely in memory rather than the hard table. If I get to this I will try it (however I am about to have a second interview for a different job, same employer different agency, and it may not leave me with the time to do this FYI). I didn't know I could do this. Thank you for the tip. This was just the sort of solution that I was looking for with this thread.

 

[HalloweenWeed]

So your printed the Sql string to the immediate window. Did you drop it into the query designger? Did it sort correctly? I do not know what you are doing wrong, but something is wrong with your logic or the sql.

Yes I did (actually in the popup window from MsgBox() from the immediate window). But I found that when I put it into the query designer SQL it didn't like 'SELECT "*"'. So I had to go to the "design view" and drag all the fields into the query manually. So then I took that code, which worked perfectly, and put it into an SQL string in that same subroutine, replacing SQLTmp with:

    SQLTmp = "SELECT PtntLogging_tbl.ID_Key, PtntLogging_tbl.ID,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MasterPtNameRcrdStatus, PtntLogging_tbl.MasterPtNameRcrdIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MstrNmCnf, PtntLogging_tbl.MR_No, PtntLogging_tbl.MR_txt,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MasterMRRcrdStatus, PtntLogging_tbl.MasterMRRcrdIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MstrMRCnf, PtntLogging_tbl.MRMtchIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.PtntNameInpt, PtntLogging_tbl.PtntLN_FN_MI,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.PtLstName, PtntLogging_tbl.PtFrstName,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.PtMidSfxName, PtntLogging_tbl.PtNmMtchIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.NmCnfdnc, PtntLogging_tbl.MRCnfdnc,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.CntExctPtRcrdMtch, PtntLogging_tbl.CntPtntNameMtch,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.CountMR_LN_FN, PtntLogging_tbl.CntExctNameMtch,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.ErrorFlg, PtntLogging_tbl.MRErrorFlg, PtntLogging_tbl.NmErrorFlg,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.InvrtdPtNames, PtntLogging_tbl.NmInvrsnCnfdnc,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.InvrtdPtNamIndx, PtntLogging_tbl.RcmndPtntLN_FN_MI,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.RcmndPtLstName, PtntLogging_tbl.RcmndPtFrstName,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.RcmndPtMidSfxName, FROM PtntLogging_tbl"
    SQLTmp = SQLTmp & " WHERE (((ZeroLength([PtntLogging_tbl].[PtntLN_FN_MI]))=False)"
    SQLTmp = SQLTmp & " AND ((VarType([MR_No]))>1))"
    SQLTmp = SQLTmp & " ORDER BY PtntLogging_tbl.NmCnfdnc DESC, PtntLogging_tbl.MRCnfdnc DESC;"

    Set tblCnf = DB.OpenRecordset(SQLTmp, dbOpenSnapshot)

And it still didn't sort. FYI.

 

[The_Doc_Man]

Is that a screen capture? Because if so, I am surprised it would have returned anything. There is an extraneous comma in front of the FROM clause, which normally would trigger a syntax error.

 

[MajP]

Where is there no "debug.print SqlTmp" and why did can you not post the result?? I want to see how the string resolves.

 

[HalloweenWeed]

Is that a screen capture? Because if so, I am surprised it would have returned anything. There is an extraneous comma in front of the FROM clause, which normally would trigger a syntax error.

Actually I just pasted from a previous version save, and yes I did troubleshoot said extraneous comma and remove it before it would run. Apparently I used the save previous to that troubleshooting session, sorry.

 

[CJ_London]

Just as an aside. Have you ever copied using windows explorer a large number of files from one folder to another? If so you will no doubt notice that windows does not start with the first file then moves on to the next.

instead, files are copied at random. At least that is my observation based on the destination folder. Unless there is some underlying rule where the order is based on size or creation date or some other metric perhaps to do with level of disk fragmenta?

I believe access will work in much the same way - take the first record and put it here, take the second and squeeze it in someplace else where there is a sufficiently large enough gap in the file continuum, etc.

Or perhaps something to do with efficient recall of records, some will be larger than others based on content so maximising the number of records in a block by grouping the small ones together. But just speculation on my part.

make table probably works better because you are starting with a whole new object

 

[The_Doc_Man]

CJ, this only applies to Compact & Repair, but I believe I have read this somewhere. During a C&R, Access visits the MSysObjects table and copies things in internal object ID order, one object at a time, which has the side effect that every object is internally contiguous. At least until the first time you do anything else.

 

[CJ_London]

It is speculation in my part. I only use tables to store data, not for presentation and a sort order is one of the elements of presentation.

agree with C&R - it is a whole new object

 

[The_Doc_Man]

I've thought about this for a while after you asked. This is SPECULATION on my part and if someone has evidence to the contrary, I would not be totally heart-broken. However, I believe that the method of the copy will make a difference. As noted by the OP, if you use recordset-oriented code or copy something via SQL to make the copied table, you should be able to specify the order of records using ORDER BY syntax. However, if you do a copy/paste through the GUI, I would suggest that you preserve the order of the table at the moment of the copy.