SQL Query Without Worksheet Object

[Guest]

Hi all

I have an SQL query that works fine by returning the value to the cell "A1".

In essence, the value that it returns is a count so it will always be a single value. As the query is part of an iteration of over 1000+ id's I would prefer to be able to get the Value from the SQL query without having the value return to the worksheet

I realise that the QueryTable is part of the Worksheet Object model, but am just wondering if it can be called to return the query result as an integer

Any help would be great

Thanks Paul.

 

[Dick Kusleika]

Paul

You can use ADO to get at the database without using a QueryTable. Take
this example

Function GetADOValue() As Long

Dim mycn As ADODB.Connection
Dim mySQL As String
Dim stConn As String
Dim myRS As ADODB.Recordset

mySQL = "SELECT TblCertRequest.CertificateReqID,"
mySQL = mySQL & "TblCertRequest.Date, "
mySQL = mySQL & "TblCertRequest.CertHolder "
mySQL = mySQL & "FROM TblCertRequest "

stConn = "DSN=MS Access 97 Database;"
stConn = stConn & "DBQ=s:\Paragon\Access\InsuranceForsm.mdb;"
stConn = stConn & "DefaultDir=s:\Paragon;DriverId=281;"
stConn = stConn & "FIL=MS Access;MaxBufferSize=2048"

Set mycn = New ADODB.Connection

mycn.Open stConn

Set myRS = mycn.Execute(mySQL)

GetADOValue = myRS.Fields(0).Value

End Function

You'll need to set a reference (VBE - Tools - References) to the Microsoft
ActiveX Data Objects 2.x Library. You can get the proper information for
stConn and mySQL from your existing QueryTable. Go to the Immediate Window
in the VBE and

?Sheet1.QueryTables(1).Connection
?Sheet1.QueryTables(1).CommandText

Since you only have one field, then .Fields(0).Value should return the only
value in the recordset.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Hi all,

I have an SQL query that works fine by returning the value to the cell "A1".

In essence, the value that it returns is a count so it will always be a

single value. As the query is part of an iteration of over 1000+ id's I
would prefer to be able to get the Value from the SQL query without having
the value return to the worksheet.

I realise that the QueryTable is part of the Worksheet Object model, but

am just wondering if it can be called to return the query result as an
integer?