Passing SQL Query Result Into A Variable

  • Thread starter Thread starter Elliot
  • Start date Start date
E

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
 
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
 
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 said:
Hi Elliot,
Destination:=Range("A1"))
.....is where the results will be delivered to. Would it not work like
Click to expand...
that? It looks as if it should do.......
 
'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
 
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?
 
Back
Top