[AccessD] Stored procedures from Access

James,

When I try to run the function I get an error on the 4th 
executable line:

conn.open

Multi-step ole db operation generated errors.

John W. Colby
www.ColbyConsulting.com


James Barash wrote:
> 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
> 
> 
>