By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

stored procedure in access

P: 92
hi
is there any way to use sql stored procedure in access 2003?

i have a tored procedure that want to link it to access database but have no ided that it is possible or not?

thanks alot
Oct 16 '07 #1
Share this Question
Share on Google+
4 Replies


Jim Doherty
Expert 100+
P: 897
hi
is there any way to use sql stored procedure in access 2003?

i have a tored procedure that want to link it to access database but have no ided that it is possible or not?

thanks alot
Have a look at the .ADP Project file format in Access it is designed to work directly with stored procedures and SQL server side records exposing the stored procedures and view interface for manipulation on the client side.

Regards

Jim
Oct 16 '07 #2

Jim Doherty
Expert 100+
P: 897
Have a look at the .ADP Project file format in Access it is designed to work directly with stored procedures and SQL server side records exposing the stored procedures and view interface for manipulation on the client side.

Regards

Jim

If you are committed to the MDB format then you can access the functionality of stored procedures via queries defined as 'pass through' queries and are documented in help.

In considering your options have a look also at the .ADP Project file format in Access it is designed to work directly with stored procedures and SQL server side records exposing the stored procedures and view interface for manipulation on the client side.

A simple example of using a code function within an ADP file to access a stored procedure where it accepts input parameters comprising of a date from and a date to value derived from, shall we say, a main menu screenform and returning a single output value to the client side application might be something like this (seemingly long winded visually speaking for a simple example that could be achieved in SQL I know,... but it merely illustrates the point)

Expand|Select|Wrap|Line Numbers
  1.  Function GetMyCount() 
  2. On Error GoTo Err_GetMyCount
  3. Dim cmd As ADODB.Command
  4. Set cmd = New ADODB.Command
  5. cmd.ActiveConnection = CurrentProject.Connection
  6. cmd.CommandText = "dbo.usp_MyCountBetweenDates"
  7. cmd.CommandType = adCmdStoredProc
  8. Dim par As ADODB.Parameter
  9. Set par = cmd.CreateParameter("@datefrom", adDate, adParamInput)
  10. cmd.Parameters.Append par
  11. Set par = cmd.CreateParameter("@dateto", adDate, adParamInput)
  12. cmd.Parameters.Append par
  13. Set par = cmd.CreateParameter("@intResult", adInteger, adParamOutput)
  14. cmd.Parameters.Append par
  15. cmd.Parameters("@datefrom") = Format(Forms!frmMainMenu!DateFromCrit, "mm/dd/yyyy")
  16. cmd.Parameters("@dateto") = Format(Forms!frmMainMenu!DateToCrit, "mm/dd/yyyy")
  17. cmd.Execute
  18. GetBookingCount = cmd.Parameters("@intResult").Value & " Records"
  19. Exit_GetMyCount:
  20. Exit Function
  21. Err_GetMyCount:
  22. DoCmd.Hourglass False
  23. DoCmd.Echo True
  24. MsgBox err.Description,vbinformation,"System Function GetCount Error" 
  25. Resume Exit_GetMyCount
  26. End Function
  27.  
The server side stored procedure then might look something like this

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE PROCEDURE dbo.usp_MyCountBetweenDates
  3. (@datefrom datetime,@dateto datetime,@intResult int output)
  4. AS
  5. SET NOCOUNT ON
  6. SELECT @intResult=COUNT(*) FROM dbo.tblMyTable WITH (NOLOCK) WHERE (MyDate >=@datefrom AND MyDate<=@dateto)
  7. SELECT @intResult
  8. GO


Regards

Jim
Oct 16 '07 #3

P: 92
thanks alot
i try to do as you said. i hope it work for me. it seems hard.
thanksssssssssssssssssssssss
Oct 17 '07 #4

Jim Doherty
Expert 100+
P: 897
thanks alot
i try to do as you said. i hope it work for me. it seems hard.
thanksssssssssssssssssssssss
Well the learning curve is never simple.. but if you are working with stored procedures predominently then inevitably you going to need to know server side transact SQL language. If you intend to keep your transactions server side mostly you might as well dive straight in, do just that and work server side, and make the application reference and return only the data it needs. It is the optimum method of working with SQL Server

Regards

Jim :)
Oct 17 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.