Seek in a SQL table from Access

WesJD

I have an MS Access 2003 database from which I want to seek a specific record in a SQL Server Express 2005 database. I can connect to the table and get a recordcount but the recordset.supports (adseek) and recordset.Supports(adIndex) both return false. Any suggestions Specific code I'm using is as follows:

Dim cnxn As ADODB.Connection
Dim strCnxn As String
Set cnxn = New ADODB.Connection
cnxn.Provider = "sqloledb"
strCnxn = "Data Source=SERVER\SQLEXPRESS2005;Initial Catalog=RAMPSQL;Integrated Security='SSPI';"

cnxn.Open strCnxn

Set rsWSC = New ADODB.Recordset
rsWSC.CursorLocation = adUseServer
strSQL = "DailyData"
rsWSC.Open strSQL, cnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect

Thank you!

Andrea Montanari

hi Wes,

SQL Server does not like "seek" operations in the relative returned rowset... you should migrate, changeing mind about the "scrollable" features... so instead of executing SELECT * FROM MillionsRowsTable; prefer a well suited SELECT <col_List> FROM MillionsRowsTable WHERE <adegaute search condition(s)>;

regards

Arnie Rowland

Wes,

You may have better luck getting a good response by posting in one of the Access related forums.

WesJD

Thank you, Andrea, I assumed that a "seek" operation on an indexed table would be significantly faster than the method you suggest. Is that not correct (I'm targeting the entire table in the recordset because Access says that seek will only work in table direct mode, not on a query.)

Mike Wachal - MSFT

Hi Wes,

The provider doesn't support indexes or seek functionality, so you'll need to use some other mechanism. Andrea offers a good suggestion of targeting your select statement to exactly what you're looking for. If you have very large records, it may even be worth narrowing it even further to doing an initial query based on a clustered index and then run the full query only if the first query returns results, indicating that a record exists. I'm sure others have additional ideas on how to optimize queries.

Mike