How To Call Stored Procedures Using Data Access Objects
This article was previously published under Q184749
This article has been archived. It is offered "as is" and will no longer be updated.
This article describes how to use Data Access Objects (DAO) to callMicrosoft SQL stored procedures from Microsoft Visual Basic for Windows. Astored procedure is a precompiled collection of SQL statements, oftenincluding control-of-flow language.
NOTE: This article assumes you already know how to open an ODBC database.
The method of calling depends on whether the SQL stored procedure returnsrecords or not. For example:
- Stored procedures that do not return records (or rows) can be executed from Visual Basic with the Execute method in Visual Basic as follows:You can also use ExecuteSQL:
MyDb.Execute "sp_name", dbSQLPassThrough i = MyDb.RowsAffectedHowever, this syntax is obsolete, and you should replace it with the Execute method and RowsAffected property syntax given at the beginning of this section.
i = MyDb.ExecuteSQL("sp_name")
The Execute (and ExecuteSQL) method runs the stored procedure sp_name. The RowsAffected property returns the number of rows the stored procedure affected. This method is strictly for action queries such as:Using Execute with an SQL statement that uses "SELECT..." returns records that causes a run-time error.
Delete Authors where name like "fred%"
- Stored procedures that return records (or rows) require a Snapshot-type Recordset to capture the values. Listed below are two examples:
Example 1The following example using a Data Control on a Visual Basic Form:When you use the SQLPassThrough bit, the Microsoft Jet database engine ignores the syntax used and passes the command through to the SQL server.
Data1.Options = dbSQLPassThrough Data1.Recordsource = "sp_name" ' Name of the stored procedure. Data1.Refresh ' Refresh the data control.
Example 2Using Data Access Objects:You must use dbOpenSnapshot. dbOpenDynaset and dbOpenTable do not apply to pass-through queries.
Dim Rs as Recordset ' Open your desired database here. Set MyDB = DBEngine.Workspaces(0).OpenDatabase(... Set Rs = MyDB.OpenRecordset("sp_name", dbOpenSnapshot, _ dbSQLPassThrough)
How to Pass Parameters to a Stored ProcedureTo pass parameters, include them after the name of the stored procedure in a string. For example:The object variable (Rs) contains the first set of results from the stored procedure (My_StorProc).
' String specifying SQL. SQL = "My_StorProc parm1, parm2, parm3" ... ' For a stored procedure that doesn't return records. MyDb.Execute SQL, dbSQLPassThrough i = MyDb.RowsAffected ... 'For a stored procedure that returns records. set Rs = MyDB.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough)
Another ExampleThe following contains more example code showing both methods:
Dim db as Database Dim l as Long Dim Rs as Recordset Set Db = DBEngine.Workspaces(0).OpenDatabase _ ("", False, False, "ODBC;dsn=yourdsn;uid=youruid;pwd=yourpwd:") ' For SPs that don't return rows. Db.Execute "YourSP_Name", dbSQLPassThrough l = Db.RowsAffected ' For SPs that return rows. Set Rs = Db.OpenRecordset("YourSP_Name", dbOpenSnapshot, _ dbSQLPassThrough) Col1.text = Rs(0) ' Column one. Col2.text = Rs!ColumnName Col3.Text = Rs("ColumnName")
For additional information about calling stored procedures, please see thefollowing:
Microsoft SQL Server "Microsoft SQL Server Programmer's Reference for Visual Basic," version 4.2, pages 200-201
See the functions SqlRpcInit%, SqlRpcParam%, and SqlRpcSend%. Thesefunctions call stored procedures more quickly than the methods describedabove.
kbODBC kbVBp500 kbVBp600 kbVBp400 kbdse kbDSupport kbVBp
Article ID: 184749 - Last Review: 12/05/2015 08:36:58 - Revision: 1.2
Microsoft Visual Basic 5.0 Professional Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft Visual Basic 5.0 Enterprise Edition, Microsoft Visual Basic 6.0 Enterprise Edition, Microsoft Visual Basic 4.0 Professional Edition, Microsoft Visual Basic 4.0 Professional Edition, Microsoft Visual Basic 4.0 16-bit Enterprise Edition, Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
- kbnosurvey kbarchive kbhowto KB184749