Calling stored procedure using VBA

Victoria,

You can run a stored procedure using ADO, like below...

Set mobjConn = New ADODB.ConnectionmobjConn.Open "your connection string"Set mobjCmd = New ADODB.CommandWith mobjCmd    .ActiveConnection = mobjConn     .CommandText = "your stored procedure"    .CommandType = adCmdStoredProc    .CommandTimeout = 0    .Parameters.Append .CreateParameter("your parameter name", adInteger, adParamInput, , your parameter value)    ' repeat as many times as you have parameters    .ExecuteEnd With

To get your connection string, you can use the line

Debug.Print CurrentDb.TableDefs("tblInstrumentInterfaceLog").Connect

in the Immediate Window and that should show you a connection string which you can use.

Would you try that and let me know if you have any problems.

Ash

Can also formulate a stored proc call that returns a result set as a select statement.

As per this example:

Sub Macro2()'' Macro1 Macro'    'Declare variables'        Dim mySql As String        Set objMyConn = New ADODB.Connection        objMyConn.CommandTimeout = 0        Set objMyCmd = New ADODB.Command        objMyCmd.CommandTimeout = 0        Set objMyRecordset = New ADODB.Recordset        objMyConn.ConnectionString = CStr(Range("ADOConnectString").Value)        objMyConn.Open        Set objMyRecordset.ActiveConnection = objMyConn        Set objMyCmd.ActiveConnection = objMyConn   ' call dbo.TotalLHCLoadingRate  Range("TotalLHCLoadingRate")        mySql = "select dbo.TotalLHCLoadingRate ( " _    + CStr(Range("MemberNo").Value) _    + ", getdate() ) "        MsgBox "TotalLHCLoadingRate SQL : " + mySql        objMyCmd.CommandText = mySql        objMyCmd.CommandType = adCmdText        objMyCmd.Execute        objMyRecordset.Open objMyCmd        Range("TotalLHCLoadingRate ").Value = ""        Range("TotalLHCLoadingRate ").CopyFromRecordset (objMyRecordset)        Range("TotalLHCLoadingRate ").Interior.ColorIndex = 37        MsgBox "TotalLHCLoadingRate  : " + CStr(Range("TotalLHCLoadingRate ").Value)        objMyRecordset.CloseEnd Sub