Hello:
Here is code I am using to retrieve data from Access 2003. Problem: the
headers are being copy/pasted into the sheet, however no other data is. (See
the "Dump the contents paragraph.)

Also, no errors are being generated. I have turned on the references for
the Office 11 (Office, Access, Excel), VBA and ActiveXData Object 2.5
Library. An error WAS being generated when the ADO 2.7 library was checked.
(This code comes from wrox's Xls 2002 VBA book--I am running Office 2003.)

There is apparently some small difference here between xls 02 and 03.
Hoping to avoid going out to get the '03 book.

Public Sub GetMainCurrentQuery()

Dim objField As ADODB.Field
Dim rsData As ADODB.Recordset
Dim lngOffset As Long
Dim strConnect As String

'Create the connection string
strConnect = "Provider = Microsoft.jet.OLEDB.4.0;" & _
"Data Source = [My Data Source];"

'Create the Recordset object and run the query
Set rsData = New ADODB.Recordset
rsData.Open "([My Select Query])", strConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdTable

'Make sure we got the records
If Not rsData.EOF Then
'add headers to the worksheet
With Sheet1.Range("a1")
For Each objField In rsData.Fields
.Offset(0, lngOffset).Value = objField.Name
lngOffset = lngOffset + 1
Next objField
.Resize(1, rsData.Fields.Count).Font.Bold = True
End With

'Dump the contents of the recordset onto the worksheet
Sheet1.Range("a2").CopyFromRecordset rsData

'Fit the colum widths to fit the data
Sheet1.UsedRange.EntireColumn.AutoFit

Else
MsgBox "Something's up. No records were returned.", vbCritical

End If

'Close the recordset
rsData.Close
Set rsData = Nothing

End Sub

Many thanks

If you are using a query rather than a table name then omit adCmdTable.
Jamie.

--

Hi Jamie:
Good guess, but actually that didn't solve this. I tried all of the other
options for this piece of the .Open method and none of them work. The only
solution was to make a talbe out of my query.

I would prefer to run this from a query--rather than add more step to a long
macro. Does anyone know how this is done using Xls and Access 2003?


"onedaywhen" wrote:

If you are using a query rather than a table name then omit adCmdTable.
Jamie.

--