Re: Run Access Query From Excel With Parameter?
- From: "vanderghast" <vanderghast@com>
- Date: Thu, 12 Nov 2009 10:44:15 -0500
Indeed, the word exec (or execute, at long) is not required either, so just:
? CurrentProject.Connection.Execute("query13 7").Fields(0).Value
works too, with ADO, and it is not limited to Jet as targeted db. Note that if you have more than one parameter, you may have to be sure of the right order of the parameters, using an explicit PARAMETERS clause in your Jet query (something we have to do for crosstabs).
Building, explicitly, an ADO parameters collection is required when your stored proc return a value in one of the parameters, but that is not something Jet does with its stored procedures... or queries with parameters, if you prefer.
Vanderghast, Access MVP
"EricG" <EricG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:6A695F8E-FD86-428D-9A0C-E694579F9C34@xxxxxxxxxxxxxxxx
It's that simple? Wow! I added the parameter value in-line with the
procedure name, and it worked perfectly. Thanks for the help.
Eric
"vanderghast" wrote:
Assuming the database has a saved parameter query, query13 as example:
SELECT MAX(Ds.d)
FROM Ds
WHERE (((Ds.d)<=[limite]));
You can then EXEC it like this:
? CurrentProject.Connection.Execute("exec query13 7").Fields(0).Value
or use an open connection to the database. Note that (here the single)
argument is simply passed after the stored procedure... ooops, the saved
parameter query, name. Also, Execute produce a read only forward only
recordset, so here, I chose to read the value from the first field of the
first record it produced.
Vanderghast, Access MVP
"EricG" <EricG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FADC4A3C-4250-41CB-A6D7-46BFF9488899@xxxxxxxxxxxxxxxx
> I'm using code like the following to run a stored procedure from Excel.
> Is
> there a similar method to run a stored procedure that requires a
> parameter?
> I can always hard code the SQL, but I would rather just be able to call
> the
> procedure and provide the parameter it needs.
>
> Thanks,
>
> Eric
>
> Sub RunAccessQueries_ADO()
>
> Dim cn As ADODB.Connection
> Dim cm As ADODB.Command
>
> dbPath = "d:\data\mypath\"
> dbName = "mydb.mdb"
>
> Set cn = New ADODB.Connection
> Set cm = New ADODB.Command
>
> With cn
> .CommandTimeout = 0
> .Provider = "Microsoft.Jet.OLEDB.4.0;"
> .ConnectionString = "Data Source=" & dbPath & dbName
> .Open
> End With
>
> With cm
> .CommandText = "qryMakeTable" ' Something like "qryMakeTable " &
> myParam?
> .CommandType = adCmdStoredProc
> .ActiveConnection = cn
> .Execute
> End With
> '
> cn.Close
> '
> End Sub
>
.
- References:
- Run Access Query From Excel With Parameter?
- From: EricG
- Re: Run Access Query From Excel With Parameter?
- From: vanderghast
- Re: Run Access Query From Excel With Parameter?
- From: EricG
- Run Access Query From Excel With Parameter?
- Prev by Date: Hide & Unhide Ribbon in Access 2007
- Next by Date: RE: Mysterious Toolbar
- Previous by thread: Re: Run Access Query From Excel With Parameter?
- Next by thread: Enable Disable Report Alternate Back Color
- Index(es):
Relevant Pages
|
