Adodo problem

[Moss100]

Hello, I want to connect to and external DB (on our own network)

I have succeeded in this, but am having trouble filtering and sorting the results

If I use the code:

If .RecordCount > 0 Then
rs.Filter = "[Type] LIKE '*" & StrTypeNeeded & "*'"
Set Me.Recordset = rs

Then I get all results. But adding in a SORT into this causes fewer restults to be returned. What is my problem?

If .RecordCount > 0 Then
rs.Filter = "[Type] LIKE '*" & StrTypeNeeded & "*'"
rs.Sort = "[date] desc"
Set Me.Recordset = rs

Also:

If I wanted to use an exact string rather than a LIKE, how would i phrase this:

I have definded StrTypeNeeded as a string variable

I have tried the below, but it errors

If .RecordCount > 0 Then
rs.Filter = "[Type] = StrTypeNeeded"
rs.Sort = "[date] desc"
Set Me.Recordset = rs

Thank you for any help

 

[strongm]

Try

Set Me.Recordset = rs.OpenRecordset

 

[Moss100]

Thanks, but no that just causes an error.

The problem occurs when I add a sort or filter


If .RecordCount > 0 Then
rs.Filter = "[Type] LIKE '*" & StrTypeNeeded & "*'"
rs.Sort = "[date] desc"
Set Me.Recordset = rs

So if i remove either the rs.filter or rs.sort then it works - it seems to be a problem when I use them both.

Thanks anyway. Mark

 

[Moss100]

I see its a limitation / bug in ado. It will only return 100 records when filter and sort are used at the same time.

 

[dhookom]

Consider using DAO which has always worked better (IMHO).

If .RecordCount > 0 Then
rs.Filter = "[Type] = '" & StrTypeNeeded & "'"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

>Consider using DAO

... which is what I assumed was being used, as I clearly didn't read the question title properly.

 

[Moss100]

I use ado because I want the connection to the external database to be as short as possible, to stop it affecting the external database ability to update.

I don’t think I can create a quick on/off connection by dao can I?

The other option I thought was to transfer the unfiltered ado recordset to a local table (with same structure) and then use the data from that table.

Is that a possible option? If so could you provide sample code to do this?

Thank you Mark.

 

[dhookom]

I haven’t had issues with simply setting the RecordSource property to an SQL statement.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016