Stored procedures & pass through queries

  • Thread starter Thread starter andyj
  • Start date Start date
A

andyj

Hi All,

I've been trying to utilise SQL Server stored procedures from an Acces
database for while now. So far I use the following in a pass throug
query, which works great:

<vbcode>
'This displays all visitors based on two date parameters
EXEC visitorLog @dtFrom = [2/10/04 08:00:00 AM], @dtTo = [3/10/0
6:00:00 PM]
</vbcode>

This is OK if my parameters are static, but being parameters, they ar
not. How do I send the EXEC command to SQL Server from VBA code? Is
case of using an ADO connection and command object? If so, I guess
have a bit of learning to do ;)

Thanks for reading! Hope any responses will be of use to others :)

And
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use DAO & Pass-thru queries. Create a pass-thru query & name
it something, like "qryVistorLog." Then create some VBA code to load
the query's SQL property w/ the appropriate dates & run it. E.g.:

== Begin air code ==

' This can be in a CommandButton's OnClick event procedure,
' or wherever you deem necessary

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs("qryVistorLog")

dim strFrom as string
dim strTo as string
strFrom = Format(Me!txtFrom, "yyyy-mm-dd hh:nn:ss")
strTo = Format(Me!txtTo, "yyyy-mm-dd hh:nn:ss")

dim strSQL as string
strSQL = "EXEC visitorLog '" & strFrom & "', '" & strTo & "'"

' This loads the pass-thru query w/ the new dates
qd.SQL = strSQL

' Now open a recordset on the QueryDef or Execute the query.
' Open recordset:
' dim rs as dao.recordset
' set rs = qd.OpenRecordset()
'
' Execute query
' qd.Execute
'
== End air code ==

You'll have to put in your own error handling code.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQC71eoechKqOuFEgEQLLTQCfZUMXj7XWyRb1vSNRX/lD8/H4wUsAn3mo
nuTvq7z8nWBXI1m0e9zoQRfq
=/al2
-----END PGP SIGNATURE-----
 
Back
Top