Passing SQL Query Result Into A Variable

[Elliot]

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=7000;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

 

[Guest]

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=7000;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

 

[Roger Whitehead]

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)

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.......

 

[Guest]

'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=7000;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="Password";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

 

[onedaywhen]

As far as I can tell, the code as posted does what you want i.e. you
get a value for your Cust_Ref vaiable using an inputbox and you plug
it into the SQL using

"WHERE (SL.slc_ref='" & Cust_Ref & "'"

and the results are sent to range A1. What do you want to do
differently?