Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Extracting data from an Access 2010 database using Word VBA

    I've got a Word 2010 template which has five variables in one of the procedures, e.g. oSignatory, oRecipient, oInstruction and so on.
    The Access database is called Languages.accdb, the table to read is called Template_Details. This table has three fields: label_name, English, French.
    So, using pseudo-code, I would like to say in my SQL statement something like "Search in my Template_Details table, find the record called lblSignatory and tell me what appears in the French column". It is this result I want to allocate to one of my variables.
    I then want it to repeat the same process for another record. And so on until I've got my five variables populated.
    I've been searching the internet for days now but only seem to have found 20 different ways to populate a combo box and another ten for a list box! But nothing for individual results into a variable!
    I'm doing some lookups in an Excel spreadsheet using DAO so would like to continue using it to access the Access database.
    Can anyone offer any suggestions on how I can achieve this, please?
    Thanks for your help.
    Roderick

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by RoryOk13 View Post
    I've got a Word 2010 template which has five variables in one of the procedures, e.g. oSignatory, oRecipient, oInstruction and so on.
    The Access database is called Languages.accdb, the table to read is called Template_Details. This table has three fields: label_name, English, French.
    So, using pseudo-code, I would like to say in my SQL statement something like "Search in my Template_Details table, find the record called lblSignatory and tell me what appears in the French column". It is this result I want to allocate to one of my variables.
    I then want it to repeat the same process for another record. And so on until I've got my five variables populated.
    I've been searching the internet for days now but only seem to have found 20 different ways to populate a combo box and another ten for a list box! But nothing for individual results into a variable!
    I'm doing some lookups in an Excel spreadsheet using DAO so would like to continue using it to access the Access database.
    Can anyone offer any suggestions on how I can achieve this, please?
    Thanks for your help.
    Roderick
    This is air code (i.e. untested) but you want something like this;

    if rst is a DAO recordset, dbs is a DAO database variable pointing to your db, and strSQL is a string

    ' Assuming label_name holds string values, build an SQL string and put single quotes around the value being searched:
    strSQL = "SELECT * FROM [Template_Details] WHERE label_name = '" & lblSignatory & "'"
    set rst = dbs.openrecordset(strSQL)
    ' N.B. need to test here that you got a record back!
    myVariable = rst![French].value
    ' close recordset / database variables etc

  4. #3
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    667
    Thanks
    3
    Thanked 19 Times in 18 Posts
    I've had success pulling data from a DB using Word vba. Perhaps this will help.

    I assume from your description that Template_Details is a table with five rows. Each row has an identifying name, an English entry, and a French entry. I think I would have created my Table with only two rows: one named "English" and one named "French". And each row would have five columns: one for signatory, one for recipient, and so on. This way, all we have to do is find one row in the table -- English or French -- and pull all information from that single row. With the existing layout, the only code I could devise visits each row, one after the other, pulling one variable from each. Perhaps someone has a better way?

    At any rate, here's some code to try.
    Code:
    Sub GetData()
    Dim oDataBase As Database
    Dim oWorkSpace As Workspace
    Dim oRecordSet As Recordset
    Dim strDB As String
    Dim strDBTable As String
    Dim strDBField As String
    Dim strDBName
    Dim strSignatory
    Dim strRecipient
    Dim strInstruction
    Dim strLanguage
    
    ' Full Path to Database
    strDB = "[C:\MyFullPath\]Languages.accdb"
    ' Database Table or Query to Use
    strDBTable = "Template_Details"
    ' Database Field to Use
    
    strLanguage = "English" 'or "French" -- not sure how you capture this
    
    ' Connect to Database
    Set oWorkSpace = CreateWorkspace(name:="JetWorkspace", _
        UserName:="admin", Password:="", UseType:=dbUseJet)
    Set oDataBase = OpenDatabase(strDB)
    Set oRecordSet = oDataBase.OpenRecordset(strDBTable)
    
    Do Until oRecordSet.EOF
        If oRecordSet("label_name") = "lblSignatory" Then
            strSignatory = oRecordSet.Fields(strLanguage)
        ElseIf oRecordSet("label_name") = "lblRecipient" Then
            strRecipient = oRecordSet.Fields(strLanguage)
        ElseIf oRecordSet("label_name") = "lblInstruction" Then
            strInstruction = oRecordSet.Fields(strLanguage)
        '...keep going until you've captured all fields
    
        End If
        oRecordSet.MoveNext
    Loop
    oRecordSet.Close
    oDataBase.Close
    strDBName = ""
    
    End Sub
    Hope this helps, Kim

  5. The Following User Says Thank You to kmurdock For This Useful Post:

    RoryOk13 (2013-05-25)

  6. #4
    New Lounger
    Join Date
    May 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Kim for your your code sample. I have used pieces of it to meet my structure and they all work great! Brilliant!

  7. #5
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    667
    Thanks
    3
    Thanked 19 Times in 18 Posts
    Great! Glad to help.

Tags for this Thread

Posting Permissions

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