Sort and Seek in the same recordset?

[Mac]

Can I open a recordset and Sort it and then Seek in it? I continue to get
errors due to a error related to the index. My Seek worked fine until I tried
to add a Sort prior. Should I use a mySQL to open the recordset? Any examples
would help.

 

[Brendan Reynolds]

Can I open a recordset and Sort it and then Seek in it? I continue to get
errors due to a error related to the index. My Seek worked fine until I
tried
to add a Sort prior. Should I use a mySQL to open the recordset? Any
examples
would help.

A DAO recordset? Nope. You can only use Sort with a dynaset-type and
snapshot-type recordsets, and you can only use Seek with table-type
recordsets. To sort a table-type recordset, use the Index property.

See the following on-line help topic ...

http://office.microsoft.com/client/...CESS.DEV&lcid=2057&QueryID=L5wB2Oelr&respos=2

I don't use ADO much, but according to the help file, you can set the Sort
property of an ADO recordset if you set the CursorLocation property to
adUseClient.

See the following on-line help topic ...

http://office.microsoft.com/client/helppreview.aspx?AssetID=HV012287611033&ns=MSACCESS.DEV&lcid=2057

 

[Mac]

I am using ADO. I did set the CursorLocation property to adUseClient and then
it returns an error of "Current provider does not support the necessary
interface for index functionality". My Seek sub works fine but once I add the
sort it bombs with that error. All I really need to do is open a table in the
order it was created (sort by autonumber) because it is a history. Here is my
module that errors:

Public Sub Dynamic_Update_Recycle_Repair_Counts()

Dim strSQL As String

strSQL = "SELECT [History].* FROM History ORDER BY [Entry #]ASC;"

'Open History recordset.
Dim rsHistory As New ADODB.Recordset

Set cn = CurrentProject.Connection
Set rsHistory = New ADODB.Recordset

With rsHistory
.ActiveConnection = cn
'.Source = strSQL
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open strSQL, Options:=adCmdTableDirect
'.Sort = "[Entry #] ASC"
.Index = "Serial Number"
End With


'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
..Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
..Index = "PrimaryKey"
..MoveFirst
End With



Do Until rsMainData.EOF = True
rsHistory.MoveFirst
'find the last instance,if any, of the current interface and update the
recycle count and repair count
rsHistory.Seek rsMainData("Serial Number"), adSeekLastEQ


If Not rsHistory.EOF Or rsHistory.BOF Then

rsMainData("Recycle Count") = rsHistory("Recycle Count")

rsMainData("Repair Count") = rsHistory("Repair Count")

Else


End If

'Update the current record before moving to the next record
rsMainData.Update

'Move to the next record in the "PCM Interfaces (Main Data)" table.
rsMainData.MoveNext


Loop

rsHistory.MoveFirst

'Close the recordsets
rsMainData.Close
rsHistory.Close
Set rsMainData = Nothing
Set rsHistory = Nothing

End Sub

 

[Brendan Reynolds]

It would appear that the help topic is wrong when it says that
CursorLocation should be adUseClient. I found a KB article with example code
that works for me, and in that code, CursorLocation is set to adUseServer.
Here's the code that worked for me ...

Sub LinkTableSeek()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\Users\Brendan Reynolds\Documents\testdb.mdb"
.Open
End With
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "Customers"

.CursorLocation = adUseServer

.CursorType = adOpenKeyset
.Open Options:=adCmdTableDirect
.Index = "Last Name"
.Seek "Lee"
If (Not .EOF And Not .BOF) Then
MsgBox rs.Fields("First Name").Value
Else
MsgBox "Record not found"
End If
.Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub

Here's a link to the KB article ...

http://www.kbalertz.com/Feedback.aspx?kbNumber=290060

--
Brendan Reynolds

I am using ADO. I did set the CursorLocation property to adUseClient and
then
it returns an error of "Current provider does not support the necessary
interface for index functionality". My Seek sub works fine but once I add
the
sort it bombs with that error. All I really need to do is open a table in
the
order it was created (sort by autonumber) because it is a history. Here is
my
module that errors:

Public Sub Dynamic_Update_Recycle_Repair_Counts()

Dim strSQL As String

strSQL = "SELECT [History].* FROM History ORDER BY [Entry #]ASC;"

'Open History recordset.
Dim rsHistory As New ADODB.Recordset

Set cn = CurrentProject.Connection
Set rsHistory = New ADODB.Recordset

With rsHistory
.ActiveConnection = cn
'.Source = strSQL
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open strSQL, Options:=adCmdTableDirect
'.Sort = "[Entry #] ASC"
.Index = "Serial Number"
End With


'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
.Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
.Index = "PrimaryKey"
.MoveFirst
End With



Do Until rsMainData.EOF = True
rsHistory.MoveFirst
'find the last instance,if any, of the current interface and update the
recycle count and repair count
rsHistory.Seek rsMainData("Serial Number"), adSeekLastEQ


If Not rsHistory.EOF Or rsHistory.BOF Then

rsMainData("Recycle Count") = rsHistory("Recycle Count")

rsMainData("Repair Count") = rsHistory("Repair Count")

Else


End If

'Update the current record before moving to the next record
rsMainData.Update

'Move to the next record in the "PCM Interfaces (Main Data)" table.
rsMainData.MoveNext


Loop

rsHistory.MoveFirst

'Close the recordsets
rsMainData.Close
rsHistory.Close
Set rsMainData = Nothing
Set rsHistory = Nothing

End Sub

--
Regards, Michael

A DAO recordset? Nope. You can only use Sort with a dynaset-type and
snapshot-type recordsets, and you can only use Seek with table-type
recordsets. To sort a table-type recordset, use the Index property.

See the following on-line help topic ...

http://office.microsoft.com/client/...CESS.DEV&lcid=2057&QueryID=L5wB2Oelr&respos=2

I don't use ADO much, but according to the help file, you can set the
Sort
property of an ADO recordset if you set the CursorLocation property to
adUseClient.

See the following on-line help topic ...

http://office.microsoft.com/client/helppreview.aspx?AssetID=HV012287611033&ns=MSACCESS.DEV&lcid=2057

 

[Jamie Collins]

Do Until rsMainData.EOF = True
rsHistory.MoveFirst
'find the last instance,if any, of the current interface and update the
recycle count and repair count
rsHistory.Seek rsMainData("Serial Number"), adSeekLastEQ

If Not rsHistory.EOF Or rsHistory.BOF Then

rsMainData("Recycle Count") = rsHistory("Recycle Count")

rsMainData("Repair Count") = rsHistory("Repair Count")

Else

End If

'Update the current record before moving to the next record
rsMainData.Update

From a quick glance it looks like you are iterating through one

recordset, seeking a record in a second recordset (consisting of every
row your table named 'History') matched on a common key column and, if
found, copying values back to the first recordset. This is classic
procedural mindset. There is another way...

Considering the first recordset is updateable then you should be able
to code the entire logic into a single UPDATE statement e.g. something
like (standard SQL aircode):

UPDATE Main
SET "Recycle Count" =
(
SELECT H1."Recycle Count"
FROM History AS H1
WHERE H1."Serial Number" = Main."Serial Number"
),
"Repair Count" =
(
SELECT H1."Repair Count"
FROM History AS H1
WHERE H1."Serial Number" = Main."Serial Number"
)
WHERE EXISTS
(
SELECT *
FROM History AS H1
WHERE H1."Serial Number" = Main."Serial Number"
);

Access/Jet SQL DML does not support the standard syntax; rather, you
are required to use has its own proprietary syntax (e.g. using a
single 'JOIN' in place of the multiple scalar subqueries, square
brackets in place of quotes, etc -- perhaps someone could post a
translation, TIA). However, you did mention mySQL in your original
post so I figure the standard SQL may be more useful to you.

Jamie.

--

 

[Mac]

It looks like the example code you sent me contains only a Seek. I have my
Seek working properly already but run into problems when I try to add a .Sort
or try to use a mySQL to sort the recordset. My problem is getting the .Seek
and .Sort to work in the same subroutine. The reason I need the sort is to
open the recordset in the order it was created (by autoumber) NOT ascending
by serial number (default). Once the recordset is sorted by automumber I can
..Seek for the desired Serial Number and use adSeekLastEQ to finds the last
entry of that Serial Number in the history recordset. Because it is a History
I need the most recent data that was entered for that particular Serial
Number. Any ideas on getting the recordset sorted prior to .Seek?
--
Regards, Michael

It would appear that the help topic is wrong when it says that
CursorLocation should be adUseClient. I found a KB article with example code
that works for me, and in that code, CursorLocation is set to adUseServer.
Here's the code that worked for me ...

Sub LinkTableSeek()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\Users\Brendan Reynolds\Documents\testdb.mdb"
.Open
End With
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "Customers"

.CursorLocation = adUseServer

.CursorType = adOpenKeyset
.Open Options:=adCmdTableDirect
.Index = "Last Name"
.Seek "Lee"
If (Not .EOF And Not .BOF) Then
MsgBox rs.Fields("First Name").Value
Else
MsgBox "Record not found"
End If
.Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub

Here's a link to the KB article ...

http://www.kbalertz.com/Feedback.aspx?kbNumber=290060

--
Brendan Reynolds

I am using ADO. I did set the CursorLocation property to adUseClient and
then
it returns an error of "Current provider does not support the necessary
interface for index functionality". My Seek sub works fine but once I add
the
sort it bombs with that error. All I really need to do is open a table in
the
order it was created (sort by autonumber) because it is a history. Here is
my
module that errors:

Public Sub Dynamic_Update_Recycle_Repair_Counts()

Dim strSQL As String

strSQL = "SELECT [History].* FROM History ORDER BY [Entry #]ASC;"

'Open History recordset.
Dim rsHistory As New ADODB.Recordset

Set cn = CurrentProject.Connection
Set rsHistory = New ADODB.Recordset

With rsHistory
.ActiveConnection = cn
'.Source = strSQL
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open strSQL, Options:=adCmdTableDirect
'.Sort = "[Entry #] ASC"
.Index = "Serial Number"
End With


'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
.Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
.Index = "PrimaryKey"
.MoveFirst
End With



Do Until rsMainData.EOF = True
rsHistory.MoveFirst
'find the last instance,if any, of the current interface and update the
recycle count and repair count
rsHistory.Seek rsMainData("Serial Number"), adSeekLastEQ


If Not rsHistory.EOF Or rsHistory.BOF Then

rsMainData("Recycle Count") = rsHistory("Recycle Count")

rsMainData("Repair Count") = rsHistory("Repair Count")

Else


End If

'Update the current record before moving to the next record
rsMainData.Update

'Move to the next record in the "PCM Interfaces (Main Data)" table.
rsMainData.MoveNext


Loop

rsHistory.MoveFirst

'Close the recordsets
rsMainData.Close
rsHistory.Close
Set rsMainData = Nothing
Set rsHistory = Nothing

End Sub

--
Regards, Michael

Can I open a recordset and Sort it and then Seek in it? I continue to
get
errors due to a error related to the index. My Seek worked fine until I
tried
to add a Sort prior. Should I use a mySQL to open the recordset? Any
examples
would help.
--
Regards, Michael


A DAO recordset? Nope. You can only use Sort with a dynaset-type and
snapshot-type recordsets, and you can only use Seek with table-type
recordsets. To sort a table-type recordset, use the Index property.

See the following on-line help topic ...

http://office.microsoft.com/client/...CESS.DEV&lcid=2057&QueryID=L5wB2Oelr&respos=2

I don't use ADO much, but according to the help file, you can set the
Sort
property of an ADO recordset if you set the CursorLocation property to
adUseClient.

See the following on-line help topic ...

http://office.microsoft.com/client/helppreview.aspx?AssetID=HV012287611033&ns=MSACCESS.DEV&lcid=2057

 

[Mac]

I appreciate your alternate code example. My requirements are really to sort
a history table (recordset) by the autonumber field so the records are in the
order entered, not in the order that Access uses by default when openeing a
recordset (indexed field, ascending). After sorted I need to find the LAST
entry of a particular Serial Number, for every Serial Number found in the
Main recordset and then grab a field (Recycle Count, Repair Count) from the
history and update the Main recordset. There are multiple entries of the same
serial number in the history because the history tracks all transactions of
all Serial Numbers so you can see why I need the last entry becasue it will
contain the latest information about that Serail Number. My .Seek is working
fine but my results are way off due to the order the recordset opens by
default. All of my trouble has been in finding a way to sort the recordset
after it is open but prior to .Seek. Any ideas on how to get a .Seek and
..Sort to work in the same Subroutine?

Thanks again for your help.

 

[Jamie Collins]

I can
.Seek for the desired Serial Number and use adSeekLastEQ to finds the last
entry of that Serial Number in the history recordset. Because it is a History
I need the most recent data that was entered for that particular Serial
Number. Any ideas on getting the recordset sorted prior to .Seek?

Again, wrong mindset. Rather than 'get all rows, sort into date order,
go to the last row', try going straight for 'get the most recent row'.

Consider this simple example query using Northwind to get the most
recent Orders for each CustomerID:

SELECT O2.CustomerID, MAX(O2.OrderDate) AS most_recent_order_date
FROM Orders AS O2
GROUP BY O2.CustomerID

I use the table correlation name 'O2' because usually the next step is
a self join to get other details about the order e.g. the employee who
most recently took an order for each customer:

SELECT O1.CustomerID, O1.EmployeeID, DT1.most_recent_order_date
FROM Orders AS O1
INNER JOIN
(
SELECT O2.CustomerID, MAX(O2.OrderDate) AS most_recent_order_date
FROM Orders AS O2
GROUP BY O2.CustomerID
) AS DT1
ON O1.CustomerID = DT1.CustomerID

You could then use the above to update another table e.g. (aircode;
would fail in Access/Jet due to daft 'Operation must use an updateable
query' error):

UPDATE Customers
SET LastOrderTakerEmployeeID =
(
SELECT O1.EmployeeID
FROM Orders AS O1
INNER JOIN
(
SELECT O2.CustomerID, MAX(O2.OrderDate) AS
most_recent_order_date
FROM Orders AS O2
GROUP BY O2.CustomerID
) AS DT1
ON O1.CustomerID = DT1.CustomerID
WHERE Customers.CustomerID = O1.CustomerID
)
WHERE EXISTS
(
SELECT *
FROM Orders AS O1
INNER JOIN
(
SELECT O2.CustomerID, MAX(O2.OrderDate) AS
most_recent_order_date
FROM Orders AS O2
GROUP BY O2.CustomerID
) AS DT1
ON O1.CustomerID = DT1.CustomerID
WHERE Customers.CustomerID = O1.CustomerID
);

No recordsets, no looping, no sorting, no seeking.

....but it would beg the question why one would store the most recent
value rather than query it on demand but then mine is merely a simple
example

Jamie.

--

 

[Jamie Collins]

I appreciate your alternate code example. My requirements are really to sort
a history table (recordset) by the autonumber field so the records are in the
order entered, not in the order that Access uses by default when openeing a
recordset (indexed field, ascending). After sorted I need to find the LAST
entry of a particular Serial Number, for every Serial Number found in the

How do you tell which is the LAST row for a given serial number? I
previously assumed you had a timestamp (DATETIME) column but it sounds
like you have a sequential autonumber. I don't think your design is
sound; I you ever get someone like me as a system tester the first
thing I'll do is insert really large positive and negative numbers
into your autonumber columns (then I'll put Unicode into your Unicode
columns...). Even the autonumber advocates (I am not one) say you
should never attach 'meaning' to autonumber values.

Risks of autonumber aside, simply substitute MAX(O2.OrderDate) with
MAX(autonumber_column) in the examples I posted upthread.

Any ideas on how to get a .Seek and
.Sort to work in the same Subroutine?

Let's compare your approach to mine:

Your approach involves several hundred lines of VBA; mine is a single
SQL statement.
Your approach executes in the middleware (slow); mine executes in the
engine (fast).
Your approach involves a number of steps: retrieving, sorting,
seeking, iterating, copying, updating; mine is a single SQL statement
and the SQL engine does its own retrieving, sorting, seeking,
iterating, copying, updating or whatever it sees fit.
Your approach I've encountered many times and it's a screaming PIA to
code and maintain; you've not tried mine yet <g>.
You can't get yours to work!

I urge you to give my approach further consideration. You can do this
in one UPDATE statement, you really can! I tell you what: post the
structure of the tables involved and I'll take the time to figure out
the Access/Jet syntax for the UPDATE, unless someone else beats me to
it.

But if you really want to do it your way, I suggest using ORDER BY in
your SQL when you grab the tables and using Filter rather than Seek on
the recordset.

Jamie.

--

 

[Brendan Reynolds]

It looks like the example code you sent me contains only a Seek. I have my
Seek working properly already but run into problems when I try to add a
.Sort
or try to use a mySQL to sort the recordset. My problem is getting the
.Seek
and .Sort to work in the same subroutine. The reason I need the sort is to
open the recordset in the order it was created (by autoumber) NOT
ascending
by serial number (default). Once the recordset is sorted by automumber I
can
.Seek for the desired Serial Number and use adSeekLastEQ to finds the last
entry of that Serial Number in the history recordset. Because it is a
History
I need the most recent data that was entered for that particular Serial
Number. Any ideas on getting the recordset sorted prior to .Seek?

I'm no expert on ADO, but as far as I can tell, it would seem that
CursorLocation needs to be set to adUseServer to use Index and Seek, but to
adUseClient to use Sort, so I don't think you're going to be able to use
both of them with the same recordset. I've included some examples below,
notice how the CusorLocation needs to be different for the two examples.

The advice to sort using the ORDER BY clause of the SQL statement whenever
possible is good advice, if you can find a way to do it.

Failing that I think you'd have to use Filter or Find rather than Seek.

Sub LinkTableSeek()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\Users\Brendan Reynolds\Documents\testdb.mdb"
.Open
End With
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "Customers"

.CursorLocation = adUseServer

.CursorType = adOpenKeyset
.Open Options:=adCmdTableDirect
.Index = "Last Name"
.Seek "Lee"
If (Not .EOF And Not .BOF) Then
MsgBox rs.Fields("First Name").Value
Else
MsgBox "Record not found"
End If
.Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub

Sub LinkTableSort()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\Users\Brendan Reynolds\Documents\testdb.mdb"
.Open
End With
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "Customers"

.CursorLocation = adUseClient

.CursorType = adOpenKeyset
.Open Options:=adCmdTableDirect
'.Index = "Last Name"
'.Seek "Lee"
.Sort = "[First Name] DESC"
Do Until rs.EOF
Debug.Print .Fields("First Name")
.MoveNext
Loop
' If (Not .EOF And Not .BOF) Then
' MsgBox rs.Fields("First Name").Value
' Else
' MsgBox "Record not found"
' End If
.Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub