Retrieving data, looping over queries

[chazewalker]

hi! I'm new in here as well as in VBA programming...

I have an ADO connection on my excel to access, connection works fine but when i try to loop on the query, I got this error:

Run-time error '3705'
Operation is not allowed when the object is open.

I want the macro to retrieve data from access database by specific cells.
Help me plz..

here's my code:

startRow = 7
here:

rs.Open "SELECT * FROM tblSample WHERE item_name = '" & ActiveSheet.Cells(startRow, 2) & "'"     '<<--here i got the error..
Do Until rs.EOF
   ActiveSheet.Cells(startRow, 2) = rs.Fields(2).Value
   ActiveSheet.Cells(startRow, 7) = rs.Fields(3).Value
   ActiveSheet.Cells(startRow, 6) = rs.Fields(5).Value
   rs.MoveNext
  startRow = startRow + 1
Loop

GoTo here

thanks in advance!

 

[Fazza]

Hi,

Try ActiveSheet.Cells(startRow, 2).value

If still not good, add a debug.print & review the SQL

HTH

regards

 

[Fazza]

Another thought, does the code run OK without the WHERE clause?

SELECT * FROM tblSample

 

[Ruddles]

You appear to be opening the recordset with rs.Open and then looping round and doing another rs.Open whilst the recordset is still open.

Close the recordset with an rs.Close before you loop round and try to open it again.

 

[Sektor]

Is "item_name" char or numeric?