Visual Basic Q&A

Symptoms
The Active Directory Service Interfaces (ADSI) LightweightDirectory Access Protocol (LDAP) provider implements OLE DB interfaces thatallow you to use ActiveX Data Objects (ADO) to access objects in LDAPcompliant directories. You must create an ADO connection object and set itsProvider property to “ADsDSOObject”.You can specify any string, including”", as the connection string (first argument) of the ADO connectionobject’s open method.
The connection object Execute method’s CommandText (first object) is anLDAP query composed of four elements separated by semicolons, in thefollowing format:

<LDAP://server/adsidn>;ldapfilter;attributescsv;scope where:
server is the name (or IP address) of the server hosting the directory.adsidn is the distinguished name (DN) of the starting point for yourquery expressed ADsPath format with “/” separators and the root of thenamespace to the left. You can also use an X.500 style attributed nameformat with the relative distinguished names separated by commas and theroot of the name space to the right.1dap filter is the LDAP filter string (see rfc2254).attributescsv is a comma separated list of names of the attributes to be returned for each row in the recordset.scope is either: base, onelevel, or subtree.NOTE: rfc2253 specifies the LDAP syntaxes on which the ADSI LDAP syntax is based.
To return the ADsPath, class, and cn attributes of all the objects in allthe recipient containers in an Exchange server, you can use the followingCommandText (in URL format):

LDAP:<//server/o=organization/ou=site/cn=recipients>;(objectClass=*);ADsPath,objectClass,cn;subtree” or (in attributed name format):

<LDAP://server/cn=recipients,ou=site,o=organization>, _(objectClass=*);ADsPath,objectClass;subtree
Resolution
The following Visual Basic sample code illustrates this query:
Sample Code

Dim conn As ADODB.ConnectionDim rs As ADODB.RecordsetSet conn = New ADODB.Connectionconn.Provider = “ADSDSOObject”conn.Open “ADs Provider”Set rs = conn.Execute( _”<LDAP://server/o=organization/ou=site/cn=recipients>;” _& “(objectClass=*);ADsPath,objectClass,cn;subtree”)While Not rs.EOFDebug.Print rs.Fields(0).Value, rs.Fields(1).Value, _rs.Fields(2).Valuers.MoveNextWendconn.Close

Symptoms
This article discusses how to automate Word to create a mail merge for an external data source. This article also explains the code differences between accessing the data with OLEDB, ODBC, and dynamic data exchange (DDE).
Resolution
Data access methods To programmatically set up a data source for a Word mail merge document, you first call the OpenDataSource method of a MailMerge object. The syntax for the OpenDataSource method is as follows:
<MailMergeObject>.OpenDataSource(Name, [Format], [ConfirmConversions], [ReadOnly], [LinkToSource], [AddToRecentFiles], [PasswordDocument], [PasswordTemplate], [Revert],[WritePasswordDocument], [WritePasswordTemplate], [Connection], [SQLStatement], [SQLStatement1], [OpenExclusive], [SubType]) Note For a complete description of each argument, refer to the Microsoft Word Visual Basic online Help. Of primary interest for connecting to an external data source are the Name, Connection, and SubType arguments. Different combinations of these three arguments represent different data access methods for the mail merge.
Using OLEDB OLEDB is the recommended data access method. To specify OLEDB as the data access method with OpenDataSource, supply the Name argument with the path and the file name to either the database or an Office DataSource Connection (.odc). If you provide a database for the Name argument, Word will automatically use OLEDB if there is an OLEDB provider installed that supports the database format.
Example

<MailMergeObject>.OpenDataSource Name:=”C:\MyDB.mdb”, _SQLStatement:=”SELECT * FROM [MyTable]”
- or -

<MailMergeObject>.OpenDataSource Name:=”C:\MyDataSource.odc”, _SQLStatement:=”SELECT * FROM [MyTable]” Word and other Office XP applications use the Office DataSource Object (ODSO) for OLEDB access to external data sources. ODSO is the only mechanism by which Word can access data by using OLEDB for a mail merge. ODSO requires that the Name argument for OpenDataSource be either a complete path to a database or a complete path to a valid ODC file. ODSO ignores any information in the Connection argument.
Using ODBC You can use ODBC for your mail merge to access data for which a user data source name (DSN) has been set up on the system. To specify ODBC as the data access method with OpenDataSource, supply an empty string for the Name argument, an ODBC connection string for the Connection argument, and wdMergeSubTypeWord2000 for the SubType argument.
Example

<MailMergeObject>.OpenDataSource Name:= “”, _Connection:= “DSN=MySQLServerDSN;DATABASE=pubs;uid=sa;pwd=;”, _SQLStatement:= “Select au_id, au_lname, au_fname from authors”, _SubType:= wdMergeSubTypeWord2000
Using DDE You can use DDE to access data in Microsoft Access databases or Microsoft Excel workbooks. To specify DDE as the data access method with OpenDataSource, supply the path and the file name to the database or the workbook for the Name argument, and wdMergeSubTypeWord2000 for the SubType argument.
Example

<MailMergeObject>.OpenDataSource Name:=”C:\MyDB.mdb”, _SQLStatement:=”SELECT * FROM [MyTable]“, _SubType:=wdMergeSubTypeWord2000
Automation sample The following sample code creates and executes a mail merge for form letters by using OLEDB (by way of ODSO). The data source that is used is the sample Access database Northwind.mdb. If Northwind is not installed, start Microsoft Access 2002 or Microsoft Office Access 2003. On the Help menu, click Sample Databases, and then choose Northwind Sample Database to install this feature.
To run this sample, follow these steps: Start a new Standard EXE project in Visual Basic. By default, Form1 is created.On the Project menu, click References.Click Microsoft Word 2000 Object Library in the list of references, and then click OK.
Note: To use the Microsoft Office Word 2003 Object, add the Microsoft Word 11.0 Object Library in the list of references and then Click OK.Add a CommandButton control to Form1.Add the following code to the code module for Form1.
Note If it is necessary, modify the path to Northwind.mdb to match your installation for Office XP.

Dim WithEvents oApp As Word.ApplicationPrivate Sub Form_Load()’Start Word.Set oApp = CreateObject(“Word.Application“)End SubPrivate Sub Command1_Click()Dim oMainDoc As Word.DocumentDim oSel As Word.SelectionDim sDBPath as String’Start a new main document for the mail merge.Set oMainDoc = oApp.Documents.AddWith oMainDoc.MailMerge.MainDocumentType = wdFormLetters’Set up the mail merge data source to Northwind.mdb.sDBPath = “C:\Program Files\Microsoft Office\” & _”OfficeXP\Samples\Northwind.mdb”.OpenDataSource Name:=sDBPath, _SQLStatement:=”SELECT * FROM [Customers]“‘Add the field codes to the document to create the form letter.With .FieldsSet oSel = oApp.Selection.Add oSel.Range, “CompanyName”oSel.TypeParagraph.Add oSel.Range, “Address”oSel.TypeParagraph.Add oSel.Range, “City”oSel.TypeText “, “.Add oSel.Range, “Country”oSel.TypeParagraphoSel.TypeParagraphoSel.TypeText “Dear “.Add oSel.Range, “ContactName”oSel.TypeText “,”oSel.TypeParagraphoSel.TypeParagraphoSel.TypeText ” This letter is to inform you…”oSel.TypeParagraphoSel.TypeParagraphoSel.TypeText “Sincerely, [Your Name Here]“End WithEnd With’Perform the mail merge to a new document.With oMainDoc.MailMerge.Destination = wdSendToNewDocument.MailMerge.Execute Pause:=FalseEnd WithEnd SubPrivate Sub oApp_MailMergeAfterMerge(ByVal Doc As Word.Document, ByVal DocResult As Word.Document)’When the mail merge is complete, 1) make Word visible,’2) close the mail merge document leaving only the resulting document’open and 3) display a message.Doc.Close FalseoApp.Visible = TrueMsgBox “Mail Merge Complete: ” & oApp.ActiveDocument.NameEnd SubPrivate Sub Form_Unload(Cancel As Integer)Set oApp = NothingEnd Sub Press F5 to run the program.Click the CommandButton control on Form1 to perform the mail merge. When the code completes, Word is made visible with a new document open. The new document contains form letters that result from a mail merge containing data that is extracted from the Customers table in Northwind.mdb.

Symptoms
Collaboration Data Objects (CDO) 1.1 supports programmatic access to MAPI Address Booksthrough the AddressEntries collection of an AddressList object. EachAddressList object corresponds to a MAPI Address Book and is a member ofthe AddressLists collection of the Session object.
The available AddressBooks can be enumerated by walking the AddressLists collection, and anindividual address book can be selected by indexing the AddressListscollection by name or by position.
The following sample code walks the AddressListscollection, and displays information about each address book that can be accessed. Thesecond part of the sample selects the global address list (GAL) and then walksthe Address Entries, recursively expanding Distribution Lists.
Resolution
The following sample code applies to any 32-bit Visual Basic-based product.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
Step-by-step exampleCreate a new Visual Basic project. Add a reference to the Collaboration Data Objects1.1 Object Library.Type the following code into a module.

Option ExplicitSub main()Dim objSession As MAPI.Session’ Use early binding for’ efficiency.Dim collAddressLists As AddressListsDim objAddressList As AddressListDim collAddressEntries As AddressEntriesOn Error GoTo error_olemsg’ Create a session and log on.Set objSession = CreateObject(“MAPI.Session”)’ Use a valid Exchange profile name.objSession.Logon (“<Your Profile Name Here>”)’Walk the available address books.Set collAddressLists = objSession.AddressListsFor Each objAddressList In collAddressLists’ Display collection selection indices.Debug.Print objAddressList.Name,Debug.Print objAddressList.Index,’ Display the readonly access flag.Debug.Print objAddressList.IsReadOnly,’ Display the count of recipients.Set collAddressEntries = objAddressList.AddressEntriesDebug.Print Str(collAddressEntries.Count)’ If there are any AddressEntries, display the first recipient name.If Not objAddressList.AddressEntries(1) Is Nothing Then _Debug.Print objAddressList.AddressEntries(1).NameNext objAddressList’ Walk the global address list.Set collAddressEntries = objSession.AddressLists( _”Global Address List”).AddressEntriesWalkAddressList collAddressEntries’ Close the session and log off.objSession.LogoffExit Suberror_olemsg:MsgBox “Error ” & Str(Err) & “: ” & Error$(Err)Exit SubEnd Sub’ Walk an AddressEntries collection and recursively expand’ Distribution Lists.Sub WalkAddressList(collAddressEntries As AddressEntries)Dim objaddressEntry As AddressEntryFor Each objaddressEntry In collAddressEntries’ Display the recipient’s name.Debug.Print objaddressEntry.Name,’ Display the recipient’s type.Select Case objaddressEntry.DisplayTypeCase ActMsgUserDebug.Print “Exchange Recipient”Case ActMsgDistListDebug.Print “Distribution List”WalkAddressList objaddressEntry.MembersDebug.Print “End of DL ” & objaddressEntry.NameCase ActMsgForumDebug.Print “Public Folder”Case ActMsgAgentDebug.Print “Agent”Case ActMsgPrivateDistListDebug.Print “Private DL”Case ActMsgOrganizationDebug.Print “Organization”Case ActMsgRemoteUserDebug.Print “Custom Recipient”Case ElseDebug.Print “Unknown type”End SelectNext objaddressEntryEnd SubVisual Basic only: Set Sub Main to run in the Project Properties, andthen run the application.
Other VBA applications: In the Debug/Immediate window, type MAIN,and then press ENTER.The output will appear in the Debug/Immediate window.
NotesThe address book can have restricted entries. Restricted entries will generate an”Access Denied” message:

Error -2147024891: [Collaboration Data Objects - [E_ACCESSDENIED(80070005)]]The user can include additional error handling where required.

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
When you make a late-bound call in Microsoft Visual Basic .NET, the late-binding support fails when the ByRef decimal is converted to ByRef currency. You receive the following error message:

Unhandled Exception: System.Runtime.InteropServices.COMException (0×80020005): Type mismatch. at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn) at Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack) at ConsoleApplication2.Module1.main() in C:\ConsoleApplication\Module.vb:line number
Press any key to continueNote In this error message, C:\ConsoleApplication\Module.vb:line number is a placeholder for the actual path where the application is saved. In the application module, line number represents the line where the error occurs.
Resolution
To work around this bug, use either of the following options: Use the CurrencyWrapper type.Force the decimal value to be passed by using the ByVal keyword.
To do this, put parentheses around the variable that contains the decimal value.To implement either of these workaround options, create a DLL by using Visual Basic 6.0, create a console application, and reference the DLL in the console application, as follows:Create a DLL by using Visual Basic 6.0Start Visual Basic 6.0.On the File menu, click New Project.
The New Project dialog box appears.Click ActiveX DLL, and then click OK.
By default, the Class1 class is created.Add the following code to the Class1 class:

Public Function cedrbank(name As String, ACCNUM As String, ACCBAL As Currency) As LongMsgBox ACCBALACCBAL = 4.321End FunctionOn the File menu, click Save Class1.cls, and then click Save Project As.In the File name box, type the name of the project.On the File menu, click Make Project.dll.
The Make Project dialog box appears.
Note Project is a placeholder for the actual name of the project.Click OK.Create a console applicationStart Visual Studio .NET.On the File menu, point to New, and then click Project.
The New Project dialog box appears.Under Project Types, click Visual Basic Projects.Under Templates, click Console Application.Click OK.
By default, the Module1.vb module is created.Replace the existing code in the Module1.vb module with the following code:

Imports SystemImports System.Runtime.InteropServicesImports System.ReflectionImports Project1Public Module Module1Sub Main()Dim x As Objectx = New Class1Dim dc As Object = New CurrencyWrapper(5)Dim d As Decimal = 1.987Dim args(2) As Objectargs(0) = “name”args(1) = “num”args(2) = dcDim c(0) As Reflection.ParameterModifierc(0) = New Reflection.ParameterModifier(3)c(0).Item(0) = Truec(0).Item(1) = Truec(0).Item(2) = TrueCObj(x).GetType().InvokeMember(“cedrbank”, BindingFlags.InvokeMethod, Nothing, x, args, c, Nothing, Nothing)Console.WriteLine(args(2))dc = New CurrencyWrapper(6)’Use the CurrencyWrapper type.x.cedrbank(“name”, “num”, dc)Console.WriteLine(dc)’Force ‘d’ to be passed ByVal by using parentheses around ‘d.’x.cedrbank(“name”, “num”, (d))Console.WriteLine(d)End SubEnd ModuleAdd a reference to the DLL that you created in the “Create a DLL by using Visual Basic 6.0″ section of this article.On the Build menu, click Build Solution.On the Debug menu, click Start.
You do not receive the error message that is mentioned in the “Symptoms” section of this article.

Symptoms
When you create a DataSet during design time by using the Generate DataSet option of a DataAdapter, an instance of the DataSet is automatically created on the form. When you modify the name of the DataSet in the Properties window, save the project, and then try to open the form, you may receive the following error message on the form:

An error occurred while loading the document. Fix the error, and then try loading the document again. The error message follows:
Unspecified errorWhen you build your project, you may receive the following error message:

Type ‘ApplicationName.OldDataSetName’ is not defined.
Resolution
The problem occurs because when the DataSet name is changed in the DataSet schema file (.xsd), Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET does not modify the DataSet name in the InitializeComponent of the form where the DataSet is initialized.