General discussion

Locked

Pass value to SQL query from Access

By Bob Sellman ·
Pass through queries (Access front end, SQL back end) work fine, but for faster processing I'd like to define some views in SQL, but be able to call the view with a pass through query from Access and, as part of the pass through query, define the value of a variable value contained in the SQL view.

This would mean that I could define a view in SQL that would be used for a report, but the SQL view would include variables in its where clause and I would somehow be able to pass the variable through from Access.

I don't want to use a value in a special table because with multiuser operation, more than one user might ask for the same report at the same time.

Can this be done? If so, how?

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Pass value to SQL query from Access

by mdmeyers In reply to Pass value to SQL query f ...

I'm not sure how you're doing it in access, but I always write my VB code with the .execute procedure that way I don't have to deal with all of the other "overhead" of standard recordsets. You can then assign a recordset to the .execute procedure (which is a recordset type). From there you should be able to build out your report.

If you have additional questions, feel free to e-mail me and I'll send you the VB code (which should work in A2K or better)...

Collapse -

Pass value to SQL query from Access

by Bob Sellman In reply to Pass value to SQL query f ...

I'm not concerned about the report, rather I'd like to define views in SQL that include variables and be able to excute that view with a pass through query (i.e., a query executed at the SQL Server) that somehow passes values to the variables that are contained in the view.

The basic concept is that I can then write some standard views in SQL that will be called by existing queries in the (Access) front end, rather than creating a custom pass through query in Access based on the user selected criteria.

Collapse -

Pass value to SQL query from Access

by RRV In reply to Pass value to SQL query f ...

Create stored procedure in SQL server which calls the parameterised queries inside SQL server. call these SP's with parameters from ACCESS [any version].
All the best
RV

Collapse -

Pass value to SQL query from Access

by Bob Sellman In reply to Pass value to SQL query f ...

What is not obvious is that you can call the stored procedures from Access by writing a pass through query in which the "SQL code" reads something like:
EXEC {stored procedure name] @parameter1 = {value}

It seems that Access doesn't care what text/code you are passing through, so it really doesn't have to be a true SQL query. Unfortunately, it's still necessary to build the code each time in Access. It's not that building the code for the pass through query is difficult, it's just that it would have been nicer to be able to have the Access "query" automatically pick up values and pass them through to SQL.

Collapse -

Pass value to SQL query from Access

by Bob Sellman In reply to Pass value to SQL query f ...

The second answer sounds like it may be the solution, but more information would be helpful. Any particular reference that might explain how to set up parameters, build a SQL view that calls them, and how to pass the parameter values from the Access front end to SQL?

And what happens if two users are, let's say, calling the same report (which would mean the same parameters) but with different criteria (such as a date range, for example)? Wouldn't you end up with the same problem as one where you have a table (essentially a "parameter" table) where the user criteria values are stored and then the report run, where the SQL view just uses the values in the table (rather than parameters as you suggest)? (The problem being that the wrong values might be used for one of the users' reports.)

Collapse -

Pass value to SQL query from Access

by Glen_McLeod In reply to Pass value to SQL query f ...

SQL Server views *cannot* have parameters, so you have to use a stored procedure, it can take parameters and return resultsets.

Should multiple users call the procedure at the same time, then each will have their own copy executing with each's different parameters, and get different resultsets for the different reports.

Glen

Collapse -

Pass value to SQL query from Access

by Glen_McLeod In reply to Pass value to SQL query f ...

Passing parameters to stored procedures:
(m_conData has already been configured as an ADO Connection and opened.)

Dim m_cmExecute as ADODB.Command
Dim rsDataSet as ADODB.Recordset

Set m_cmExecute = New ADODB.Command

Set m_cmExecute.ActiveConnection = m_conData
m_cmExecute.CommandType = adCmdStoredProc
m_cmExecute.CommandText = "ProcNameHere"

'CreateParameter function (ParamName, [Type], [Direction], , [Value])

m_cmExecute.Parameters.Add m_cmExecute.CreateParameter("FirstName", adVarChar, adParamInput, 10, "Glen")

'Execute and assign the resultset to the recordset object
Set rsDataset = m_cmExecute.Execute


Once your stored procedure has been created in the database, you know what the parameter names, directions and types are, so you hard code them. (There is a way to get them dynamicly, but I won't go into that here.)

Since the Value is optional, you can supply it immediately or add it later with the Command.Parameters("ParamName").Value syntax.

You can get the value of the input parameters from the user by any means that fits your program, text box, list, combo, whatever.

Glen

Collapse -

Pass value to SQL query from Access

by Bob Sellman In reply to Pass value to SQL query f ...

What is not obvious is that you can call the stored procedures from Access by writing a pass through query in which the "SQL code" reads something like:
EXEC {stored procedure name] @parameter1 = {value}

It seems that Access doesn't care what text/code you are passing through, so it really doesn't have to be a true SQL query. Unfortunately, it's still necessary to build the code each time in Access. It's not that building the code for the pass through query is difficult, it's just that it would have been nicer to be able to have the Access "query" automatically pick up values and pass them through to SQL.

By the way, it's actually easier to create the query in Access than your code suggests. Drop the current query, then create the new query (with the connection string) and execute it.

Collapse -

Pass value to SQL query from Access

by Bob Sellman In reply to Pass value to SQL query f ...

But how do you call the stored procedure from Access and pass parameters (hopefully without having to write a new Access query or whatever is used when the values change for the parameters)? Unless that part of my question is answered, the stored procedures are useless.

Collapse -

Pass value to SQL query from Access

by Bob Sellman In reply to Pass value to SQL query f ...

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums