ADO Passthrough Query Order-By Problem 1

[dmassey]

HI

I am connecting to a table on an AS400 server using the following code:

cn.ConnectionString = "Connect=ODBC;DSN=AS400 AMFLIB;UID=;PWD=;"

rs.Open "Select ITNBR, ITDSC, UCDEF FROM AMFLIB.ITEMASA ORDER BY ITNBR", cn, adOpenDynamic, adLockOptimistic

My problem is that the query is passed to the server and the recordset is sorted in the sortorder that the server uses. I need to have the recordset sorted in the order that access 97 uses.

Can I create another recordset based on the first somehow?

I could do this in access by linking to the table on the server, and then creating a query to sort the results, but I need to accomplish the same thing in code.

Thanks, Derek

 

[MarkSweetland]

You can use the ADODB .Sort property to "resort" the resultant recordset. However, the recordset needs to be opened with a client side cursor:

cn.ConnectionString = "Connect=ODBC;DSN=AS400 AMFLIB;UID=;PWD=;"
cn.CursorLocation = adUseClient

rs.Open "Select ITNBR, ITDSC, UCDEF FROM AMFLIB.ITEMASA ORDER BY ITNBR", cn, adOpenDynamic, adLockOptimistic

rs.Sort = "ITNBR, ITDSC DESC" [green]' to sort by ITNBR and ITDSC descending[/green]

...


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[dmassey]

Thanks, Mark

That was exactly what I needed. I hadn't thought of the client side cursor.

Derek