By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Issue with Select not returning data to recordset

P: 4
I am somewhat new to Access and I am trying to create a recordset to get data from the local access db. I get an error stating "Either BOF or EOF is True, or the current record has benn deleted..." I know the query I am selecting from has data as I run the same query in the query editor and get data. Please let me know what I am doing wrong. (Using Access 2003) The test function is listed below:

Expand|Select|Wrap|Line Numbers
  1. Public Function test1() As Integer
  2. On Error GoTo Err_test1
  3.  
  4. Dim rst1 As ADODB.Recordset
  5. Set rst1 = New ADODB.Recordset
  6.  
  7. strSQLStmt = "SELECT SystemAcronym, Control_ID, RA_Likelihood, RA_Impact, " & _
  8.              "RA_Risk, RA_Title, RA_Summary, RA_TandVAssessment, RA_Recommendation " & _
  9.              "FROM qryTechWriteUps;"
  10.  
  11.  
  12.     With rst1
  13.         .Source = strSQLStmt
  14.         .ActiveConnection = CurrentProject.Connection
  15.         .Open Options:=adCmdTxt
  16.         .MoveFirst
  17.     End With
  18.  
  19.     MsgBox rst1!SystemAcronym
  20.  
  21. Exit_test1:
  22.     Set rst1 = Nothing
  23.     Exit Function
  24.  
  25. Err_test1:
  26.     MsgBox Err.Number & "~" & Err.Description & "~" & Err.Source
  27.     Resume Exit_test1
  28.  
  29. End Function
  30.  
Sep 17 '07 #1
Share this Question
Share on Google+
7 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, Chris.

Your code has to work as soon as query returns records.
I've not reproduced your situation.
Did you tried to run exactly the same string in query editor?
Sep 17 '07 #2

P: 4
I did a copy and paste into the query editor, removed the quotes and line breaks and it returns 2 rows. I have been beating my head against the wall trying to figure this out.

Hi, Chris.

Your code has to work as soon as query returns records.
I've not reproduced your situation.
Did you tried to run exactly the same string in query editor?
Sep 17 '07 #3

FishVal
Expert 2.5K+
P: 2,653
I did a copy and paste into the query editor, removed the quotes and line breaks and it returns 2 rows. I have been beating my head against the wall trying to figure this out.
Well.

First I thought that using default adOpenForwardOnly recordset cursor disables MoveFirst method. But it works fine. Moreover I've copypasted your code into my draft db, changed SQL string to match one of my table and it worked fine again.

So I don't have clues so far.
BTW disable error handling and determine there does code execution stop.
Sep 17 '07 #4

P: 4
Figured out something: In my access query, I have a Criteria that uses 'Like 'TECH*' It returns rows if I execute that query. Now if I change it to LIKE 'TECH%' it does not return rows if I execute the query but the vb code gets records back. Any Idea as to why this is happening?


Well.

First I thought that using default adOpenForwardOnly recordset cursor disables MoveFirst method. But it works fine. Moreover I've copypasted your code into my draft db, changed SQL string to match one of my table and it worked fine again.

So I don't have clues so far.
BTW disable error handling and determine there does code execution stop.
Sep 17 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Figured out something: In my access query, I have a Criteria that uses 'Like 'TECH*' It returns rows if I execute that query. Now if I change it to LIKE 'TECH%' it does not return rows if I execute the query but the vb code gets records back. Any Idea as to why this is happening?
Much more clear now. :)

Access SQL dialect supports "*" wildcard.
"%" wildcard is being supported by ANSI SQL.

You may change SQL syntax to ANSI to run your query with "%" wildcard in Access (as is in builder, or as RecordSource/RowSource). Tools -> Options -> Tables/Queries -> SQL server compatible syntax (ANSI 92).

Or use DAO recordset in you code to open query with "*" wildcard.
Sep 17 '07 #6

P: 4
Thank You, This is now working. It makes it tough to mixing ANSI 89 with ANSI 92
Much more clear now. :)

Access SQL dialect supports "*" wildcard.
"%" wildcard is being supported by ANSI SQL.

You may change SQL syntax to ANSI to run your query with "%" wildcard in Access (as is in builder, or as RecordSource/RowSource). Tools -> Options -> Tables/Queries -> SQL server compatible syntax (ANSI 92).

Or use DAO recordset in you code to open query with "*" wildcard.
Sep 17 '07 #7

FishVal
Expert 2.5K+
P: 2,653
Thank You
Not for. You've solved it yourself
This is now working. It makes it tough to mixing ANSI 89 with ANSI 92
Add BUG2000 and Access2007 to the list. Both Microsoft(R). :)

You are welcome.

Regards,
Fish
Sep 17 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.