Visual Basic Q&A

Symptoms
ActiveX Data Objects (ADO) offers several ways to open both the Connection and Recordset objects. This article presents sample code for several common techniques for each object.
Resolution
There are several ways to open a Connection Object within ADO:
By Setting the ConnectionString property to a valid Connect string and then calling the Open() method. This connection string is provider- dependent.By passing a valid Connect string to the first argument of the Open() method.By passing a valid Command object into the first argument of a Recordset’s Open method.By passing the ODBC Data source name and optionally user-id and password to the Connection Object’s Open() method. There are three ways to open a Recordset Object within ADO:
By opening the Recordset off the Connection.Execute() method.By opening the Recordset off the Command.Execute() method.By opening the Recordset object without a Connection or Command object, and passing an valid Connect string to the second argument of the Recordset.Open() method. This code assumes that Nwind.mdb is installed with Visual Basic, and is located in the C:\Program Files\DevStudio\VB directory:

Option ExplicitPrivate Sub cmdOpen_Click()Dim Conn1 As New adodb.ConnectionDim Cmd1 As New adodb.CommandDim Errs1 As ErrorsDim Rs1 As New adodb.RecordsetDim i As IntegerDim AccessConnect As String’ Error Handling VariablesDim errLoop As ErrorDim strTmp As StringAccessConnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=nwind.mdb;” & _”DefaultDir=C:\program files\devstudio\vb;” & _”Uid=Admin;Pwd=;”‘—————————’ Connection Object Methods’—————————On Error GoTo AdoError’ Full Error Handling which traverses’ Connection object’ Connection Open method #1:Open via ConnectionString PropertyConn1.ConnectionString = AccessConnectConn1.OpenConn1.CloseConn1.ConnectionString = “”‘ Connection Open method #2:Open(“[ODBC Connect String]“,”",”")Conn1.Open AccessConnectConn1.Close’ Connection Open method #3:Open(“DSN”,”Uid”,”Pwd”)Conn1.Open “Driver={Microsoft Access Driver (*.mdb)};” & _”DBQ=nwind.mdb;” & _”DefaultDir=C:\program files\devstudio\vb;” & _”Uid=Admin;Pwd=;”Conn1.Close’————————–’ Recordset Object Methods’————————–’ Don‘t assume that we have a connection object.On Error GoTo AdoErrorLite’ Recordset Open Method #1:Open via Connection.Execute(…)Conn1.Open AccessConnectSet Rs1 = Conn1.Execute(“SELECT * FROM Employees”)Rs1.CloseConn1.Close’ Recordset Open Method #2:Open via Command.Execute(…)Conn1.ConnectionString = AccessConnectConn1.OpenCmd1.ActiveConnection = Conn1Cmd1.CommandText = “SELECT * FROM Employees”Set Rs1 = Cmd1.ExecuteRs1.CloseConn1.CloseConn1.ConnectionString = “”‘ Recordset Open Method #3:Open via Command.Execute(…)Conn1.ConnectionString = AccessConnectConn1.OpenCmd1.ActiveConnection = Conn1Cmd1.CommandText = “SELECT * FROM Employees”Rs1.Open Cmd1Rs1.CloseConn1.CloseConn1.ConnectionString = “”‘ Recordset Open Method #4:Open w/o Connection & w/Connect StringRs1.Open “SELECT * FROM Employees”, AccessConnect, adOpenForwardOnlyRs1.CloseDone:Set Rs1 = NothingSet Cmd1 = NothingSet Conn1 = NothingExit SubAdoError:i = 1On Error Resume Next’ Enumerate Errors collection and display properties of’ each Error object (if Errors Collection is filled out)Set Errs1 = Conn1.ErrorsFor Each errLoop In Errs1With errLoopstrTmp = strTmp & vbCrLf & “ADO Error # ” & i & “:”strTmp = strTmp & vbCrLf & “ADO Error# ” & .NumberstrTmp = strTmp & vbCrLf & “Description” & .DescriptionstrTmp = strTmp & vbCrLf & “Source” & .Sourcei = i + 1End WithNextAdoErrorLite:’ Get VB Error Object’s informationstrTmp = strTmp & vbCrLf & “VB Error # ” & Str(Err.Number)strTmp = strTmp & vbCrLf & “Generated by ” & Err.SourcestrTmp = strTmp & vbCrLf & “Description” & Err.DescriptionMsgBox strTmp’ Clean up gracefully without risking infinite loop in error handlerOn Error GoTo 0GoTo DoneEnd Sub
ERROR NOTES Only the ADO Connection object has an errors collection. The observant reader will notice that a lightweight error handler is in effect for the RecordSet.Open examples. In the event of an error opening a RecordSet object, ADO should return the most explicit error from the OLEDB provider. Some common errors that can be encountered with the preceding code follow.
If you omit (or there is an error in) the DefaultDir parameter in the connect string, you may receive the following error:

ADO Error # -2147467259
Description [Microsoft][ODBC Microsoft Access 97 Driver] ‘(unknown)’
isn’t a valid path. Make sure that the path name is
spelled correctly and that you are connected to the server
on which the file resides.
Source Microsoft OLE DB Provider for ODBC Drivers
If there is an error in the Dbq parameter in the connect string, you may receive the following error:

ADO Error # -2147467259 Description [Microsoft][ODBC Microsoft Access 97 Driver] Couldn’t find
file ‘(unknown)’.
Source Microsoft OLE DB Provider for ODBC Drivers
The preceding errors also populate the Connection.Errors collection with the following errors:

ADO Error # -2147467259
Description [Microsoft][ODBC Driver Manager] Driver’s
SQLSetConnectAttr failed
Source Microsoft OLE DB Provider for ODBC Drivers

ADO Error # -2147467259
Description Login Failed
Source Microsoft OLE DB Provider for ODBC Drivers Note that for each error, the ADO Error number is the same, in this case translating to 0×80004005, which is the generic E_FAIL error message. The underlying Component did not have a specific error number for the condition encountered, but useful information was never-the-less raised to ADO.

Symptoms
In many applications there is a function to search and highlight keywordsin a text window. The RichTextBox control in Visual Basic can be madeto provide this functionality, as shown in the sample code below.
Resolution
Start a new project in Visual Basic. Form1 is created by default.Place a Command button and a RichTextBox on Form1. Set the Text propertyof the RichTextBox to “This is an example of finding text in a richtext box.”Add the following code to the General Declarations section of Form1:

Option ExplicitPrivate Sub Command1_Click()HighlightWords RichTextBox1, “text”, vbRedEnd SubPrivate Function HighlightWords(rtb As RichTextBox, _sFindString As String, _lColor As Long) _As IntegerDim lFoundPos As Long’Position of first character’of matchDim lFindLength As Long’Length of string to findDim lOriginalSelStart As LongDim lOriginalSelLength As LongDim iMatchCount As Integer’Number of matches’Save the insertion points current location and lengthlOriginalSelStart = rtb.SelStartlOriginalSelLength = rtb.SelLength’Cache the length of the string to findlFindLength = Len(sFindString)’Attempt to find the first matchlFoundPos = rtb.Find(sFindString, 0, , rtfNoHighlight)While lFoundPos > 0iMatchCount = iMatchCount + 1rtb.SelStart = lFoundPos’The SelLength property is set to 0 as’soon as you change SelStartrtb.SelLength = lFindLengthrtb.SelColor = lColor’Attempt to find the next matchlFoundPos = rtb.Find(sFindString, _lFoundPos + lFindLength, , rtfNoHighlight)Wend’Restore the insertion point to its original’location and lengthrtb.SelStart = lOriginalSelStartrtb.SelLength = lOriginalSelLength’Return the number of matchesHighlightWords = iMatchCountEnd Function Choose Start from the Run menu, or press the F5 key to start theproject. Click the Command button and you should see that bothoccurrences of the word “text” are now shown in red.

Symptoms
As its name suggests, the MSHFlexGrid control that ships with Visual Basic6.0 allows database data to be displayed in an aggregated, hierarchicalformat. Using this control in conjunction with the Data EnvironmentDesigner, developers can easily and logically display information containedin several different database tables, which are connected through the useof foreign keys.
Resolution
The Data Environment Designer is a tool that enables developers toencapsulate ActiveX Data Object (ADO) connection and command objects in areusable Visual Basic object. This sample project makes use of a DataEnvironment Designer to establish a connection to SQL Server and toretrieve related data from two tables in the Pubs sample database viacommand objects. The related data is then displayed in a hierarchicalformat in the MSHFlexGrid control.
To create this sample, do the following:Create a new Standard EXE project. Form1 is created by default.On the Project menu, click Components, and click the Designers tab inthe Components window. On the Designers tab, make sure that the DataEnvironment check box is selected. On the Controls tab, select the checkbox next to the Microsoft Hierarchical FlexGrid Control 6.0 option. Thenclick OK to exit the Components window.On the Project menu, click Add Data Environment, which adds theDataEnvironment1 object to your project.If the object window for the DataEnvironment1 object is not visible,right-click the DataEnvironment1 object in Project Explorer and selectView Object.In the DataEnvironment1 object window, right-click the Connection1 iconpictured beneath the DataEnvironment1 icon and select the Propertiesoption from the pop-up menu (this displays the Data Link Propertieswindow).Select the Use Connection String option, and click Build. Select theMicrosoft OLE DB Provider for SQL Server option, and click Next.Under step 1 on the Connection tab, select or enter the name of the SQLServer that you are using for this project.Under step 2, enter a user name and password that are valid values foryour SQL Server Environment. Also select the Allow Saving Of Passwordcheck box.Under step 3, select the Pubs sample database from the combo box. ClickTest to test your connection information. If your test is successful,click OK to exit the Data Link Properties window.Right-click the Connection1 icon in the object window for theDataEnvironment1 object. Select the Add Command option from the pop-upmenu to add a new command object to your project.Right-click the new Command1 icon in the DataEnvironment1 objectwindow and select the Properties option from the pop-up window (thisdisplays the Command1 Properties window).On the General tab, select the SQL Statement option button and cut andpaste the following code into the enabled text box:

SELECT pub_id, pub_name FROM Publishers Then click OK to close the Command1 Properties window.Right-click the Command1 icon in the DataEnvironment1 object window,and select the Add Child Command option from the pop-up menu (this addsanother icon to the DataEnvironment1 object window called Command2).Right-click the Command2 icon in the DataEnvironment1 object window andselect the Properties option from the pop-up menu (this displays theCommand2 Properties window).On the General tab, select the SQL Statement option button and cut andpaste the following code into the enabled text box:

SELECT pub_id, title, price FROM Titles On the Relation tab, make sure that the pub_id value is selected inboth the Parent Fields and the Child Fields/Parameters combo boxes. Thenclick Add (this creates a hierarchical relationship between the pub_idfields that are selected in the two command objects).Place an MSHFlexGrid control onto Form1, the default form of theproject.Select the MSHFlexGrid control that you added to Form1, then select theProperties window.Select the DataEnvironment1 option for the DataSource property, andselect the Command1 option for the DataMember property.Now run your project. The MSHFlexGrid control is filled with data fromboth the Publishers and Titles tables of the Pubs database, and theinformation is hierarchically grouped.NOTE: The results of steps 18 and 19 can also be accomplished at run timeby adding the following code to the code window of Form1:

Option ExplicitPrivate Sub Form_Load()DataEnvironment1.Command1Set MSHFlexGrid1.DataSource = DataEnvironment1.rsCommand1End Sub

Symptoms
The Pinnacle-BPS Graph control shipping with Visual Basic gives usersthe ability to create pie charts. The Pinnacle-BPS is a relatively largecontrol and uses a large amount of disk space on distribution disks.Therefore, the custom effects available are limited to the features of thecontrol. The Circle method in the VBA language provides functionality todraw arcs and segments. By drawing segments, you can easily create a piechart and add custom features as you require. Below is a code sampledemonstrating how to do this.
Resolution
Start a new Visual Basic project. Form1 is created by default.Place a Command button on the form.Place a 200×200 pixel Picture box on the form.Add the following code to the Form1 code window:

Option ExplicitPublic Sub DrawPiePiece(lColor As Long, fStart As Double, fEnd AsDouble)Const PI As Double = 3.14159265359Const CircleEnd As Double = -2 * PIDim dStart As DoubleDim dEnd As DoublePicture1.FillColor = lColorPicture1.FillStyle = 0dStart = fStart * (CircleEnd / 100)dEnd = fEnd * (CircleEnd / 100)Picture1.Circle (100, 100), 60, , dStart, dEndEnd SubPrivate Sub Command1_Click()Picture1.ScaleMode = vbPixelsCall DrawPiePiece(QBColor(1), 0.001, 36)Call DrawPiePiece(QBColor(2), 36, 55)Call DrawPiePiece(QBColor(3), 55, 75)Call DrawPiePiece(QBColor(4), 75, 99.999)End Sub Press the F5 key to run the project. You should see a pie chart beingdrawn with four sections mirroring the four times that the DrawPieceroutine was called in the Command1_Click event.

Symptoms
Microsoft Visual Basic does not offer a property or method to make a formthe topmost window. This behavior can be achieved using the SetWindowPosWin32 API.
This article demonstrates how to set a form as the topmost window using theSetWindowPos Win32 API.
Resolution
The sample code below uses a function called SetTopMostWindow. TheSetTopMostWindow function sets a window as a topmost Window or as a normalWindow, based on the two parameters, hwnd and Topmost, passed to it.
The hwnd parameter specifies the handle of the window to be set as topmostor as normal.
The Topmost parameter specifies whether to set the form as topmost or asnormal. If the value is true, the function sets the form to always remainon top. If the value is false, the function sets the form as a normalwindow.
Step-by-Step ExampleStart a new Standard EXE project. Form1 is created by default.Add two command buttons (Command1 and Command2) to Form1.Set the caption property of Command1 to “Always on top.”Set the caption property of Command2 to “Normal.”Put the following code in the Form1 Declaration section:

Option ExplicitPrivate Sub Command1_Click()Dim lR As LonglR = SetTopMostWindow(Form1.hwnd, True)End SubPrivate Sub Command2_Click()Dim lR As LonglR = SetTopMostWindow(Form1.hwnd, False)End Sub On the Project menu, click Add Module, to add a new module to theproject.Add the following code to the new module:

Option ExplicitPublic Const SWP_NOMOVE = 2Public Const SWP_NOSIZE = 1Public Const FLAGS = SWP_NOMOVE Or SWP_NOSIZEPublic Const HWND_TOPMOST = -1Public Const HWND_NOTOPMOST = -2Declare Function SetWindowPos Lib “user32″ Alias “SetWindowPos”_(ByVal hwnd As Long, _ByVal hWndInsertAfter As Long, _ByVal x As Long, _ByVal y As Long, _ByVal cx As Long, _ByVal cy As Long, _ByVal wFlags As Long) As LongPublic Function SetTopMostWindow(hwnd As Long, Topmost As Boolean) _As LongIf Topmost = True Then ‘Make the window topmostSetTopMostWindow = SetWindowPos(hwnd, HWND_TOPMOST, 0, 0, 0, _0, FLAGS)ElseSetTopMostWindow = SetWindowPos(hwnd, HWND_NOTOPMOST, 0, 0, _0, 0,FLAGS)SetTopMostWindow = FalseEnd IfEnd Function
NOTE: In the above sample code, an underscore (_) at the end of a line isused as a line-continuation character.
Press F5 to run the project.If you click the “Always on top” command button, your form becomes thetopmost window and remains on top of every window; you cannot move anyother window on top of it. If you click the “Normal” button, the formbehaves normally (you can move other windows on top of it).

Symptoms
This article describes how to create the basic framework of an Add-in forVisual Basic 5.0 or 6.0. An Add-in utilizes the Visual Basic Extensibilityobject model to customize and extend the Visual Basic environment.
Resolution
In Visual Basic 5.0 or 6.0, you can create the code for a minimal Add-in project with a single step. You simply double-click on the Add-in icon when you start a new Visual Basic Project.
A Visual Basic 5.0 or 6.0 Add-in can be either an ActiveX DLL or ActiveXEXE, depending upon the project type setting in the Project Propertiesdialog.
By default, an ActiveX EXE is created by the Add-in Wizard for Visual Basic5.0 and an ActiveX DLL is created by default for Visual Basic 6.0. It isgiven the name MyAddin.vbp. In Visual Basic 5.0, three files are added tothis project, connect.cls, frmAddin.frm, and Addin.bas. The contents offiles are listed below with additional comments.
Visual Basic 5.0
Connect.cls

Option Explicit’The IDTExtensibility is used to gain access to the’necessary events in the extensibility model. These four’interface methods must be contained in this class’module and must contain at least one line of code. Even’if the line of code is a comment.”+ OnConnection’+ OnDisconnection’+ OnStartUpComplete’+ OnAddInsUpdate”Implements IDTExtensibility’FormDisplayed keeps track of whether your form is displayedPublic FormDisplayed As Boolean’VBInstance is used to identify which Visual Basic IDE the’Addin belongs to. Because you can have multiple’IDEs open, this identifies the correct IDE.Public VBInstance As VBIDE.VBE’mcbMenuCommandBar is a reference to the new menu item in the Addins’Menu.Dim mcbMenuCommandBar As Office.CommandBarControl’mfrmAddIn is used to reference the addins form.Dim mfrmAddIn As New frmAddIn’MenuHandler is the command bar event handler that gives’access to the command bar events that is used to notify the’addin that a menuitem was selected.Public WithEvents MenuHandler As CommandBarEvents’Hides the Addin Form’Sub Hide()On Error Resume NextFormDisplayed = FalsemfrmAddIn.HideEnd Sub’Shows the Addin Form’Sub Show()On Error Resume NextIf mfrmAddIn Is Nothing ThenSet mfrmAddIn = New frmAddInEnd If’Sets the forms Public VBInstance variable to the instance of Visual’Basic that the addin is being run under.Set mfrmAddIn.VBInstance = VBInstance’Sets the Forms Connect Variable to the instance of this class.Set mfrmAddIn.Connect = MeFormDisplayed = TruemfrmAddIn.ShowEnd Sub’This method adds the Add-In to VB.’Private Sub IDTExtensibility_OnConnection(ByVal VBInst As Object, _ByVal ConnectMode As vbext_ConnectMode, _ByVal AddInInst As VBIDE.AddIn, custom() As Variant)On Error GoTo error_handler’save the vb instanceSet VBInstance = VBInst’This is a good place to set a breakpoint and’test various addin objects, properties and methods.Debug.Print VBInst.FullNameIf ConnectMode = vbext_cm_External Then’Used by the wizard toolbar to start this wizard.Me.ShowElse’Create the Menu Item in the AddinMenu and’return a reference to it in mcbMenuCommandBar.’(See AddToAddInCommandBar function below.)’Set mcbMenuCommandBar = AddToAddInCommandBar(“My AddIn”)’Sets this Classes MenuHandler Event to receive events from the’Menu Item that was just added to the AddInCommandBar’(mcbMenuCommandBar).Set Me.MenuHandler = _VBInst.Events.CommandBarEvents(mcbMenuCommandBar)End If’ vbext_cm_AfterStartup indicates Addin is connected after IDE’ startup.’If ConnectMode = vbext_cm_AfterStartup Then’Checks in the Registry to see if it needs’to show the Addin when it connects.If GetSetting(App.Title, “Settings”, “DisplayOnConnect”, “0″) = _”1″ Then’Set this to display the form on connect.Me.ShowEnd IfEnd IfExit Suberror_handler:MsgBox Err.DescriptionEnd Sub’This method removes the Add-In from VB.’Private Sub IDTExtensibility_OnDisconnection(ByVal RemoveMode As _vbext_DisconnectMode, custom() As Variant)On Error Resume Next’Remove the Menu Item for the Add-inmcbMenuCommandBar.Delete’ Shut down the Add-In and Save the Visible state of the’ addin form for the next time the addin is loaded.’If FormDisplayed ThenSaveSetting App.Title, “Settings”, “DisplayOnConnect”, “1″FormDisplayed = FalseElseSaveSetting App.Title, “Settings”, “DisplayOnConnect”, “0″End IfUnload mfrmAddInSet mfrmAddIn = NothingEnd SubPrivate Sub IDTExtensibility_OnStartupComplete(custom() As Variant)If GetSetting(App.Title, “Settings”, “DisplayOnConnect”, “0″) = _”1″ Then’Set this to display the form on connect.Me.ShowEnd IfEnd Sub’As it has been mentioned above, all four interfaces must be’implemented and must contain at least one line of code for your add-in’to function properly. If you don’t have any particular code that you’want to put in these procedures, just insert a comment. If the’procedure is empty, it will be removed by the compiler.Private Sub IDTExtensibility_OnAddInsUpdate(custom() As Variant)’End Sub’This event fires when the menu is clicked in the IDE.Private Sub MenuHandler_Click(ByVal CommandBarControl As Object, _handled As Boolean, CancelDefault As Boolean)Me.ShowEnd SubFunction AddToAddInCommandBar(sCaption As String) As _Office.CommandBarControlDim cbMenuCommandBar As Office.CommandBarControl’command bar objectDim cbMenu As ObjectOn Error GoTo AddToAddInCommandBarErr’See if the Add-Ins menu can be found.Set cbMenu = VBInstance.CommandBars(“Add-Ins”)If cbMenu Is Nothing Then’Add-Ins menu is not available so you fail.Exit FunctionEnd If’Create a new menu item in the Add-Ins menu.’Set cbMenuCommandBar = cbMenu.Controls.Add(1)’Set the menu caption.’cbMenuCommandBar.Caption = sCaption’ Return a Reference to the New Menu Item.’Set AddToAddInCommandBar = cbMenuCommandBarExit FunctionAddToAddInCommandBarErr:End Function
frmAddin.frm

Public VBInstance As VBIDE.VBEPublic Connect As ConnectOption ExplicitPrivate Sub CancelButton_Click()’ Hide the Form’Connect.HideEnd SubPrivate Sub OKButton_Click()’ Place your specific Add-in code here’MsgBox “AddIn operation on: ” & VBInstance.FullNameEnd Sub
Addin.bas

Option ExplicitDeclare Function WritePrivateProfileString& Lib “Kernel32″ Alias”WritePrivateProfileStringA” (ByVal AppName$, ByVal KeyName$, _ByVal keydefault$, ByVal FileName$)’====================================================================’This sub should be executed from the Immediate window.’In order to get this app added to the VBADDIN.INI file,’you must change the name in the second argument to reflect’the correct name of your project.’====================================================================Sub AddToINI()Dim ErrCode As LongErrCode = WritePrivateProfileString(“Add-Ins32″,”MyAddIn.Connect”,”0″, “vbaddin.ini”)End Sub
Visual Basic 6.0Although the Add-in project code in Visual Basic 6.0 is very similar to theVisual Basic 5.0 code, only two files are added to the MyAddin project; thefrmAddin.frm and an ActiveX Designer (connect.dsr). The ActiveX Designer isan ActiveX component that has a programmable interface and a visualinterface. The Designer allows users to customize the Add-in for run-timeuse. The designer also contains the necessary AddinInstance Interface,rather than needing to implement the IDTExtensibility Interface.
On the General tab, the designer lets you customize some basic informationfor the Add-in such as the Display Name, Description, Host application andversion, Initial Load Behavior, and whether the Add-in contains a UserInterface.
On the Advanced tab, you have the option of selecting a Satellite DLL forLocalization and a Registry key to save additional Add-in specific data.
You can add code to the add-in through code by editing the designer codeand by adding code to frmAddin.
How to Run the Basic Add-in Using Visual Basic 5.0 or Visual Basic 6.0Start a new project and select the AddIn icon.If you are using Visual Basic 5.0, press CTRL+G to open the Immediate Window. Type addtoini, and press return.(NOTE: This step is not required in Visual Basic 6.0.)Press F5 to run the Add-In. This loads and registers the Add-In.Create a new Standard EXE in a second instance of Visual Basic.From the Add-Ins menu, select Add-in Manager.In Visual Basic 5.0, select the My Addin check box.
In Visual Basic 6.0, select My Add-In from the list, and select the Loaded/Unloaded check box. Click OK.From the Add-Ins menu, select My AddIn.Click OK to run your specific Add-In code in frmAddin. (A messagebox appears by default.) Click Cancel to close the form.