SQL Query to Excel

[PatK]

I am in the initial throws of attempting to populate an Excel Worksheet, with
table rows selected from a SQL Server DB, using the following code:

Sub WSPopulate()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strWhere As String

Set con = New ADODB.Connection
con.Open "Driver={SQL Server};Server=GVS00534\node1;Database=mydb;"
strWhere = "SELECT * FROM dbo.application WHERE Owner_L2 = 'sbcorg'"

Set rs = con.Execute(strWhere, , 1)
Range("a1").CopyFromRecordset rs

rs.Close
con.Close
End Sub

This worked VERY nicely the first time...and ALL my data rows/columns
populated the page. However, on my second attempt, only 3 columns from the
sql db passed to the Worksheet, and on my 3rd, nothing. On the third try,
the hour glass hardly flickered...it's as if I have consumed all of some
resource along the way. Do I have a "hygiene" issue here, where I am not
releasing something I should be? Any other ideas? Also, does anyone have a
pointer to tips on how to do this? I am pretty new to DB access from excel
VBA, and some "examples" might help me a lot.

Thanks,
Patk

 

[Rob]

I am in the initial throws of attempting to populate an Excel Worksheet, with
table rows selected from a SQL Server DB, using the following code:

Sub WSPopulate()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strWhere As String

Set con = New ADODB.Connection
con.Open "Driver={SQL Server};Server=GVS00534\node1;Database=mydb;"
strWhere = "SELECT * FROM dbo.application WHERE Owner_L2 = 'sbcorg'"

Set rs = con.Execute(strWhere, , 1)
Range("a1").CopyFromRecordset rs

rs.Close
con.Close
End Sub

This worked VERY nicely the first time...and ALL my data rows/columns
populated the page. However, on my second attempt, only 3 columns from the
sql db passed to the Worksheet, and on my 3rd, nothing. On the third try,
the hour glass hardly flickered...it's as if I have consumed all of some
resource along the way. Do I have a "hygiene" issue here, where I am not
releasing something I should be? Any other ideas? Also, does anyone have a
pointer to tips on how to do this? I am pretty new to DB access from excel
VBA, and some "examples" might help me a lot.

Thanks,
Patk

Try putting the following at the end of the code:

set rs = nothing
set con = nothing

 

[Gary''s Student]

Try:

Sub WSPopulate()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strWhere As String

Set con = New ADODB.Connection
con.Open "Driver={SQL Server};Server=GVS00534\node1;Database=mydb;"
strWhere = "SELECT * FROM dbo.application WHERE Owner_L2 = 'sbcorg'"

Set rs = con.Execute(strWhere, , 1)
Range("a1").CopyFromRecordset rs

rs.Close
con.Close
Set rs=Nothing
Set con=Nothing
End Sub

 

[PatK]

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?

Thanks again, Rob and Gary's Student

patk

 

[Dick Kusleika]

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.

 

[PatK]

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

 

[Mike]

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]

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