execute a sql server stored procedure from ms access database

[timfost]

folks, is it possible to execute a stored procedure in the sql server database from an ms access database. We already have the stored procedure running via a sql server job but the users want to be able to execute it on demand and already use ms access to query tables in the database.

thanks

 

[JoeAtWork]

You can use an ADO Command object to execute stored procedures on SQL Server.

 

[genomon]

Also do a help search on pass-through queries.

When everything is coming your way, you're in the wrong lane.

 

[timfost]

thanks folks. I'll try the pass through query as it looks like it may work according to the help documents. I'm not familiar with access or vb so couldn't really find any good examples of an ado command object. JoeAtWork, do you have any links to some examples of how to use it?

 

[JoeAtWork]

Here's the first example I could find from an old project. This runs the stored procedure "GetAllPlantCartonRecords". It should be noted that the MakeDatabaseConnection() function returns an ADO connection object that has opened a connection with the database.

If you Google "ADO Command Object" you should be overwhelmed with information.

'Created:       11-Dec-2001
'
'Description:   Returns a recordset of all undeleted Plant Carton records, all fields
Public Function GetAllPlantCartonRecords() As ADODB.Recordset
    Dim objCommand As ADODB.Command
    
    Set objCommand = New ADODB.Command
    objCommand.ActiveConnection = MakeDatabaseConnection
    objCommand.CommandText = "GetAllPlantCartonRecords"
    objCommand.CommandType = adCmdStoredProc
    
    Set GetAllPlantCartonRecords = objCommand.Execute
    
    Set objCommand = Nothing
End Function