I dont know whats wrong with this....

[ryan.fitzpatrick3]

I have this code below which pulls information out of access onto a
sheet in my workbook

Sub Access_Data()

Dim nResult As Long
nResult = MsgBox( _
Prompt:="Do you want to pull Information?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
'Rest of macro macro

End If
If nResult = vbNo Then
Exit Sub
End If

Dim conn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim strPath As String

strPath = "C:\Documents and Settings\rfitz03\Desktop\reports.mdb"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strPath & ";"
conn.CursorLocation = adUseClient

' Create a Recordset from all the records
' in the Products table

Set rst = conn.Execute(CommandText:="tblQryAllSupplyPlantFlour", _
Options:=adCmdTable)

' begin with the first record
rst.MoveFirst
' transfer the data to Excel
' get the names of fields first
With Worksheets("Pulled from Access").Range("A6")
.CurrentRegion.Clear
For j = 0 To rst.Fields.Count - 1
.Offset(0, j) = rst.Fields(j).Name
Next j
.Offset(1, 0).CopyFromRecordset rst
' .Offset(1, 0).CopyFromRecordset rst, 5
' .Offset(1, 0).CopyFromRecordset rst, , 2
.CurrentRegion.Columns.AutoFit
End With
rst.Close
conn.Close
End Sub

Now I used to have a table that it pulled from but I have a query that
it pulls from. Now it gives me an error.

It says " Either BOF or EOF is true, or the current record has been
deleted. requested operation requires current record"

What does this mean? Does this mean I need to pull the table instead
of query? If so, I want to have the macro I created run the query and
dump that info onto my sheet is that possible?

Ryan

 

[Tim Williams]

Worked for me using a generic query in Access, so maybe there's something
odd about your query ?

It's always worth adding code the check for EOF whenever you open a
recordset using ADO.

If not rst.EOF then
'use results
Else
'no results !
End if


Tim