Visual Basic Q&A

Symptoms
The Terminal Services ActiveX client control does not expose the ImsTscNonScriptable interface. However, this interface can be used to configure automatic log on for a Terminal Services Session programmatically, which enables the programmer to log a user on to a Terminal Services Session without receiving the Windows Logon prompt. This is demonstrated in the sample in the “More Information” section.
Resolution
To run this program, configure your Terminal Server computer as follows: Log on to the Terminal Server locally as an administrator.On the Start button, click Programs, click Administrative Tools, and then click Terminal Services Configuration.Click on Connections.In the right pane, right-click RDP-Tcp, and then choose Properties.Click on the Logon Settings tab.Deselect Always prompt for password, and then click OK.NOTE: For security reasons, Microsoft recommends that you do not implement this scenario without extreme care and a clear understanding of Microsoft Windows security.
Sample CodeStart a new Standard EXE project. Form1 is created by default.On the Project menu, click to select Components, select Microsoft Terminal Services Control(redist), and then click OK. If this control is not available, see the “References” section of this article for information on how to download and install it.Add one Terminal Services Control to Form1, making sure it is big enough to handle the display of the session.Add three Label controls, three TextBox controls, and one CommandButton control to Form1. Make sure that Lable1 and Text1 are on the same line, and that Label2, Text2, Label3, and Text3 are on the same line.Paste the following code into the General Declarations of Form1:

‘ This code only works when you set the configuration on the Server-side.’ Log on to the Terminal Server as an administrator’ Start\Programs\Administrative Tools\Terminal Services Configuration’ Click on Connections’ On the Right Pane, right-click on RDP-Tcp and choose Properties’ Click on the “Logon Settings” Tab’ Uncheck “Always prompt for password” and click OKOption ExplicitPrivate Obj As IMsTscNonScriptablePrivate Sub Form_Load()Text1.Text = “”Text2.Text = “”Text3.Text = “”Label1.Caption = “Server”Label2.Caption = “UserName”Label3.Caption = “Password”Command1.Caption = “Connect”Text3.PasswordChar = “*”End SubPrivate Sub Command1_Click()Set Obj = MsTscAx1.ObjectMsTscAx1.Server = Text1.TextMsTscAx1.UserName = Text2.TextObj.ClearTextPassword = Text3.TextMsTscAx1.ConnectEnd Sub Save the project, press the F5 key to run it, and note that after you supply your username, password, and server name, you are not prompted for a logon screen at the server. Microsoft recommends that you enlarge the Terminal Server .ocx file so that you are able to manipulate the Shut Down dialog box.

Symptoms
This article describes how to attach and create Querydefs on external ODBCtables. The method for opening external ODBC tables is to attach the tablesto an .mdb file.
Jet does not support named QueryDefs on a non-attached ODBC database. A non-attached ODBC database is one that is opened directly with the OpenDatabasemethod of the WorkSpace object without the use of an .mdb file.
If it is not appropriate for the application to attach the ODBC tables, itis possible to create Querydefs with no name to accomplish the procedure.
For additional information, please see the following article in theMicrosoft Knowledge Base:
149055?(http://support.microsoft.com/kb/149055/EN-US/): Jet Doesn’t Support QueryDefs on a Non-Attached ODBC Table
Resolution
The following is information from “Guide To Data Access Objects,” Chapter7, Data Access Choices, that explains this procedure:
In many cases, attaching a table to access external data is faster thanopening a table directly, especially if the data is located in an ODBCdatabase. In Visual Basic version 4.0, SQL Passthrough is used to queryattached ODBC databases directly. Consider attaching external tables ratherthan opening them directly. Using external data in an ODBC databaserequires opening the external tables directly so performance issignificantly slower when using the data.
Sample ProgramThe following example describes how to attach to and create a Querydef onan ODBC table using a “DSN-less” ODBC connection. With this procedure, itis not necessary to set up a DSN with the ODBC Admin utility.
Start a new project in Visual Basic. Form1 is created by default.Add three Command buttons to Form1: Command1, Command2, Command3 bydefault.Paste the following code in the General Declarations section of Form1:

Dim db As DatabaseDim cn As StringPrivate Sub Form_Load()cn = “odbc;driver={SQL Server};server=myserver;” & _”database=pubs;uid=myuid;pwd=mypwd”If Dir(“mydb.mdb”) <> “” Then’ database exists, so just open it.Set db = OpenDatabase(Name:=”mydb”, Exclusive:=False, _ReadOnly:=False, Connect:=”")Else’database does not exist, create it and attach authors table.Set db = CreateDatabase(Name:=”mydb”, Connect:=dbLangGeneral, _Option:=dbVersion30)Dim td As TableDefSet td = db.CreateTableDef()td.Name = “Authors”td.SourceTableName = “Authors”td.Connect = cnEnd IfEnd SubPrivate Sub Command1_Click()Dim qd As QueryDefOn Error Resume NextSet qd = db.QueryDefs(“abc”)’ test for existence of querydef.If Error > 0 ThenSet qd = db.CreateQueryDef(Name:=”abc”)qd.Connect = cnqd.SQL = “Select @@Version” ‘native SQL ServerEnd IfSet qd = db.QueryDefs(“xyz”)’ test for existence of querydef.If Error > 0 ThenSet qd = db.CreateQueryDef(Name:=”xyz”)qd.Connect = cnqd.SQL = “Select * from titles” ‘ generic SQL.End IfOn Error GoTo 0End SubPrivate Sub Command2_Click()Dim rs As RecordsetDim qd As QueryDefSet qd = db.QueryDefs(“abc”)Set rs = qd.OpenRecordset()Call displayResults(rs)End SubPrivate Sub Command3_Click()Dim rs As RecordsetDim qd As QueryDefSet qd = db.QueryDefs(“xyz”)Set rs = qd.OpenRecordset()Call displayResults(rs)End SubSub displayResults(rs As Recordset)Dim f As Field, s As String, i As IntegerFor Each f In rs.Fieldss = s & f.NameNext fDebug.Print s’ print column headers.While Not rs.EOF And i < 5s = “”For Each f In rs.Fieldss = s & f.ValueNext fDebug.Print s’ print first 5 rows.rs.MoveNexti = i + 1WendEnd Sub NOTE: You need to change the DRIVER, SERVER, DATABASE, UID, and PWDparameters in the OpenConnection method. Also you must modify the SQLstatements contained in the Command1_Click event to match your SQL datasource.
Press the F5 key to start the program.Click the Command1 button to create the Querydefs. Click the Command2and Command3 buttons to execute the Querydefs. Note that the first fiverows of data appear in the Debug window.

Symptoms
This article describes how to get more information on the ODBC Call Failederror. When errors occur using ODBC databases, Visual Basic will provide an”ODBC Call Failed” error message. This generic error message provides nospecific detail so you must cycle through the Errors collection to getadditional information. Below is a code sample that shows the difference inbehavior.
Resolution
The DBEngine has an Errors collection that can be manipulated by the FOR-EACH construct. The JET Engine can store multiple errors in the DBEngineErrors collection. In Visual Basic 3.0, it was possible to parse the stringusing the routine shown on Page 175 of the Visual Basic 4.0 ProfessionalFeatures Book under the “Guide to Data Access Objects” section. The #symbol was used to separate the “ODBC Call Failed” message from thedetailed ODBC description in Visual Basic 3.0. However, this is notnecessary under Visual Basic versions 4.0 and 5.0.
For the example below, a two-field table called MyTable has been set up onan ODBC Source and a primary key set on the ID Field. Two records have beenadded as below:

FieldIDDescription===============================Record 11HelloRecord 22World
The code below will generate an error by trying to add a record with aduplicate primary key value to test the code:
Start a new Standard EXE project. Form1 is added by default.Add a CommandButton to Form1.Add the following code to the General Declarations section of Form1:

Option ExplicitPrivate Sub Command1_Click()Dim db As DatabaseDim rs As RecordsetOn Error GoTo trapSet db = OpenDatabase(“”)Set rs = db.OpenRecordset(“Select * from MyTable”)rs.AddNewrs.Fields(0).Value = 2rs.UpdateExit Subtrap:MsgBox Errors.CountMsgBox Err.Number & ” ” & Err.DescriptionEnd Sub Press the F5 key to run the project. Click on the CommandButton and youshould receive error 3146, “ODBC Call Failed.” Although the Error countis greater than one, only one message will be displayed.Remove the code from within the error trap and replace it with oneof the following error handlers:

‘ DAO Error HandlerDim MyError As ErrorMsgBox Errors.CountFor Each MyError In DBEngine.ErrorsWith MyErrorMsgBox .Number & ” ” & .DescriptionEnd WithNext MyError’ RDO Error HandlerDim MyError As rdoErrorMsgBox rdoErrors.CountFor Each MyError In rdoEngine.rdoErrorsWith MyErrorMsgBox .Number & ” ” & .DescriptionEnd WithNext MyError Press the F5 key to run the project. You should see a detailed messageand then the 3146 Error for “ODBC Call Failed.”

Symptoms
The UserConnection Designer is one of the new ActiveX Designers includedwith Visual Basic 5.0 and higher. It allows you to wrap SQL or a StoredProcedure with code at design time so you can call it as a method at runtime. This way, Visual Basic will list the parameters of your storedprocedure in the object browser just as it does with any other VBA method.This new functionality can replace the rdoPreparedStatement and rdoQueryobjects, although these are still available.
The following is an excerpt on the UserConnection from Books Online:
The UserConnection designer uses Visual Basic’s ActiveX designerarchitecture to provide design-time support for programmatic dataaccess. It allows you to create connection and query objects atdesign time. These connections and queries are persisted as project-levelobjects. You can pre-set properties, define new properties and methods,and write code behind the objects to catch events.
Resolution
This sample uses SQL Server and the Pubs database, but you can change theDSN, UID, PWD, and SQL to match any ODBC database you may be using.
Task One: Create the Example Stored ProcedureThis sample SQL Server stored procedure accepts an input parameter ofau_id and returns a resultset and a test output variable. The testoutput variable is hard-coded and has no meaning except to demonstratehow to return an output using a UserConnection. If you are not usingSQL Server, you might have to modify the SQL into an acceptable formatfor your database server.To create this stored procedure you will need a tool that allows you toexecute SQL such as ISQL/W or MSQuery included with SQL Server. Selectthe Pubs database, then place the following Create Procedure T-SQL inthe SQL window of your selected tool and execute it:

Create procedure GetAuthorInfo@au_id Varchar(11), @testOut Varchar(10) OUTPUT AsSelect * from authors Where au_id = @au_idSelect @testOut = ‘Hello’
Task Two: Create the UserconnectionStart a new project in Visual Basic and choose “Standard EXE.” Form1 iscreated by default.From the Project menu, select Components, select the Designer tab, andthen place a check next to Microsoft UserConnection.From the Project menu, select Add ActiveX Designer, then selectMicrosoft UserConnection. This will bring up a dialog titledUserConnection1 Properties.On the Connection tab, select either a DSN or a DSN-Less connection andfill in the appropriate information. If you select DSN-less, make sureyou specify a database in the Other ODBC Attributes area withdatabase=pubs. On the Authentication tab, fill in your username andpassword, and place a check next to Save Connection Information forDesign time. On the Miscellaneous tab in the Other section, choose UseODBC Cursor Library because you have more than one Select statement inyour stored procedure.Click OK to save this information, and return to the Designer window.Press the F4 key to display the Properties window and change the Nameproperty from UserConnection1 to StoredProcs.Insert a new Query by right-clicking on StoredProcs and choosingInsert Query or by clicking on the Insert Query toolbar icon. Change thename of the Query from Query1 to GetAuthorInfo. From the Source ofQuery, choose Based on Stored Procedure, then select the storedprocedure you created earlier, GetAuthorInfo.If you select the Parameters tab, you can review the input and outputparameters. Your GetAuthorInfo Query setup is now finished. ClickOK to close the Dialog.To place code in the connection event of your UserConnection, selectView Code from the UserConnection toolbar. Choose UserConnection infrom the upper-left combo box, then choose the Connect Event from theupper-right combo box. Place the following code in the UserConnectionConnect event:

Debug.Print “Connect”
Task Three: The Userconnection CodeAdd a CommandButton, Command1, to Form1 of Project1.Paste the following code in the General Declarations section ofForm1:

Private Sub Command1_Click()Dim objSP As New StoredProcs’creatable UC objectDim objRs As rdoResultset’pointer to rdoResultsetDim lngRet As Long, strOut As String’vars to hold outputobjSP.EstablishConnection rdDriverNoPrompt’establish connectionlngRet = objSP.GetAuthorInfo(“648-92-1872″, strOut) ‘SP methodSet objRs = objSP.LastQueryResults’get results off objRsDebug.Print lngRet, strOut, objRs(0)’output example resultsEnd Sub Start the program or press the F5 key.Click the Command1 button to execute the UserConnection code. TheStored Procedure return value, output value, and the first column ofthe returned resultset will print in the Debug window.

Symptoms
Microsoft Access allows you to modify an existing field’s data type. To do so programmatically, Microsoft Jet 4.0 introduces the ALTER TABLE ALTER COLUMN DDL statement. However, there is no equivalent for Microsoft Jet 3.5.
This article demonstrates a method to alter a field’s data type using DAO objects.
Resolution
Modifying a field’s data type requires the following steps:Rename the old field.Add a new field.Copying the data from the old field to the new field.Delete the old field.If the table has any indexes or relations, the relationships and indexes must be dropped prior to performing the steps above, then re-established after completion of the steps above.
Microsoft Access handles indexes but not relationships when changing data types.
The Jet 4.0 ALTER TABLE ALTER COLUMN DDL statement has similar limitations.
The sample code provided handles both indexes and relationships.It also contains error handling to roll back the changes and report on any problems.
The main procedure is ChangeFieldType. It takes the following arguments:db – an open Database object where the table resides.TableName – the name of the table where the field resides.FieldName – the name of the field to be changed.NewType – the new data type for the field.NewAllowZeroLength – new value for the AllowZeroLength property.NewAllowNulls – used to set the Required property of the new field.NewAttributes – used to set the Attributes property of the new field.Note: This procedure is for illustration purposes only. For example, the procedure copies only basic field properties. In addition to these basic field properties, other field properties might also have to be copied. These additional field properties include ValidationRule, ValidationText, DecimalPlaces, and others, depending on the field type. In addition, the procedure does not copy user-defined properties.
The other procedures, RecordRelationInfo, RecordIndexInfo, IsField, and MakeArray, are helper procedures used by the main function.
Sample CodeThis sample changes the CustomerID field in the Customers table from a five character field to an eight character field.
The sample uses the Nwind database that comes with Visual Basic.
In Visual Basic, create a new Standard EXE project.
Form1 is created by default.Add a command button to Form1. Command1 is created by default.On the Project menu, select References.
In the References dialog, select the Microsoft DAO Object Library.On the Project menu, select Add Module to add a Code Module.
Module1 is created by default.Paste the following code into the General Declarations section of Module1’s Code Window:

Option Compare TextOption ExplicitConst CFT_Failed As Long = 55555Private Const R_NAME = 0, R_ATTRIBUTES = 1, R_TABLE = 2, R_FOREIGNTABLE = 3, R_FIELD = 4, R_FOREIGNFIELD = 5Private Const I_NAME = 0, I_PRIMARY = 1, I_UNIQUE = 2, I_REQUIRED = 3, I_IGNORENULLS = 4, I_CLUSTERED = 5, I_FIELD = 6, I_FIELDATTRIBUTES = 7Public Sub ChangeFieldType(db As Database, _ByVal TableName As String, _ByVal FieldName As String, _ByVal NewType As Integer, _Optional NewSize As Long, _Optional NewAllowZeroLength As Boolean = False, _Optional NewAllowNulls As Boolean = True, _Optional NewAttributes As Long)’ User-defined properties are not maintainedDim td As TableDef, I As Index, R As Relation, F As Field’ loop iterators for Indexes, Fields, and Relations collections:Dim I1 As Long, F1 As Long, R1 As LongDim colR As Collection, colI As CollectionDim E_Desc As String, Process As String, SubProcess As String, E As ErrorDim TempFieldName As String, Suffix As Long, OldName As StringDim Temp As VariantDim OrdinalPosition As LongSet colI = New CollectionSet colR = New CollectionOn Error GoTo CFT_ErrDBEngine(0).BeginTrans’ Enumerate relations and save/remove themDBEngine(0).BeginTransProcess = “Removing relations on [" & TableName & "]![" & FieldName & "]“SubProcess = “”For R1 = db.Relations.Count – 1 To 0 Step -1Set R = db.Relations(R1)If R.Table = TableName ThenFor F1 = 0 To R.Fields.Count – 1Set F = R.Fields(F1)If F.Name = FieldName ThenRecordRelationInfo R, colRSubProcess = “Removing relation ” & R.Namedb.Relations.Delete R.NameExit ForEnd IfNext F1ElseIf R.ForeignTable = TableName ThenFor F1 = 0 To R.Fields.Count – 1Set F = R.Fields(F1)If F.ForeignName = FieldName ThenRecordRelationInfo R, colRSubProcess = “Removing relation ” & R.Namedb.Relations.Delete R.NameExit ForEnd IfNext F1End IfNext R1Set F = NothingSet R = NothingDBEngine(0).CommitTrans’ Enumerate indices and save/remove themDBEngine(0).BeginTransProcess = “Removing indexes on [" & TableName & "]![" & FieldName & "]“SubProcess = “”db.TableDefs.RefreshSet td = db(TableName)td.Indexes.RefreshFor I1 = td.Indexes.Count – 1 To 0 Step -1Set I = td.Indexes(I1)If I.Foreign <> True ThenFor F1 = 0 To I.Fields.Count – 1Set F = I.Fields(F1)If F.Name = FieldName ThenRecordIndexInfo I, colISubProcess = “Removing index ” & I.Nametd.Indexes.Delete I.NameExit ForEnd IfNext F1End IfNext I1Set F = NothingSet I = NothingDBEngine(0).CommitTrans’ Rename FieldDBEngine(0).BeginTransProcess = “Renaming field”SubProcess = “”td.Fields.RefreshSet F = td(FieldName)OrdinalPosition = F.OrdinalPosition’ save this value’ determine a field name not in useSuffix = 0DoSuffix = Suffix + 1TempFieldName = “XXX” & SuffixLoop While IsField(td, TempFieldName)’ rename the fieldSubProcess = “to ” & TempFieldNameF.Name = TempFieldNameSet F = NothingDBEngine(0).CommitTrans’ Add new FieldDBEngine(0).BeginTransProcess = “Adding new field”SubProcess = “”td.Fields.RefreshSet F = td.CreateField(FieldName, NewType)If NewSize Then F.Size = NewSizeF.AllowZeroLength = NewAllowZeroLengthF.Required = Not NewAllowNullsF.Attributes = NewAttributesF.OrdinalPosition = OrdinalPositiontd.Fields.Append FSet F = NothingSet td = NothingDBEngine(0).CommitTrans’ Copy dataDBEngine(0).BeginTransProcess = “Copying data from ” & TempFieldName & ” to ” & FieldNameSubProcess = “”db.Execute “UPDATE [" & TableName & "] SET [" & FieldName & "]=[" & _TempFieldName & "]“, dbFailOnErrorDBEngine(0).CommitTrans’ Delete temporary fieldDBEngine(0).BeginTransProcess = “Deleting temporary field ” & TempFieldNameSubProcess = “”Set td = db(TableName)td.Fields.Delete TempFieldNameDBEngine(0).CommitTrans’ Add back IndicesDBEngine(0).BeginTransProcess = “Adding indexes back into table”SubProcess = “”Set td = db(TableName)td.Fields.Refreshtd.Indexes.RefreshOldName = “”Set I = NothingFor Each Temp In colIIf Temp(I_NAME) <> OldName ThenIf Not (I Is Nothing) Then’ handle first time through caseSubProcess = “Adding index ” & I.Nametd.Indexes.Append IEnd IfSet I = td.CreateIndex(Temp(I_NAME))I.Primary = Temp(I_PRIMARY)I.Unique = Temp(I_UNIQUE)I.Required = Temp(I_REQUIRED)I.IgnoreNulls = Temp(I_IGNORENULLS)I.Clustered = Temp(I_CLUSTERED)End IfSet F = I.CreateField(Temp(I_FIELD))F.Attributes = Temp(I_FIELDATTRIBUTES)’ to handle descending indexI.Fields.Append FNext TempIf Not (I Is Nothing) Then’ handle case of no indexesSubProcess = “Adding index ” & I.Nametd.Indexes.Append IEnd IfSet F = NothingSet I = NothingSet td = NothingDBEngine(0).CommitTrans’ Add back relationsDBEngine(0).BeginTransProcess = “Adding relations back into database”SubProcess = “”OldName = “”db.Relations.RefreshSet R = NothingFor Each Temp In colRIf Temp(I_NAME) <> OldName ThenIf Not (R Is Nothing) Then’ handle first time through caseSubProcess = “Adding relation ” & R.Namedb.Relations.Append REnd IfSet R = db.CreateRelation(Temp(R_NAME), Temp(R_TABLE), _Temp(R_FOREIGNTABLE), Temp(R_ATTRIBUTES))End IfSet F = R.CreateField(Temp(R_FIELD))F.ForeignName = Temp(R_FOREIGNFIELD)R.Fields.Append FNext TempIf Not (R Is Nothing) Then’ if there are no indexes…SubProcess = “Adding relation ” & R.Namedb.Relations.Append REnd IfSet F = NothingSet R = NothingDBEngine(0).CommitTrans’ Commit all pending chhangesDBEngine(0).CommitTransExit SubCFT_Abort:On Error Resume NextSet F = NothingSet td = NothingDBEngine(0).RollbackDBEngine(0).RollbackErr.ClearOn Error GoTo 0Err.Raise CFT_Failed, “ChangeFieldType”, E_DescExit SubCFT_Err:E_Desc = “Error ” & ProcessIf SubProcess <> “” Then E_Desc = E_Desc & vbCrLf & SubProcessIf DBEngine.Errors.Count = 0 ThenE_Desc = E_Desc & vbCrLf & “Error ” & Err.Number & ” ” & _Err.DescriptionElseFor Each E In DBEngine.ErrorsE_Desc = E_Desc & vbCrLf & “Error ” & E.Number & ” (” & _E.Source & “) ” & E.DescriptionNext EEnd IfDebug.Print E_DescResume CFT_AbortEnd SubPrivate Sub RecordRelationInfo(ByVal R As Relation, colR As Collection)’ Records information regarding the relationship and its fields’ in the colR collection.Dim F1 As Long, F As FieldFor F1 = 0 To R.Fields.Count – 1Set F = R.Fields(F1)colR.Add MakeArray(R.Name, R.Attributes, R.Table, R.ForeignTable, _F.Name, F.ForeignName)Next F1End SubPrivate Sub RecordIndexInfo(ByVal I As Index, colI As Collection)’ Records information about fields in the index and about the index itself’ into the colI collection.Dim F1 As Long, F As FieldFor F1 = 0 To I.Fields.Count – 1Set F = I.Fields(F1)colI.Add MakeArray(I.Name, I.Primary, I.Unique, I.Required, _I.IgnoreNulls, I.Clustered, F.Name, F.Attributes)Next F1End SubPrivate Function IsField(td As TableDef, ByVal FieldName As String) _As Boolean’ Returns TRUE if a field exists in the table with the same name as’specified in FieldName.’ Returns FALSE otherwise.Dim F As FieldErr.ClearOn Error Resume NextSet F = td(FieldName)IsField = Err.Number = 0Err.ClearEnd Function Private Function MakeArray(ParamArray X() As Variant) As Variant’ Does the same thing as the Array() function in VB6MakeArray = XEnd Function If necessary, change the CFT_Failed constant to use an error number that conforms to your company’s standards.Paste the following code into the General Declarations section of Form1’s Code Window:

Private Sub Command1_Click()Dim strDB As StringstrDB = “c:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb”Dim db As DAO.DatabaseSet db = DBEngine(0).OpenDatabase(strDB)ChangeFieldType db, “Customers”, “CustomerID”, dbText, 8db.CloseEnd Sub If necessary, modify strDB to use your Nwind database.Run the sample project.
Click the command button.
End the project.Examine the table in Microsoft Access or the Visual Basic Visual Database Manager add-in.
Note that the field has been resized.

Symptoms
This article describes how to call system-stored procedures on SQL Serverfrom RDO.
SQL Server has a number of prewritten, stored procedures that are used toconfigure and administer the SQL server. They are all located in the Masterdatabase and have the prefix [ASCII 147]sp_,[ASCII 148] which distinguishes them fromuser- or application-written stored procedures.
In order to call these stored procedures from RDO, you must specify thecorrect database in which they reside, the master database. There areseveral ways to do this, but the most effective way is to explicitlyreference the stored procedure in your call syntax. For example:

{ ? = call master.dbo.sp_addlogin(?,?) }
Another method to accomplish this behavior is to set the default databasebefore creating and executing your stored procedure. The following exampleuses a prepared statement and assumes that “Pubs” is your default database,cn is an active rdoConnection object, and qr is an rdoQuery object:

cn .Execute “Use Master”qr.SQL= “some sql to execute…”Set qr.ActiveConnection = cnqr .Executecn .Execute “Use Pubs”
Resolution
Start a new Standard EXE project. Form1 is created by default.Add a CommandButton (Command1) to Form1.From the Project menu, select References, and place a check next toMicrosoft Remote Data Object 2.0.Paste the following code into the General Declarations section of Form1:

Private Sub Command1_Click()Dim cn As New rdoConnectionDim qr As New rdoqueryWith cn.Connect = “Driver={SQL Server};Server=MyServer;” & _”Database=pubs;Uid=<username>;Pwd=<strong password>”.EstablishConnection Prompt = rdDriverNoPromptEnd WithWith qrSet .ActiveConnection = cn’Note: If you don’t specify master, you will get the following’error: “An invalid parameter was passed.”.SQL = “{ ? = call master.dbo.sp_addlogin(?,?) }”.Prepared = True.BindThreshold = 1024 ‘largest column that will be bound under’ODBC.Debug.Print .rdoParameters.Count.rdoParameters(0).Direction = rdParamOutput.rdoParameters(1).Direction = rdParamInput.rdoParameters(2).Direction = rdParamInput.rdoParameters(1) = “Edward”.rdoParameters(2) = “Scissorhands”.ExecuteDebug.Print .rdoParameters(0).Value’Const SQL_SUCCESS As Long = 0′Const SQL_ERROR As Long = -1End WithEnd Sub Note that you need to change your DATABASE, UID, and PWD parameters inthe Connect Property.Start the program or press the F5 key.Click the Command1 button to execute the stored procedure and displaythe parameter count and the output parameter in the debug window.