Calling stored procedure using VBA

by

Victoria,

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

Set mobjConn = New ADODB.Connection
mobjConn.Open "your connection string"
Set mobjCmd = New ADODB.Command
With 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

    .Execute
End 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

More Related Contents:

  1. ODBC Call Failed with stored procedure – Pass through query
  2. How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?
  3. SQL Server Passthrough query as basis for a DAO recordset in Access
  4. T-SQL stored procedure that accepts multiple Id values
  5. Select columns from result set of stored procedure
  6. SQL Server: Query fast, but slow from procedure
  7. Retrieve column definition for stored procedure result set
  8. Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?
  9. How to list all dates between two dates [duplicate]
  10. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  11. EXEC sp_executesql with multiple parameters
  12. How do I find a stored procedure containing ?
  13. How do I search an SQL Server database for a string?
  14. Calling an API from SQL Server stored procedure
  15. SQL Server history table – populate through SP or Trigger?
  16. Retrieve data from stored procedure which has multiple result sets
  17. Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
  18. Stored Procedure and Permissions – Is EXECUTE enough?
  19. #DELETE viewing SQL Server table in Access
  20. Avoid Naming User Stored Procedures SP% or SP_%
  21. Execute stored procedure with an Output parameter?
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. Stored procedure – return identity as output parameter or scalar
  24. How to find a text inside SQL Server procedures / triggers?
  25. Python – pyodbc call stored procedure with parameter name
  26. SQL Server stored procedure return code oddity
  27. How to make a passthrough / passthru query editable?
  28. Populate Dataset With Table Names From Stored Procedure
  29. How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?
  30. Why can’t Entity Framework see my Stored Procedure’s column information?

Leave a Comment