September 4, 2002 at 10:17 am
Forgive me for my lack of knowledge, I am fairly new to TSQL programming. I want to run a db query in Excel, which I can do no problem. What I would like to do is use data in cells as arguements in the TSQL. IE - select * from table_1 where column_1 = cellA1. Help??
September 5, 2002 at 2:28 am
Carl,
This seems more an excel/ADO question than T-SQL. There are a few ways of referencing it. If you can run the query from excel then just concatenate the selection (cells(r,c).value - make sure the sheet you want is active). Make sure you use correct TSQL syntax when concatenating the string. The rest is just like normal VB unless you use a query table object.
Query tables are an excellent generic way to go.
But generally if it is fixed then just create the T-sql string referencing the cells.
Create an adodb.recordset object.
Create an adodb.connection object.
Connect.
Open the recordset with the sql string and the connection object.
(rs.Open strsql, <connected connectionobject>, adOpenKeyset, adLockOptimistic 'or whatever
And you've got the recordset.
You can also muck around with command objects which are good for stored procedures (if quite unweildy)
here's an example using command objects and a query table.
Dim objMyQueryTable As QueryTable
Dim cmd As New ADODB.Command
Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset
Range("A6:M6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
con.CursorLocation = adUseClient
con.Open <connection string>
With cmd
Set .ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "spViewOrders"
.Parameters.Append .CreateParameter("@StartDate", adVarChar, adParamInput, 20, Format(Application.Range("vwStartDate"), "dd/mmm/yyyy"))
.Parameters.Append .CreateParameter("@EndDate", adVarChar, adParamInput, 20, Format(Application.Range("vwEndDate"), "dd/mmm/yyyy"))
.CommandTimeout = Application.Range("timeout")
Set objMyQueryTable = ActiveSheet.QueryTables.Add(.Execute, Destination:=ActiveSheet.Range("A6"))
objMyQueryTable.Refresh False
objMyQueryTable.Delete
End With
September 5, 2002 at 5:41 am
One approach to this in Excel is with parameters. Create a DSN and link to the database, then in the query builder use SQL and enter something like this:
SELECT Ship.TrackingNbrStr, Ship.UnitCd, Ship.CustCd, Ship.MRADt, Ship.RefNbr
FROM dw.dbo.Ship Ship
WHERE (Ship.TrackingNbrStr=?)
Note the "?". Then under the import External Data use the Parameters option to define where it comes from. You can either prompt for the parameter (on each refresh), specify a value there, or get it from a data cell on the spreadsheet.
I've used this occasionally, and it generally works, but it can be awkward and a bit flakey. I don't recall the flakiness in detail, I just remember moving away from this and providing a web interface for our people who wanted to write reusable queries. But I just tried one with the query above and it worked fine.
September 9, 2002 at 2:06 am
You could use the sql.request function from the excel odbc add in - tools - add ins - "ODBC add-in". Then you can return stuff from your database from the sheet without going through code. Or as the man says do a query table and then a lookup - often quicker. formula is "=SQL.REQUEST("dsn=databasename",,3,"SELECT top 1 fieldname from tb_Records")" You can add in your references to other cells in the sql string bit at the end. You can also return this as an array - but then you might as well use a query table.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply