[AccessD] Stored procedures from Access

John:
Here is a procedure I use in Access to call a stored procedure in SQL with
parameters. It takes two integer parameters. You will need to add a
Reference to the Microsoft ActiveX Data Objects library. It should give you
everything you need to start.
Hope it helps.

James Barash

Public Sub SetOrderStatus(ID As Long, Status As Integer)
Dim conn As ADODB.Connection
Dim cmd As ADODB.command

On Error GoTo HandleErr

Set conn = New ADODB.Connection
conn.ConnectionString =
"PROVIDER=MSDASQL;uid=<UserID>;pwd=<Password>;driver={SQL
Server};server=<ServerName>;database=<DatabaseName>" ' substitute proper
credentials here
conn.CursorLocation = adUseClient
conn.Open
If conn.State <> adStateOpen Then
	msgbox "Error opening Connection"
	set conn = Nothing
	Exit Sub
End if


Set cmd = New ADODB.command
With cmd
    Set .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_SetOrderStatus" ' name of stored procedure
    .Parameters.Append cmd.CreateParameter("ID", adInteger, adParamInput, ,
ID) 'parameters must be defined in order
    .Parameters.Append cmd.CreateParameter("Status", adInteger,
adParamInput, , Status)
    .Execute
End With
Set cmd = Nothing
conn.Close
Set conn = Nothing

  Exit Sub

HandleErr:
  Select Case err.Number
    Case Else
       MsgBox "Error " & err.Number & ": " & err.Description, vbCritical,
"basCustom.SetOrderStatus"
  End Select
End Sub
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, January 22, 2009 1:42 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored procedures from Access

Martin,

Thanks for the links.  In fact I found the first, and it 
will no doubt be useful to me someday however...

I have pretty complex (to me at least) set of stored 
procedures created out in SQL Server.  For example I have a 
stored procedure that does a BCP out to export data from a 
table for processing by a third party called Accuzip 
(address validation).  The data in the table is name / 
address information that has to be exported to files in a 
specific directory, on a specific server (actually a virtual 
machine here in my office).  The virtual machine is running 
Accuzip, which is is watching that folder.  As I place files 
into the watched directory, Accuzip starts processing them, 
and placing processed files into an "output" directory.  I 
then have to get the files back into SQL Server.  I have a 
matching set of stored procedures that BCP the data back in 
to a table in SQL Server.

So what I am trying to accomplish is simply tap SQL Server 
on the shoulder and say "hey, execute SPXXX in database 
YYYY, with these parameters".

This is not about getting a result set to bind to a form or 
control, this is about telling SQL Server to do some 
specific stored procedure, to perform some process.

Once Accuzip processes the input files and creates the 
output files, I then need to tap SQL Server on the shoulder 
and say "hey, execute this Stored Procedure in database YYY 
with these parameters.  The end results will be to import 
the files back into SQL Server.

I have all of the stored procedures created, and have used 
them for quite some time, however it still takes significant 
manual labor to manually tap SQL Server on the shoulder.

I want to tie the pieces together from Access.

This is a very well defined process, but the database name, 
stored procedure names and table names can change from run 
to run.

I kind of envisioned something like Charlotte's function 
that would do exactly this... tap SQL on the shoulder and 
tell it to do something, passing in parameters as required. 
  Unless it is somehow required in order to accomplish this 
task, I have no need for pass through queries in Access, no 
need for linked tables to SQL Server, no need for loading 
data into forms or combos etc.

I might someday, but not for this specific task.

John W. Colby
www.ColbyConsulting.com