Export (0) Print
Expand All

OpenDataSource Method

Office 2003

Attaches a data source to the specified document, which becomes a main document if it's not one already.

expression.OpenDataSource(Name, Format, ConfirmConversions, ReadOnly, LinkToSource, AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert, WritePasswordDocument, WritePasswordTemplate, Connection, SQLStatement, SQLStatement1, OpenExclusive, SubType)

expression Required. An expression that returns a MailMerge object.

Name     Required String. The data source file name. You can specify a Microsoft Query (.qry) file instead of specifying a data source, a connection string, and a query string.

WdOpenFormat can be one of these WdOpenFormat constants.
wdOpenFormatAllWord
wdOpenFormatAuto Default.
wdOpenFormatDocument
wdOpenFormatEncodedText
wdOpenFormatRTF
wdOpenFormatTemplate
wdOpenFormatText
wdOpenFormatUnicodeText
wdOpenFormatWebPages
wdOpenFormatXML

ConfirmConversions     Optional Variant. True to display the Convert File dialog box if the file isn't in Microsoft Word format.

ReadOnly     Optional Variant. True to open the data source on a read-only basis.

LinkToSource     Optional Variant. True to perform the query specified by Connection and SQLStatement each time the main document is opened.

AddToRecentFiles     Optional Variant. True to add the file name to the list of recently used files at the bottom of the File menu.

PasswordDocument     Optional Variant. The password used to open the data source. (See Remarks below.)

PasswordTemplate     Optional Variant. The password used to open the template. (See Remarks below.)

Revert     Optional Variant. Controls what happens if Name is the file name of an open document. True to discard any unsaved changes to the open document and reopen the file; False to activate the open document.

WritePasswordDocument     Optional Variant. The password used to save changes to the document. (See Remarks below.)

WritePasswordTemplate     Optional Variant. The password used to save changes to the template. (See Remarks below.)

Connection     Optional Variant. A range within which the query specified by SQLStatement is to be performed. (See Remarks below.) How you specify the range depends on how data is retrieved. For example:

  • When retrieving data through Open Database Connectivity (ODBC), you specify a connection string.
  • When retrieving data from Microsoft Excel using dynamic data exchange (DDE), you specify a named range.

    Security  Dynamic data exchange (DDE) is an older technology that is not secure. If possible, use a more secure alternative to DDE, such as object linking and embedding (OLE).

  • When retrieving data from Microsoft Access, you specify the word "Table" or "Query" followed by the name of a table or query.

SQLStatement     Optional Variant. Defines query options for retrieving data. (See Remarks below.)

SQLStatement1     Optional Variant. If the query string is longer than 255 characters, SQLStatement specifies the first portion of the string, and SQLStatement1 specifies the second portion. (See Remarks below.)

OpenExclusive    Optional Variant. True to open exclusively.

wdMergeSubTypeAccess 1
wdMergeSubTypeOAL 2
wdMergeSubTypeOLEDBText 5
wdMergeSubTypeOLEDBWord 3
wdMergeSubTypeOther 0
wdMergeSubTypeOutlook 6
wdMergeSubTypeWord 7
wdMergeSubTypeWord2000 8
wdMergeSubTypeWorks 4

Remarks

To determine the ODBC connection and query strings, set query options manually, and use the QueryString property to return the connection string. The following table includes some commonly used SQL keywords.

Keyword Description
DSN The name of the ODBC data source
UID The user logon ID
PWD The user-specified password
DBQ The database file name
FIL The file type

Security  Avoid using the built-in system administrator (sa) logon account. Instead, make system administrators members of the sysadmin fixed server role, and have them use their own accounts to log on. Use sa only when there is no other way to log on. To prevent unauthorized access through the sa logon account, you should assign that account a strong, unique password.

Security  When possible, use Windows Authentication (also referred to as a trusted connection), which uses a Windows user account to connect to SQL Server. When a user connects through a Windows user account, SQL Server uses information in the Windows operating system to validate the account name and password. Before you can use Windows Authentication, a server administrator must configure SQL Server to use this mode of authentication. If Windows Authentication is not available, avoid saving users' logon information. It is more secure for users to enter their logon information each time they log on.

Security   Avoid using hard-coded passwords in your applications. If a password is required in a procedure, request the password from the user, store it in a variable, and then use the variable in your code. For recommended best practices on how to do this, see .

Example

This example creates a new main document and attaches the Orders table from a Microsoft Access database named "Northwind.mdb."

    Dim docNew As Document

Set docNew = Documents.Add

With docNew.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource _
        Name:="C:\Program Files\Microsoft Office" & _
        "\Office\Samples\Northwind.mdb", _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="TABLE Orders"
End With
  

This example creates a new main document and attaches the Microsoft Excel spreadsheet named "Names.xls." The Connection argument retrieves data from the range named "Sales."

    Dim docNew As Document

Set docNew = Documents.Add

With docNew.MailMerge
    .MainDocumentType = wdCatalog
    .OpenDataSource Name:="C:\Documents\Names.xls", _
        ReadOnly:=True, _
        Connection:="Sales"
End With
  

This example uses ODBC to attach the Microsoft Access database named "Northwind.mdb" to the active document. The SQLStatement argument selects the records in the Customers table.

    Dim strConnection As String

With ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    strConnection = "DSN=MS Access Databases;" _
        & "DBQ=C:\Northwind.mdb;" _
        & "FIL=RedISAM;"
   .OpenDataSource Name:="C:\NorthWind.mdb", _
        Connection:=strConnection, _
        SQLStatement:="SELECT * FROM Customers"
End With
  


Applies to | MailMerge Object



Show:
© 2014 Microsoft