Fix ERROR_CANTFETCHBACKWARDS 0X00000302 in ODBC apps

Cause 1: Your query uses a forward-only cursor by default

This is the most common reason you see error 0X00000302. When you query a database through ODBC, the driver often uses a forward-only cursor. That cursor only lets you read rows from first to last, one at a time. If your code tries to go back—say, with SQLFetchScroll(SQL_FETCH_PRIOR) or SQLFetchScroll(SQL_FETCH_ABSOLUTE, -1)—the driver slams the door and throws ERROR_CANTFETCHBACKWARDS.

I see this all the time in older Visual Basic 6 apps, Excel VBA macros using ADO, and custom C++ programs that call ODBC directly. The programmer assumes the result set is fully buffered and scrollable. It isn't.

How to fix it

You have two choices. The quick one: stop trying to fetch backwards. The better one: tell the driver to use a scrollable cursor. Here's how.

1. Change the cursor type in your connection string or statement attribute.
   If you're using ADO (common in VBA), set the CursorLocation to adUseClient and the CursorType to adOpenStatic or adOpenKeyset. Example in VBA:

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.Open "SELECT * FROM Orders", conn

If you're using raw ODBC in C, call SQLSetStmtAttr before executing the query:

SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, 0);

After you set that, the driver will buffer the entire result set. You can then fetch forward, backward, or jump to any row. Expect the query to take a little longer to return, because the driver fetches all rows upfront.

2. If you can't change the cursor, rewrite your logic.
   Process rows in a single forward pass. Store the data you need in an array or a temporary table, then work from that. It's more work, but it keeps the query fast and avoids the error entirely.

One more thing: some ODBC drivers—like the old Microsoft Access driver (Jet)—simply don't support scrollable cursors at all. In that case, your only fix is option 2.

Cause 2: The data source doesn't support scrollable cursors

Even if you set the cursor type correctly, the underlying data provider might tell ODBC, "Nope, can't do that." This happens with certain database systems, especially older ones or read-only drivers. For example, the Excel ODBC driver (via Microsoft.ACE.OLEDB.12.0) does not support backward scrolling. Neither do many CSV or text file drivers. The driver simply refuses.

How to fix it

First, check if your driver supports scrollable cursors. You can do this by looking at the driver's documentation or running a quick test. In ODBC, call SQLGetInfo with SQL_SCROLL_OPTIONS. If the returned value doesn't include SQL_SO_STATIC or SQL_SO_KEYSET_DRIVEN, you're out of luck.

The fix is almost always to move the data to a real database like SQL Server Express, SQLite, or even a local Access database (Jet supports static cursors). Here's a concrete example:

1. Import your Excel or text data into a SQL Server table using the Import and Export Wizard.
2. Point your ODBC connection at that SQL Server database.
3. Set your cursor to static (as in Cause 1).

That's it. You'll never see the error again with that driver.

If moving the data isn't possible, you'll have to read all rows into a local data structure in one forward pass. Then you can scroll through your local copy. It's not elegant, but it works.

Cause 3: You're using SQLFetchScroll with an unsupported fetch orientation

Less common, but I've seen it. Some ODBC drivers support scrollable cursors but only certain fetch modes. For instance, a driver might support SQL_FETCH_NEXT and SQL_FETCH_PRIOR but not SQL_FETCH_ABSOLUTE or SQL_FETCH_RELATIVE with negative values. When you call SQLFetchScroll(hstmt, SQL_FETCH_PRIOR, 0), you're asking for the row before the current one. If that orientation isn't supported, you get error 0X00000302.

How to fix it

1. Check the driver's documentation for which fetch orientations are supported. Microsoft's SQL Server ODBC driver (version 17 and up) supports all orientations. Older drivers like the SQL Server 2000 driver might not.
2. Switch to a supported orientation. If you need to go backward, use SQL_FETCH_PRIOR (which is usually supported). Avoid negative offsets with SQL_FETCH_ABSOLUTE unless you've confirmed the driver handles them.
3. Test with a simple program to see which orientations work. Here's a quick C snippet:

SQLHSTMT hstmt;
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, 0);
SQLExecDirect(hstmt, (SQLCHAR*)"SELECT * FROM MyTable", SQL_NTS);
// Try fetching backward
retcode = SQLFetchScroll(hstmt, SQL_FETCH_PRIOR, 0);
if (retcode == SQL_ERROR) {
    // Handle error 0X00000302
}

If that fails, try SQL_FETCH_FIRST and SQL_FETCH_LAST to see if positional fetches are the problem. If those work, you can work around the restriction by fetching first/last and then scrolling forward normally.

Quick-reference summary

That's the whole story on error 0X00000302. Nine times out of ten, you just need to change your cursor type. The other cases are rarer but still solvable. Don't forget to test your fix with a small subset of data first—saves you from a production headache.