Visual Basic Q&A

Symptoms
The Data Access Object (DAO) documentation that comes with Microsoft Access and Visual Basic Online help shows how to open a Paradox table. However, it does not show how to open a table that is password protected.
Resolution
To open a Paradox table, the documentation states that you must specify Paradox 3.x, Paradox 4.x, or Paradox 5.x in the connect string. However, the documentation omits the PWD argument in the connect string. PWD is used to specify the Paradox password, just as it is used when opening password-protected ODBC or Jet tables.
The following code samples show the correct use of the PWD option when opening password-protected Paradox tables. In each of the samples, the table is C:\MYDATA\Table1.DB and the password is “ABC”.
NOTE: The period in the Paradox file name needs to be replaced by the # symbol. This is because Microsoft Jet uses the period to separate database and table names and to separate table and field names. For example, Table1.DB needs to be written as Table1#DB when referring to the table in Jet.

Attaching/Linking a password-protected table:

Dim db As DAO.DatabaseDim td As DAO.TableDefDim rs As DAO.RecordsetSet db = OpenDatabase(App.Path & “\NWIND.MDB”)Set td = db.CreateTableDef(“Table1″)td.Connect = “Paradox 4.x;PWD=ABC;DATABASE=C:\MYDATA;”td.SourceTableName = “Table1#DB”db.TableDefs.Append tdSet rs = db.OpenRecordset(“Table1″)

Opening directly using a Database object:In this case, the database is the directory where the tables reside. If all tables have the same password, you can open them from the same Database object. If the tables have different passwords, you will need to open a different Database object for each unique password.

Set db = DBEngine(0).OpenDatabase(“C:\MYDATA”, 0, 0, “Paradox 4.x;PWD=ABC;”)Set rs = db.OpenRecordset(“Table1#DB”)

Referencing a Paradox table directly in SQL statementsMicrosoft Jet allows use of an IN clause or the [Database].[Table] syntax to reference a table outside the current database. The [Database].[Table] syntax is preferred.

Set db = OpenDatabase(App.Path & “\NWIND.MDB”)Set rs = db.OpenRecordset(“SELECT * FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB]“)” Or’Set rs = db.OpenRecordset(“SELECT * FROM [Table1#DB] IN ” [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC]“)” Or’Set rs = db.OpenRecordset(“SELECT * FROM [Table1#DB] IN ‘C:\MYDATA’ [Paradox 3.x;PWD=ABC]“)
You can also use action queries with the same direct SQL technique:

‘ Make a new table Access “LocalTable” from data in Paradox table.Set db = OpenDatabase(App.Path & “\NWIND.MDB”)db.Execute “SELECT * INTO LocalTable FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB]“‘ Append data into an Access table “LocalTable” from Paradox table.db.Execute “INSERT INTO LocalTable SELECT * FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB]“‘ Append data into a Paradox table from an Access table “LocalTable”. db.Execute “INSERT INTO [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB] SELECT * FROM LocalTable”‘ Delete records from a Paradox table.db.Execute “DELETE FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB] WHERE Field1 = 55″
(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Malcolm Stewart, Microsoft Corporation.

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.