Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2007
    Posts
    54
    Thanks
    7
    Thanked 1 Time in 1 Post

    Word 2010 and SQL Server: Attach database to a Userform for a letter macro

    I have not worked with databases at all, so I`m hoping that someone can help me. I am trying to attach a SQL database to the letterhead so the user would type in a matter number and/or a client number, and the macro would then be able to grab the name, address, re, etc. from the database and insert the information into the userform. The user then would have a chance to review and change any of the information before the letter is created. I have searched the internet for a while now and have tried many things, but can't seem to get a connection to work. I have created a .dsn file, but get an Object Variable not set error when trying to use it.

    Code:
    Dim vConnection As ADODB.Connection, strConnect As String
    Dim vRecordSet As ADODB.Recordset, vCommand As ADODB.Command
    vConnection.ConnectionString = "C:\MRCustom\MacroFiles\MRLetterQuery.dsn" & "Provider=SQLOLEDB;"
    vConnection.Open
    I tried this:

    Code:
    vConnection.ConnectionString = "DRIVER=SQL Server;SERVER=DB2;DATABASE=son_db"
    vConnection.Open
    If vConnection.State = adStateOpen Then
        MsgBox "Finally!!" Else
        MsgBox "No Go!!"
    End If
    It said it was open, but everytime I tried to extract any information, it said that the connection had to be open?

    I've also tried this, but I get an invalid use of property error on .ConnectionString
    Code:
    Set vConnection = New ADODB.Connection
    vConnection.ConnectionString "Driver={SQL Server};" & _
               "Server=MR;" & _
               "Database=son;" & _
               "Trusted_Connection=true;"
    vConnection.Open
    Can anyone help me with this? This isn't code that I usually use. What I would ultimately like is to be able to connect using the .dsn file, if possible, and then use an sql query to extract the appropriate fields, put the data in a proper letter format and insert that into the userform. I'm starting to wonder if this is even possible? I am on Windows 7 and Word 2010. It is a SQL Server driver using a trusted connection.

    What am I doing wrong? I am having a tough time actually finding VBA coding on the internet -- it's all VB coding, which is slightly different and I'm wondering if that is the issue?
    Last edited by maverik; 2012-08-27 at 22:07.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    What you are attempting to do is certainly possible, but trying to do it directly in Word presents some significant challenges. Word forms are quite challenging if you want to use data from a database, and let the user select a specific record in some fashion. I would create a simple Microsoft Access front-end that links to the SQL Server database, and use a form to let the user select the recipient, and then use Automation to create the letter in Word. Helen Feddema has numerous code samples on her website - take a look at her Sample Code 2 at http://www.helenfeddema.com/CodeSamples.htm under the Access To Word category. If you aren't familiar with Automation you may find this tutorial http://www.access-experts.com/defaul...tomation&sm=18 helpful.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    On my PC (Office 2007 and SQL Server Express 2008) the code below works in Access, Excel and Word. It does not use a DSN but you may be able to customize it:
    Code:
    Sub AnotherConnectionTest()
        Dim cnn As ADODB.Connection
        Dim rs As ADODB.Recordset
        
        Dim strConn As String
        
        On Error GoTo ACTError
        strConn = "Provider=SQLNCLI;Server=PCNAME\SQLEXPRESS;DATABASE=pubs;Trusted_Connection=yes;"
        Set cnn = New ADODB.Connection
        
        cnn.Open strConn
        
        Set rs = New ADODB.Recordset
        rs.Open "SELECT * FROM dbo.authors", cnn, adOpenDynamic
        If Not rs.EOF Then
            rs.MoveFirst
            MsgBox "id: " & rs![au_id].Value
        End If
        rs.Close
    ACTExit:
        On Error Resume Next
        cnn.Close
        Set rs = Nothing
        Set cnn = Nothing
        Exit Sub
    ACTError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error in ConnectionTest"
        Resume ACTExit
    End Sub
    Last edited by jeremybarker; 2012-08-28 at 14:44. Reason: Incorrect office version!

Posting Permissions

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