SQL Server中是否有类似VB ACCESS DAO.Recordset索引定位遍历的等效方法？

Great question! I’ve been there too—moving from old DAO/Access patterns to modern SQL Server can feel like a bit of a shift. Let’s break down how to replicate that index-based record navigation behavior you’re used to.

1. Use SQL Queries to Leverage Indexes Directly #

Instead of manually setting an index and calling Seek() like in DAO, modern databases prefer you express your intent through SQL. The database optimizer will automatically use your existing IndexIW (assuming it’s a composite index matching your filter columns) to speed up the lookup.

Example: Replicate the Seek Behavior #

Your DAO code targets records where the index columns match "Item 17a" and "ABC". The equivalent SQL query would be:

SELECT * 
FROM ITEMS 
WHERE Column1 = 'Item 17a' AND Column2 = 'ABC';

(Replace Column1 and Column2 with the actual columns in your IndexIW index, keeping the same order as the index definition.)

If you want to force the use of IndexIW (though the optimizer usually picks the best index on its own), you can add a hint:

SELECT * 
FROM ITEMS WITH (FORCE INDEX (IndexIW))
WHERE Column1 = 'Item 17a' AND Column2 = 'ABC';

2. Traverse Records in Index Order #

To replicate the MovePrevious()/MoveNext() behavior along the index sequence, you have two main options:

Option 1: Query for Adjacent Records #

Once you’ve located your target record, you can fetch the previous/next records in index order with targeted SQL:

-- Get the next record in IndexIW order
SELECT TOP 1 * 
FROM ITEMS 
WHERE Column1 > 'Item 17a' 
   OR (Column1 = 'Item 17a' AND Column2 > 'ABC')
ORDER BY Column1, Column2;

-- Get the previous record in IndexIW order
SELECT TOP 1 * 
FROM ITEMS 
WHERE Column1 < 'Item 17a' 
   OR (Column1 = 'Item 17a' AND Column2 < 'ABC')
ORDER BY Column1 DESC, Column2 DESC;

Option 2: Use Server-Side Cursors (For Cursor-Style Navigation) #

If you need the exact "cursor-like" experience of DAO, you can use server-side cursors in libraries like ADO.NET. Note that this is less common in modern apps since it ties up server resources, but here’s how it might work:

using (var conn = new SqlConnection("your_connection_string"))
{
    conn.Open();
    
    // Create a command that returns records in IndexIW order
    var cmd = new SqlCommand("SELECT * FROM ITEMS ORDER BY Column1, Column2", conn);
    
    // Use a keyset cursor to enable backward navigation
    var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
    
    // Locate your target record
    while (reader.Read())
    {
        if (reader["Column1"].ToString() == "Item 17a" && reader["Column2"].ToString() == "ABC")
        {
            break;
        }
    }
    
    // Move to the previous record (if supported by the cursor type)
    if (reader.HasRows && !reader.IsClosed)
    {
        reader.ReadPrevious();
    }
}

3. Why You’re Seeing API Errors #

The errors you’re hitting make total sense! DAO was tightly coupled to Access’s Jet engine, which let you directly manipulate indexes at the recordset level. Modern SQL Server clients (like ADO.NET, ODBC, or OLE DB) don’t expose this kind of API because index management is handled by the database optimizer, not the client.

If you were trying to use something like the old ADO Recordset.Index property with SQL Server, that’s the root cause—SQL Server’s drivers don’t support that Jet-specific feature.

内容的提问来源于stack exchange，提问作者Michael