CODEWITHSUNDEEP
ms-accessvba
Colin
Colin

Reputation: 73

error code 3021 either bof or eof is true or the current record has been deleted

I have an Access 2003 database with some visual basic code using ADO calls in it. When I do a 

strsql0 = "SELECT lnk_stockitm.C_C FROM lnk_stockitm WHERE (((lnk_stockitm.C_C) Like 'T*'));"
newRS.Open strsql0, cn1, adOpenKeyset, adLockReadOnly  
newRS.movelast

I get this error:

3021 either bof or eof is true or the current record has been deleted

When I run the exact same query in the same function without the WHERE clause, like this:

strsql0 = "SELECT lnk_stockitm.C_C FROM lnk_stockitm;

I get the correct result of 56,000 records. If I paste the full SQL statement with the WHERE clause into a regular query, like so:

SELECT lnk_stockitm.C_C FROM lnk_stockitm WHERE (((lnk_stockitm.C_C) Like 'T*'));

it returns the correct subset of the results (2800 records).

Can anyone tell me what I am doing wrong?

Upvotes: 3

Views: 10345

Answers (2)

Jakkwylde
Jakkwylde

Reputation: 1302

The wildcard difference is the cause for difference between what you execute from ADO and within your access database. Convert your statement to use "%" rather than "*". As a general rule of thumb, it may be a good idea to encapsulate your code by checking for eof before calling MoveLast. If your query has zero results it'll bomb out every time.

strsql0 = "SELECT lnk_stockitm.C_C FROM lnk_stockitm WHERE (((lnk_stockitm.C_C) Like 'T*'));"
newRS.Open strsql0, cn1, adOpenKeyset, adLockReadOnly  

if not newRs.eof then
   newRS.movelast
else
  ' do something here if necessary to handle blank results
end if

Upvotes: 5

Heath Hunnicutt
Heath Hunnicutt

Reputation: 19457

You need to use the '%' character as wildcard when using ADO.

MSDN Article: Using the Right Wildcard Characters in SQL Statements

Upvotes: 5

Related Questions