Visual Basic Q&A

Symptoms
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.
Resolution
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 executedfrom Visual Basic with the Execute method in Visual Basic as follows:

MyDb.Execute “sp_name”, dbSQLPassThroughi = MyDb.RowsAffectedYou can also use ExecuteSQL:

i = MyDb.ExecuteSQL(“sp_name”)However, this syntax is obsolete, and you should replace it with theExecute method and RowsAffected property syntax given at the beginningof this section.
The Execute (and ExecuteSQL) method runs the stored procedure sp_name.The RowsAffected property returns the number of rows the storedprocedure affected. This method is strictly for action queries such as:

Delete Authors where name like “fred%”Using Execute with an SQL statement that uses “SELECT…” returnsrecords that causes a run-time error.Stored procedures that return records (or rows) require a Snapshot-typeRecordset to capture the values. Listed below are two examples:
Example 1The following example using a Data Control on a Visual Basic Form:

Data1.Options = dbSQLPassThroughData1.Recordsource = “sp_name”‘ Name of the stored procedure.Data1.Refresh’ Refresh the data control.When you use the SQLPassThrough bit, the Microsoft Jet database engineignores the syntax used and passes the command through to the SQLserver.
Example 2Using Data Access Objects:

Dim Rs as Recordset’ Open your desired database here.Set MyDB = DBEngine.Workspaces(0).OpenDatabase(…Set Rs = MyDB.OpenRecordset(“sp_name”, dbOpenSnapshot, _dbSQLPassThrough) You must use dbOpenSnapshot. dbOpenDynaset and dbOpenTable do notapply to pass-through queries.
How to Pass Parameters to a Stored ProcedureTo pass parameters, include them after the name of the stored procedurein a string. For example:

‘ String specifying SQL.SQL = “My_StorProc parm1, parm2, parm3″…’ For a stored procedure that doesn’t return records.MyDb.Execute SQL, dbSQLPassThroughi = MyDb.RowsAffected…’For a stored procedure that returns records.set Rs = MyDB.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough) The object variable (Rs) contains the first set of results from thestored procedure (My_StorProc).
Another ExampleThe following contains more example code showing both methods:

Dim db as DatabaseDim l as LongDim Rs as RecordsetSet 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”, dbSQLPassThroughl = Db.RowsAffected’ For SPs that return rows.Set Rs = Db.OpenRecordset(“YourSP_Name”, dbOpenSnapshot, _dbSQLPassThrough)Col1.text = Rs(0) ‘ Column one.Col2.text = Rs!ColumnNameCol3.Text = Rs(“ColumnName”)

Symptoms
In Microsoft Windows Server 2003, you can use COM+ partitions to partition an application to run independently of other configurations of the application. Each configuration of the application is installed in a separate partition and can be separately managed. How you manage each configuration of an application depends on the specific needs of the application users. You can create an instance of an application that is hosted in these partitions by providing a COM+ partition moniker and a remote server name.
When you call the CreateObject method in Microsoft Visual Basic for Windows 6.0 Service Pack 5 or an earlier version, and you specify both a COM+ partition moniker and a remote server name, you receive the following error message:

Run-time error ‘463′: Class not registered on local machineNote The following syntax is used to call the CreateObject method to reproduce this problem:

‘CreateObject(“partition:{guid}/new:ProgID”,”servername”)Set p = CreateObject(“partition:{8A9B9DB1-684B-426B-BE5E-65E13F97B23A}/new:Project1.CallMe”, “REMOTESERVER”)
Resolution
Support for remote COM+ partitions is not implemented in the Microsoft Visual Basic runtime that is provided with Visual Basic for Windows 6.0 Service Pack 5 or an earlier version.