VBA in Word reading from an Access database

[Roderick]

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?

[macropod]

Why are you writing anything to the Registry instead of to the document directly?

[Roderick]

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.

[macropod]

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

[gmaxey]

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)