Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bettergains
 
Posts: n/a
Default Problem Code: Retrieving Stored Access 03 Query

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





  #2   Report Post  
onedaywhen
 
Posts: n/a
Default

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

--

  #3   Report Post  
Bettergains
 
Posts: n/a
Default

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.

--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Query John Links and Linking in Excel 4 December 7th 04 03:49 PM


All times are GMT +1. The time now is 12:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"