Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQL Query Result Into A Variable
Hi readers,
I am using Excel 2000 VBA to run a quick query. I am hoping to provide my SQL server with an account number, for which it will reply with the relevant customer's name. A snippet of my code looks like this: '------------------------------------------------------------- Dim Msg As String, Cust_Ref As String Msg = "Please enter customer reference" Cust_Ref = UCase(InputBox(Msg, "User Request")) With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DRIVER={Transoft ODBC Driver};TSDSN=dbasename.udd;Server=server;Port=700 0;Timeout=3600;Description=;" _ , Destination:=Range("A1")) .CommandText = Array( _ "SELECT SL.slc_name" & Chr(13) & "" & Chr(10) & "FROM root.SL SL" & Chr(13) & "" & Chr(10) & "WHERE (SL.slc_ref='" & Cust_Ref & "')") .Name = "Query from Server" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With '------------------------------------------------------------- At first, I tried replacing Destination:=Range("A1")) with Destination:=Cust_Ref) But it didn't work. :-( Any help would be appreciated! Thanks in advance! P.S. I'm relatively new to SQL via Excel 2000, but have been using Excel VBA for years, as well as maintaining/programming a company PROGRESS 4GL v8.2c database system. Elliot |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQL Query Result Into A Variable
Hi Elliot
Destination:=Range("A1")) ......is where the results will be delivered to. Would it not work like that? It looks as if it should do...... HT Roge Shaftesbury (UK ----- Elliot wrote: ---- Hi readers I am using Excel 2000 VBA to run a quick query. I am hoping to provid my SQL server with an account number, for which it will reply with th relevant customer's name A snippet of my code looks like this '------------------------------------------------------------ Dim Msg As String, Cust_Ref As Strin Msg = "Please enter customer reference Cust_Ref = UCase(InputBox(Msg, "User Request") With ActiveSheet.QueryTables.Add(Connection:= "ODBC;DRIVER={Transoft ODB Driver};TSDSN=dbasename.udd;Server=server;Port=700 0;Timeout=3600;Description=; , Destination:=Range("A1") .CommandText = Array( "SELECT SL.slc_name" & Chr(13) & "" & Chr(10) & "FROM root.S SL" & Chr(13) & "" & Chr(10) & "WHERE (SL.slc_ref='" & Cust_Ref "')" .Name = "Query from Server .FieldNames = Fals .RowNumbers = Fals .FillAdjacentFormulas = Fals .PreserveFormatting = Tru .RefreshOnFileOpen = Fals .BackgroundQuery = Tru .RefreshStyle = xlInsertDeleteCell .SavePassword = Tru .SaveData = Tru .AdjustColumnWidth = Tru .RefreshPeriod = .PreserveColumnInfo = Tru .Refresh BackgroundQuery:=Fals End Wit '------------------------------------------------------------ At first, I tried replacing Destination:=Range("A1")) wit Destination:=Cust_Ref But it didn't work. :- Any help would be appreciated Thanks in advance P.S. I'm relatively new to SQL via Excel 2000, but have been usin Excel VBA for years, as well as maintaining/programming a compan PROGRESS 4GL v8.2c database system Ellio |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQL Query Result Into A Variable
Having re-read your post (sorry), probably best to try Recordset Method
Ensure your pointer is at the first (hopefully only) record. Then reference the appropriate Fields, eg: myRecordset.MoveFirst Cust_Ref = myRecordset.Fields("name") I can post Connection/Recordset examples tomorrow from my work PC. -- HTH Roger Shaftesbury (UK) "Roger" wrote in message ... Hi Elliot, Destination:=Range("A1")) .....is where the results will be delivered to. Would it not work like that? It looks as if it should do....... HTH Roger Shaftesbury (UK) ----- Elliot wrote: ----- Hi readers, I am using Excel 2000 VBA to run a quick query. I am hoping to provide my SQL server with an account number, for which it will reply with the relevant customer's name. A snippet of my code looks like this: '------------------------------------------------------------- Dim Msg As String, Cust_Ref As String Msg = "Please enter customer reference" Cust_Ref = UCase(InputBox(Msg, "User Request")) With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DRIVER={Transoft ODBC Driver};TSDSN=dbasename.udd;Server=server;Port=700 0;Timeout=3600;Description =;" _ , Destination:=Range("A1")) .CommandText = Array( _ "SELECT SL.slc_name" & Chr(13) & "" & Chr(10) & "FROM root.SL SL" & Chr(13) & "" & Chr(10) & "WHERE (SL.slc_ref='" & Cust_Ref & "')") .Name = "Query from Server" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With '------------------------------------------------------------- At first, I tried replacing Destination:=Range("A1")) with Destination:=Cust_Ref) But it didn't work. :-( Any help would be appreciated! Thanks in advance! P.S. I'm relatively new to SQL via Excel 2000, but have been using Excel VBA for years, as well as maintaining/programming a company PROGRESS 4GL v8.2c database system. Elliot |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing SQL Query Result Into A Variable
'Watch our for the word wrap!
Dim rs as object, wrkODBC as object Dim sqlString as String, strVar as String Cust_Ref = UCase(InputBox(Msg, "User Request")) sqlString = "SELECT SL.slc_name FROM root.SLSL WHERE (SL.slc_ref='" & Cust_Ref &"')" 'Refer to your DSN info & On Line Help for this bit: With ActiveSheet.QueryTables.Add(Connection:= "ODBC;DRIVER={Transoft ODBC Driver};TSDSN=dbasename.udd;Server=server;Port=700 0;Timeout=3600;Description=;" 'Refer to your DSN info & On Line Help for this bit: Set wrkODBC = CreateWorkspace("ODBC;DRIVER={Transoft ODBC Driver}", "UserName", "Password", dbUseODBC) wrkODBC.DefaultCursorDriver = dbUseClientBatchCursor Set conODBC = wrkODBC.OpenConnection("ODBC;DRIVER={Transoft ODBC Driver}", dbDriverNoPrompt, False, "ODBC;DATABASE="databasename";UID="UserName";PWD=" Password";DSN="ODBC;DRIVER={Transoft ODBC Driver}) Set DBs = wrkODBC.OpenDatabase(ODBC;DRIVER={Transoft ODBC Driver}, dbDriverNoPrompt, False, "ODBC;DATABASE="databsename";UID="UserName";PWD="P assword";DSN=" ODBC;DRIVER={Transoft ODBC Driver}") With DBs Set rs = DBs.OpenRecordset(sqlString, dbOpenSnapshot) rsflds = rs.Fields.Count rs.MoveFirst 'use the appropriate Fields Name here- strVar = rs.Fields("CompanyName") rs.close dbs.close wrkodbc.close HTH Roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variable to LINEST | Excel Worksheet Functions | |||
passing a variable as an argument to a function | Excel Programming | |||
Passing a variable to a range statement | Excel Programming | |||
UserForm not passing Boolean variable | Excel Programming | |||
Passing a value to a variable from Userform | Excel Programming |