replacing DAO findfirst in ADO using .Filter

[bakershawnm]

thread705-863343

I read through this thread and I am already doing this (using a laptop duocore w/4gb ram). The problem I am having now is that my tables are so hugh (some in excess of 200k records in sql tables) that it takes forever just to execute the .filter. Is there a faster way to do this?

Even if the solution is something other than ADO (Access).

 

[Remou]

Why not just select the records you want in the first place? You may also need indexes.

 

[bakershawnm]

So, I use the seek and indexes and it works much faster. However it only works on Access tables.

The following code produces the 'does not' message


Private DynmxCnxn As New ADODB.Connection (globally defined)

Dim rstPSpuren As ADODB.Recordset

DynmxCnxn.ConnectionString = "Provider='MSDASQL.1';Persist Security Info='False'; " & _
"Data Source='SMTSPC';Initial Catalog='SMTSPC'"
DynmxCnxn.Open

Set rstPSpuren = New ADODB.Recordset

rstPSpuren.Open "PSPUREN", DynmxCnxn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

If rstPSpuren.Supports(adIndex) And rstPSpuren.Supports(adSeek) Then
MsgBox "does support index or seek"
GoTo Exit_retrieval
Else
MsgBox "does support index or seek"
GoTo Exit_retrieval
End If


This code is what I use for the local Access tables:

.Index = "Lvl Key"
.Seek Array(compare, machine, table, track, level, rundate), adSeekFirstEQ

Any suggestions for what to use to get to a MSSQL ODBC connected table?

I know MSSQL uses indexes because I have seen them. So I am under the impression that it is the seek from ado that it doesn't support. I have not checked that specifically yet.

 

[Remou]

Here is a wonderful resource for connection strings:

http://www.connectionstrings.com/

Why not :

strSQL="SELECT List, Field, Here FROM TableHere " _
& "WHERE machine='x' AND table Like 'y*' AND track=1 AND " _
& "rundate=#2008/12/31#"

rs.Open strSQL

 

[bakershawnm]

Thanks for the resource. Pretty extensive.

I tried the SQL Select before I moved on to the seek and the select was not much faster than the .filter.