Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query to Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query to Excel
On Jul 23, 1:40 pm, PatK wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query to Excel
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 -- Gary''s Student - gsnu200796 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query to Excel
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200796 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query to Excel
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query to Excel
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query to Excel
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Edit Query from Excel will not open query in MSQuery | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |