getting data from access

[Guest]

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.

 

[paul.robinson]

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

 

[Ron de Bruin]

Hi Mr. Know Nothing

See this page
http://www.rondebruin.nl/accessexcel.htm

 

[Guest]

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.

 

[Guest]

Nothing helpful there.

Thanks anyways.