G
Guest
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
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