access front end with SQL Server backend question 1

[maswien]

Does anybody know how to call a SQL Server stored procedure from access front end? Thanks

 

[ptpenry]

Yes, create a pass-through query with SQL similar to this:

EXEC SPname parameters

eg EXEC SPclosedcases '01/07/2002', '31/07/2005', -1

Hope this helps

 

[maswien]

Thanks for the reply!

Is it possible to generate a passthrough query like that in VBA code, and run it when user click a submit button on the form?

 

[chrissie1]

try using adp instead of mdb files adp stands for access data project and was made especially to work with sql-server.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[hwkranger]

This has already been answered, but you can also do it this way from VBA inside of Access

dbcnn.execute "execute EXEC SPclosedcases '01/07/2002', '31/07/2005', -1"

This is similiar to how you would handle it with any ADO connection. Where dbcnn is your open connection..

Randall Vollen
National City Bank Corp.

 

[maswien]

Thanks for the replay!

chrissie1, I don't have knowlegde on ADP, is there any reading sources for this on internet?

hwkranger, that for sure helps!

I am trying to find a way to protect the access front end user from changing or viewing the tables in SQL Server, so I want grant the execute permission of stored procedure to the access user and not grant the write permission directly to the access user, is there any other method to do this from access?

 

[hwkranger]

btw,

that should read

dbcnn.execute "EXEC SPclosedcases '01/07/2002', '31/07/2005', -1"

no idea what I was thinking while I typed.


Randall Vollen
National City Bank Corp.

 

[maswien]

Is this possible to get the return value from the stored procedure? thanks

 

[hwkranger]

Yes... You can open it as a recordset

Randall Vollen
National City Bank Corp.