Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Thanked 0 Times in 0 Posts

    Query DB from template (2002)

    Ok, this might be beyond what Word can do, but just in case it is possible:

    I want to create a template from which a user can enter an account number which consists of 2 fields in a SQL database (e.g., "Please enter a client number: __________. Please enter a matter number: _________"). This simple Select query will retrieve client information such as name, address, etc.

    Is this possible through a macro? MailMerge does not seem to be the answer, unless I am missing something. The users cannot be expected to do any complicated tasks. Any insight or direction towards documentation that might help me out would be greatly appreciated.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Query DB from template (2002)

    The most elegant way (I think) is to create a UserForm with text boxes or (even better) combo boxes in which the user can enter (or select) the client number and matter number. You can put the code to query the SQL Server database behind an OK button, and populate the document with the correct information.

    Alternatively, you could use form fields, and lock the document for forms. Specify an exit macro for the scond form field (matter number); this macro will perform the querying.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Query DB from template (2002)

    I agree with Hans that a UserForm (a dialog separate from the document) provides the nicest interface and the most flexibility.

    You also could consider a toolbar interface if this function is something that you want users to be able to access at any time, rather than, say, when the document is first created. Having a toolbar hanging around means not having to explain how to run a macro. However, it does chew up valuable space on the screen, so it's not for everyone.

    As for the code to query the database, it often is easiest to mock this up in Access using a fresh database with ODBC linked tables to connect back to SQL Server. Probably you could do it in Excel, too; I haven't tried that. (Word itself doesn't have a built-in interface for querying databases.) After that, you will need some general ADO code samples to execute the query and handle the response from the database server. The Access forum might be a better place to seek help with those aspects of the project, as not all of the database gurus check this board.

    Here's some code to get you started:
    <pre>Dim objConn As ADODB.Connection, strConnect As String
    Dim objCommand As ADODB.Command, objRS As ADODB.Recordset
    ' Set up connection object and OLEDB string for SQL Server database
    Set objConn = New ADODB.Connection
    With objConn
    .Provider = "SQLOLEDB"
    .Properties("Data Source").Value = "MyServerMyDatabase"
    .Properties("User ID").Value = "MyUserID"
    .Properties("Password").Value = "MyPassword"
    ' Open the connection
    End With
    ' Set up command (query) object
    Set objCommand = New ADODB.Command
    With objCommand
    .ActiveConnection = objConn
    .CommandType = adCmdText
    .CommandText = _
    "SELECT Field1 AS ClientName........ " 'etc. etc.
    End With
    ' Set up recordset and run query
    Set objRS = New ADODB.Recordset
    With objRS
    .CacheSize = 20
    .Open objCommand, , adOpenForwardOnly, adLockReadOnly
    End With
    If objRS.EOF Then
    'Code to deal with no records found
    'Code to deal with records returned
    While Not objRS.EOF
    'Do useful stuff with each record
    End If
    ' Clean up for exit
    Set objRS = Nothing
    Set objCommand = Nothing
    Set objConn = Nothing</pre>

    Note #1: The above assumes you have set a reference in your VBA project to the Microsoft ActiveX Data Objects 2.x Library (most people now have 2.5 or later).

    Note #2: When you copy and paste code from the Lounge (or other HTML using "preformatted" tags), you get the best results if you paste into a Word document first (otherwise, it might end up being just one long line).

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts