Consulting

Results 1 to 5 of 5

Thread: VBA in Word reading from an Access database

  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
    Else
    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.
    .MoveLast
    'Get count.
    m_lngNumRecs = .RecordCount
    'Return to the start.
    .MoveFirst
    End With
    arrPassed = m_oRecordSet.GetRows(m_lngNumRecs)
    lbl_Exit:
    'Cleanup
    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
    Else
    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

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,870
    Location
    Why are you writing anything to the Registry instead of to the document directly?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    Good question, Paul.

    There was a reason for it but cannot remember why I did it (wrote this 3 years ago!).

    However, it still doesn't read more than four records from the Access table when it should be reading eight of them and which the function is telling me that there are that number of records.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,870
    Location
    Have you checked what's being added to the Registry, to see whether the problem is there, versus reading the values from the Registry? Also, unless you can establish a reason for the Registry circumlocution, you might as well investigate transferring the data directly from Access to Word (which you could do regardless of whether you do anything with the Registry).
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,044
    Location
    I would guess that is has something to do with the 0 to 3 scope of your array:

    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)
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

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