Excute SQL server store procedures from Access

[souris]

I have an app using SQL Server as backend and MS Access as front end.
I wanted to execute store procedures in SQL server from MS Access front app.
Any suggestions or opinions?

Your information is great appreciated.

Souris

 

[Tim]

1. You will need MDAC 2.7 or Higher.

2. You will need the proper reference.

Sadly, I don't know which one. But it can be done from MS Access that
I know. I had it set up in an Access97 DB.
Soon as I find out, I will post again and let you know.

 

[Douglas J. Steele]

There's absolutely no reason why you'd require MDAC 2.7, or any version of
MDAC, for that matter, especially if you're using Access 97.

All you need to do is create a pass-through query, and have the parameters
for the stored procedure as part of the SQL:

EXEC MyProc @Parm1=23, @Parm2='Value'

 

[Sylvain Lafontaine]

What kind of Access frontend application? MDB or ADP?

In both case, the use of OLEDB objects will permits to execute stored
procedures on a SQL-Server from an Access application; however, if you want
to capture the result for a bound form, you must use SQL passthrough with
MDB or setting the record source with ADP.

S. L.

 

[J. Clay]

Since this is the ADP group, I will assume that is what you are using. And,
since an ADP (Access Data Project) is a direct link into a SQL server, in
the database window there will be a list of stored procedures and views
under queries. You can just double click one to run it.....

With that said, there are a couple of ways to execute a stored procedure in
an ADP other than from the database window. It can be the record source
for a form or report. Or you can execute it in VBA using an ADODB Command.
Using VBA you can return a single value or a recordset.

Here is an example of a VBA function I use to get the current SQL user by
getting an output parameter from a stored procedure.

Public Function GetUser() As String
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "sp_CurrentUser"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("strCurrentUser", adVarChar,
adParamOutput, 20)
cmd.Parameters.Append prm
cmd.Execute

GetUser = cmd.Parameters("strCurrentUser")
Set prm = Nothing
Set cmd = Nothing
End Function



HTH,
Jim

 

[souris]

Thanks for the information,
I use Pass Through Query Exec MyProc @Param1 works, but I need select ODBC
DSN file name.
Is it possible to automation the process?
I use MDB application.
If I need some code to do the job can you please give sample code to execute
SQL server?
Thanks in advance,

Souris

 

[souris]

Thanks every one here.
I just created a ADP application.
The store procedures just have an icon to click.
Thanks again,

Souris

 

[caracol]

SQL-Pass-Through for Stored Procedures with parameters through VBA

Hello together,

can anyone tell me how to execute an SQL Pass Through (PT) from an MDB-file with VBA? I'm in need to execute stored procedures or functions with parameters as views do not support them.

I can not use ADP due to the already described problem (https://www.pcreview.co.uk/forums/thread-1627484.php) that rights of views in OLE DB are ignored and substituted by the ones of the tables they refer to.

It would be useful to create an SQL-PT with its encoded parameters by means of VBA or to enable the report wich should launch the query to pass the parameters to it.

Thanks for your help in advance
caracol