Seek on SQL 2000 Table

[katdav]

I've converted an MDB Data database to SQL 2000 and I'm accessing the data with an ADP. With the original Code MDB to Data MDB I could use Seek against the data mdb by opening a reference to the data mdb. Can I use Seek against the SQL 2000 tables? I can't figure out how to do this. I'm sure it's in the provider info somewhere but just can't find it. I usually get a message about the Provider not supporting Index. Thanks for assistance. David.

 

[cmmrfrds]

Can you show the code you are trying to use and the error message(s).

 

[katdav]

You bet:

Dim cnnx as New ADODB.Connection
Dim tblB as ADODB.Recordset

Set tblB = New ADODB.Recordset

cnnx.Provider = "sqloledb"
cnnx.Properties("Data Source&quot.value = "LocalServer"
cnnx.Properties("Initial Catalog&quot.value = "DataDBSQL"
cnnx.Properties("Integrated Security&quot.value = "SSPI"
cnnx.CursorLocation = adUseServer
cnnx.Open

tblB.Open "tblFormSecurity", cnnx, adOpenKeyset, adLockOptimistic, adCmdTable

tblB.Index = "PrimaryKey"

on the Index code I get a message about Index not being supported by the Provider. PrimaryKey does exist on the table.

I've tried sqloledb, MSDASQL as a Provider
I've tried tblB.CursorLocation = adUseServer

x = tblB.Supports(adIndex) returns False
x = tblB.Supports(adSeek) returns False

Thanks so much for your help.

Within Access, it was pretty much considered that Seek was not available to an attached table - not really true since you can open the MDB directly and perform a Seek on a table. Documentation was not really around for this scensario. Is this the same sort of thing I wonder?

David

 

[cmmrfrds]

I don't have much time now but I will give you an example opf a typical connection string to sql server against the pubs database.

Dim cn As New adodb.Connection
Dim rs As Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

This doesn't look correct, normally the local PC is local host.
cnnx.Properties("Data Source&quot.value = "LocalServer"
cnnx.Properties("Data Source&quot.value = "LocalHost"? OR
cnnx.Properties("Data Source&quot.value = "Local Host"?

The "Filter method" replaces seek, find, etc..

tblB.Filter = "yourfield = 1"
To turn off filter
tblB.Filter = adFilterNone '- look this up, it is close but may be adUseFilterNone

 

[katdav]

Thanks for the info. I actually have the .Filter method working but I wanted to use .Seek

It's a bit hard to find info on this but I'm under the impression that seeks are much faster against large tables - that indexes are used. I'm not sure about .Filter - does it use indexes?

Thanks, David

 

[cmmrfrds]

Yes, the filter can use an index. The Filter is the preferred Method in ADO and is very flexible. If you need additional speed on an ADO recordset, you can apply a temporary index to the recordset. I don't have the syntax with me at work, but if I have time tonight I could look it up when I am home. Basically, once the ADO recordset is in memory then set a field as the index and do the filtering on the indexed field.

 

[katdav]

Thanks, I'll have a look for into on the .Filter index stuff.

But further, can you confirm that Seek cannot be used?

Thanks, David

 

[cmmrfrds]

There is a Seek Method in ADO, but it may not be implemented by all providers. My guess is that the sql server provider does not implement Seek.

 

[cmmrfrds]

Here is the syntax to set an index on a recordset field.

tblB("yourfield&quot.Properties("OPTIMIZE&quot = True
tblB.Filter "yourfield = 10"

When you use yourfield in a filter it will be an optimized search. Of course, there is a little overhead in creating the index but it is quite small.

 

[katdav]

Excellent, thanks so much. David