Results 1 to 5 of 5

Thread: VBA in Word reading from an Access database

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Post VBA in Word reading from an Access database

    I'm using, in this case, Word 2016 in Windows 10.

    I have a template which, when run, searches an Access database and enters the appropriate text in bookmarked locations.

    The database in question contains eight records with four columns: Title, English, French and Spanish

    When I run the template there is a function which reads the database and passes the data back to the procedure which writes it to the Registry and from there on into the document.

    The odd thing is that the function registers eight rows but when it comes to reading the data to the Registry it only handles four records.

    First, I'll place the code for the function:

    Function fcnFillFromAccessTable(arrPassed As Variant, strDBFile As String, bMDBFormat As Boolean, _
    strTableName As String, strOrderBy As String,bSingleColumn As Boolean)
    'Dim adopenstatic As Boolean
    'Requires reference to the "Microsoft ActiveX Data Object 2.8 Library." 
    Set m_oConn = CreateObject("ADODB.Connection")
    If bMDBFormat Then
    m_strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDBFile
    m_strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strDBFile
    End If
    m_oConn.Open ConnectionString:=m_strConnection
    Set m_oRecordSet = CreateObject("ADODB.Recordset")
    m_oRecordSet.Open "SELECT * From " & strTableName & " ORDER BY [" & strOrderBy & "];", m_oConn, adopenstatic
    With m_oRecordSet
    'Find the last record.
    'Get count.
    m_lngNumRecs = .RecordCount
    'Return to the start.
    End With
    arrPassed = m_oRecordSet.GetRows(m_lngNumRecs)
    If m_oRecordSet.State = 1 Then m_oRecordSet.Close
    Set m_oRecordSet = Nothing
    If m_oConn.State = 1 Then m_oConn.Close
    Set m_oConn = Nothing
    Exit Function
    End Function
    Then the code for the procedure which calls the function:
    myLanguage = GetSetting("GA", "Template Language", "Language")
    VarPathLocal6 = Options.DefaultFilePath(wdUserTemplatesPath)
    VarPathNetwork6 = Options.DefaultFilePath(wdWorkgroupTemplatesPath)
    FullPath6 = VarPathLocal6 + "\" + "Templates Control Files" + "\" + "RibbonData.accdb"
    FullPathNet6 = VarPathNetwork6 + "\" + "Templates Control Files" + "\" + "RibbonData.accdb"
    If Dir(FullPath6) <> "" Then
    inifileloc6 = FullPath6
    ElseIf Dir(FullPathNet6) <> "" Then
    inifileloc6 = FullPathNet6
    End If
    'sets the database and the table within it
    fcnFillFromAccessTable arrData, inifileloc6, "False", "Page_Headings", "Title", "False"
    strReturn = ""
    For lngIndex = 0 To UBound(arrData)
    'label names.
    strReturn = arrData(0, lngIndex)
    If myLanguage = "CanadaEN" Or myLanguage = "USA" Then
    'English label translations
    strDetails = arrData(1, lngIndex)
    ElseIf myLanguage = "CanadaFR" Then
    'French label translations
    strDetails = arrData(2, lngIndex)
    ElseIf myLanguage = "SpanishSA" Then
    'Spanish label translations
    strDetails = arrData(3, lngIndex)
    End If
    'save the labels and its language values to the Registry
    SaveSetting "GA", "Page Details", strReturn,strDetails
    Next lngIndex
    I can't understand why it reads only four records when there are eight in the database. It picks out the correct column as regards the language but not the records.

    Could someone point out where it's all going wrong, please?
    Last edited by macropod; 10-02-2017 at 06:12 PM. Reason: Added code tags

Posting Permissions

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