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?