Re: Dynamic use of Access query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jamie Collins (jamiecollins_at_xsmail.com)
Date: 01/24/05 

Date: 24 Jan 2005 01:58:42 -0800

Laurent M wrote:
> i got this piece of code which returns a recordset from an Access
Query.
> But i need this function to pass dynamicaly the name as a parameter
of the
> function, and then get a recordset from that.
>
> But this code needs to add parameters from the query :
>
> Dim adoCmd As ADODB.Command
> Dim adoConn As ADODB.Connection
> Dim adoRS As ADODB.Recordset
>
> ' Database connection
> 'sAccessDB path of the Access DB
> Set adoConn = New ADODB.Connection
> adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> sAccessDB & "; USER ID=Admin; PASSWORD=;"
>
> Set adoCmd = New ADODB.Command
> With adoCmd
> .ActiveConnection = adoConn
> 'Name of the query
> .CommandText = "QueryName"
> .CommandType = adCmdStoredProc
> 'Parameters defined in the query
> .Parameters.Append .CreateParameter("pCycle", adVarChar,
> adParamInput, 16, sCompSel)
> .Parameters.Append .CreateParameter("pWorkDate", adDate,
> adParamInput, , UpdateDate)
> 'Execute the query
> Set adoRS = adoCmd.Execute
> End With
>
> 'Close connection
> Set adoRS = Nothing
> Set adoCmd = Nothing
> Set adoConn = Nothing
>
> Do you know how could i get rid of lines with .Parameters or how
could i
> dynamicaly get those from the name of the query?

I'm not sure what you are asking.

Note you are calling a stored procedure. The MS Access community may
call it a parameterized Stored Query object (or whatever), but you are
using the Jet 4.0 OLEDB provider which, in common with everyone else,
calls it a PROCEDURE. A stored proc may be called using the SQL method
EXECUTE (this is what the ADO Command object is using under the hood
anyhow).

If you are saying your stored proc doesn't have any parameters, the
answer is to not use any in the call e.g.

Set adoRS = adoConn.Execute( _
"EXECUTE MyStoredProc;")

If you are saying your stored proc does have parameters but you don't
know their names, the answer is you don't need to know their names. You
just need to be able to pass valid values e.g. if MyStoredProc has two
parameters start_date and end_date of type DATETIME:

Set adoRS = adoConn.Execute( _
"EXECUTE MyStoredProc '2001-01-01', '2004-01-01';")

If you are saying you only have the stored proc's name and you want to
find details e.g. number of parameters, their data type etc, then you
can use ADOX to create an appropriate ADO Command object for you and
use this to call the proc e.g. add something like this to your code

Dim adoxCat as Object
Set adoxCat = CreateObject("ADOX.catalog")
Set adoxCat.ActiveConnection = adoConn
Set adoCmd = adoxCat.Procedures("MyStoredProc").Command

You can then get the details from the newly-created command e.g.

adoCmd.Parameters(0).Name
CBool(adoCmd.Parameters(0).Type = adDate)

FWIW ADODB's GetSchema method has an enum for
adSchemaProcedureParameters to return an OLEDB PROCEDURE_PARAMETERS
rowset i.e. parameter details for procedure. However, when I try this
with the Jet 4.0 provider I get an error, 'Object or provider is not
capable of performing requested operation' (same for SQL Server). I
assume this is because the provider must query system tables not
accessible with ADODB.

Jamie. 

--

Relevant Pages

  • Re: stored proc RETURN_VALUE.... there it is, I want to use it !!!
    ... > Then you can read the value of @total after closing the query. ... > 2 Set up your input param values. ... the result of this sql code returns an integer that is ... >> take the guts of the stored proc with hard coded guestid and meeting ...
    (borland.public.delphi.database.ado)
  • Re: Stored procedure never finishes
    ... Perhaps when you run the query without the date range, ... You do this by issuing "exec sp_lock [spid]" with spid being ... SQL Server MVP ... > I have a stored proc that won't finish. ...
    (microsoft.public.sqlserver.server)
  • Re: HELP !! Running SQL 2000 using ADO command object from Access 2000
    ... A time-out might indicate a permission problem, so you should check that by ... permission to the account used from Access or 3) running the query directly ... on its execution. ... SQLOLEDB provider and ADO Command object. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Weird performance issue ....
    ... If I understand - the problem is with a query returned from a stored proc ... "a query making a user function call" you mean something different again). ... as PL/SQL is assumed to be a "batch" environment. ... Not taking about power since where the issue is, the server ...
    (comp.databases.oracle.server)
  • Re: Optimizer chooses different plans
    ... > When we execute a stored procedure it will do table scans, wether it is called from COM+ using ADO or executed in Query Analyzer. ... > If we cut and paste the code from the stored proc directly to Query Analyzer and execute it, ... > If I cut and paste this code into Query Analyzer AND remove the index hints it does Index Seeks on both tables using the indexes. ...
    (microsoft.public.sqlserver.server)