getting data from access

M

Mr. Know Nothing

Hey all, I am trying to get data from access and inserting it in excel. I
have multiple table and multiple queries. I did get one query to work but now
on my second I seem to have a problem that i can find.

Sub ProcessRecords()

Dim i As Integer
i = 1

'declares connection and record set
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'initialize connect and recordset
Set cn = New Connection
Set rs = New Recordset

cn.Provider = "Microsoft.jet.oledb.4.0"
cn.ConnectionString = "Data Source = S:\12 Students NOPP8\CO31Emile Boudreau
(Jan2006-Apr 2006)\Indmon 2005.mdb"

cn.Open
rs.Open "select * from [Production]", cn

Sheets("Sheet2").Select
Range("A1").Select

Do While rs.EOF <> True
ActiveCell.Value = rs.Fields("MILL_ID").Value ' from = to
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = rs.Fields("P_TOTALE").Value
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = rs.Fields("P_DATE").Value

i = i + 1
Range("A" & i).Select

rs.MoveNext
Loop

rs.Close

Sheets("Sheet2").Name = "Production"

cn.Close

Set rs = Nothing
Set cn = Nothing
End Sub

Now i know that i have information because in access i see it and the query
gets data in access but when i try to run in in VB "rs" has no records in it.
(rs.EOF = true) This makes no sense as i know it should pick data up.

Does anyone have an idea of what is going wrong here?

thanks in advance for the help.
 
P

paul.robinson

Hi
Maybe the cursor is at the end of the table? did you try an
rs.movefirst before the while?
Paul
 
M

Mr. Know Nothing

yup tried that and it gives me:

run-time error '3021'
Either BOF or EOF is true, or the current record has been deleted.
Requested operation requires a current record.

it's just like if the query is not grabbing any recordset.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top