SQL Query to Excel
No joy there, Mike. It just caused another error. But I think as I REALLY do
not want all 120+ fields, I have found that building the query for the 20 or
so fields I need with a SELECT statement works ok. But it is curious
behavior, non-the-less.
Thanks to all of you who helped get me going!
Patk
"Mike" wrote:
This a shot in the dark but try this or below is what I use. Just change a
few this and it should work for you.
Hope this helps.
Sub WSPopulate()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strWhere As String
Set con = New ADODB.Connection
Set rs= New ADODB.Recordset
con.Open "Driver={SQL Server};Server=GVS00534\node1;Database=mydb;"
strWhere = "SELECT * FROM dbo.application WHERE Owner_L2 = 'sbcorg'"
rs = con.Execute(strWhere, , 1)
'Set rs = con.Execute(strWhere, , 1)
Range("a1").CopyFromRecordset rs
rs.Close
con.Close
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''
Sub getDataFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Const shName As String = "Sheet1"
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String, sProvider, sDataSource, strConn
Dim iCol As Long
Dim wks As Worksheet
Set wks = Worksheets(shName)
'Clear sheet before refresh
wks.Cells.ClearContents
'use ACE for 2007 mdb or less
'sProvider = "Provider=Microsoft.ACE.OLEDB.4.0; "
'use Jet for 2003 mdb or less
sProvider = "Provider=Microsoft.Jet.OLEDB.4.0; "
'change data source with the path to your database
sDataSource = "Data Source=C:\ilsa\data\Ilsa.mdb;Persist Security
Info=False"
strConn = sProvider & sDataSource
'sSQL = "Replace with your query"
sSQL = "SELECT AdjustLog.* FROM AdjustLog;"
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
iCol = 1
For Each fld In rs.Fields
wks.Cells(1, iCol) = fld.Name
iCol = iCol + 1
Next
wks.Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub
"PatK" wrote:
I shall try and see how it goes. Ultimately, I selected the 20+ fields I
really needed, in the SQL Select statement, and that worked fine. I just
coudl not do * in the select statement. Thanks for the help, all.
Patk
"Dick Kusleika" wrote:
On Wed, 23 Jul 2008 11:53:01 -0700, PatK
wrote:
Well..that helped part of the problem. I am now consistently getting 3 of the
120+ columns of data, in the table (there are about 6000 rows X 128 columns
per row). SO, that's a start. Is there some "max" that I am crashing in to
with that many columns of data? DOes it make any difference that this is a
sql view, vs a table?
Try this:
Set rs = con.Execute(strWhere, , 1)
MsgBox rs.Fields.Count
Range("a1").CopyFromRecordset rs
That will tell us if it's problem with the data provider or with Excel's
CopyFromRecordset method.
I don't know of any maximums or any problems with view vs. tables.
--
Dick
|