Retrieving data from an Access database in Word 2010

[Roderick]

I've got a template in Word which needs to query an Access database. My VBA has a number of variables which need to be filled with information from this database. There are ten records in the DB table and my VBA has 10 variables.
With some great help from this and other forum members I've managed to piece together a procedure which picks out one record's result but I cannot figure out how to start at the top of the table and work down each record extracting the required result and putting it into its required Word VBA variable.
This is my existing code:
Sub UsingDAOWithWord()
Dim docNew As Document
Dim dbRibbonData As DAO.Database
Dim rdShippers As Recordset
Dim intRecords As Integer
Dim myString As String
Dim strSQL As String
Dim myLanguage As String
Dim myFieldCode As String

Set dbRibbonData = OpenDatabase _
(Name:="D:\DATA Samsung\myClient\RibbonX Project\" _
& "RibbonData.accdb")

myLanguage = "French" 'this will be ReturnedVal
myFieldCode = "rxmnuLanguages" 'this will be one of the record names

strSQL = "Select " & myLanguage & " FROM [Language_Labels] where [Field_Code] = '" & myFieldCode & "'"

Set rdShippers = dbRibbonData.OpenRecordset(strSQL, dbOpenDynaset)

myString = rdShippers.Fields(0)

rdShippers.Close
dbRibbonData.Close
End Sub When this procedure is run, the variable 'myString' has the correct result allocated to it.
I was thinking something in the line of arrays but my one brain cell is getting into a muddle here thinking about the next approach.
Can anyone offer some "mind-clearing" suggestions, please?
Roderick

[gmaxey]

Roderick,

The following code will extract data from an Access data base to a variant variable defined in Word. It may help. The Access table contains Employee data Name, DOB, and ID.

Option Explicit
Private m_oConn As Object 'New ADODB.Connection
Private m_oRecordSet As Object 'New ADODB.Recordset
Private m_lngNumRecs As Long, m_lngIndex As Long
Private m_strWidth As String
Dim m_strConnection As String
Sub Demo()
Dim arrData As Variant
Dim lngIndex As Long, j As Long
Dim strReturn As String
fcnFillFromAccessTable arrData, "D:\Data Stores\sourceAccess.mdb", "True", "Table1", "Employee Name", "False"
For lngIndex = 0 To UBound(arrData, 1)
strReturn = "Employee Name: "
For j = 0 To UBound(arrData, 2)
Select Case j
Case 0
strReturn = strReturn & arrData(j, lngIndex)
Case 1
strReturn = strReturn & ", DOB: " & arrData(j, lngIndex)
Case 2
strReturn = strReturn & ", ID: " & arrData(j, lngIndex)
Case Else
strReturn = strReturn & ", " & arrData(j, lngIndex)
End Select
Next j
MsgBox strReturn
Next lngIndex
End Sub

Function fcnFillFromAccessTable(arrPassed As Variant, strDBFile As String, bMDBFormat As Boolean, _
strTableName As String, strOrderBy As String, bSingleColumn 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

[Roderick]

Thank you Greg for your help.

I have made some simple changes to the code above but only so it picks out my DB labels.

The problem lies in the following function statement:

m_oConn.Open ConnectionString:=m_strConnection
This gives me the:"Unrecognised database format" error message. I'm using Windows 7 and Access 2010 and an *.accdb format. Googling the above message gives me unsuitable answers for my challenge.

The database opens correctly when I run it in Access.

I have checked the References in the VBE and have the following checked but I'm not sure which need to be there using ADO and removing the DAO ones (if any):
Visual Basic for Applications (obviously, the standard)
Microsoft Word 14.0 Object Library
OLE Automation
Microsoft Office 14 Object Library
Microsoft Office 14 Access Database engine Object
Microsoft Access 14 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data objects 2.8 LibraryBy the way, all the code compiles OK prior to running the procedure when the error appears.


Any suggestions, please?


Roderick

[gmaxey]

Roderick,

Did you change the call:
fcnFillFromAccessTable arrData, "D:\Data Stores\sourceAccess.mdb", "True", "Table1", "Employee Name", "False"

to reflect that you weren't using a .mdb format access file. Change the first "True" in the call to "False."

Does that work?

[Roderick]

Thank You Greg for your help.

Yes, I had changed the .mdbto .accdb. However, changing the "True" to "False" as directed does allow the whole procedure to work. Yippeeee!

There is a little challenge though, in that it runs through the first three record only and then terminates. There are 10 records and the "j" variable reads this correctly incrementing at every pass. So when it gets to the message box it kicks up only three of them and then terminates.

Can't figure out what is happening here. Played with the uBound settings and it kicked up an error.

Thannks again for your help.

Roderick

[gmaxey]

Roderick,

That is my fault. I didn't test very well. The:
arrPassed = m_oRecordSet.GetRows(m_lngNumRecs)

Returns a value useful for populating listboxes with the .Column property. the structure of the variant is a little odd. One record holds all the names, one holds all the DOBs, and the one holds all the IDs.

I don't know of a function (someone else reading might) how to covert the column array into a list array. But using this should work:

Sub Demo()
Dim arrData As Variant
Dim lngIndex As Long, j As Long
Dim strReturn As String
fcnFillFromAccessTable arrData, "D:\Data Stores\sourceAccess.mdb", "True", "Table1", "Employee Name", "False"
For lngIndex = 0 To UBound(arrData, 2)
strReturn = "Employee Name: "
For j = 0 To UBound(arrData, 1)
Debug.Print arrData(j, lngIndex)
Select Case j
Case 0
strReturn = strReturn & arrData(j, lngIndex)
Case 1
strReturn = strReturn & ", DOB: " & arrData(j, lngIndex)
Case 2
strReturn = strReturn & ", ID: " & arrData(j, lngIndex)
End Select
Next j
MsgBox strReturn
Next lngIndex
End Sub

[Roderick]

Awesome! Works beautifully. Thank you Greg.
Tomorrow first thing I need to figure out how I can isolate just showing - as per your example - the list of DOBs or just the list of IDs and then get the results into the .Caption of a textbox label control.
The ultimate aim is to populate a userform with either my English (your DOB) or my French (your ID) labels. But that is for the morning . . . it's late here in the UK!
Thanks once again.
Roderick

[gmaxey]

strReturn = ""
For lngIndex = 0 To UBound(arrData, 2)
'Names.
strReturn = strReturn & arrData(0, lngIndex) & vbCr
Next lngIndex
MsgBox strReturn
strReturn = ""
For lngIndex = 0 To UBound(arrData, 2)
'DOBs
strReturn = strReturn & arrData(1, lngIndex) & vbCr
Next lngIndex
MsgBox strReturn
strReturn = ""
For lngIndex = 0 To UBound(arrData, 2)
'DOBs
strReturn = strReturn & arrData(2, lngIndex) & vbCr
Next lngIndex
MsgBox strReturn

[Roderick]

Thanks, Greg.

Have now got around to working on my code again and using all the suggestions you provided have managed to get the VBA to work as intended and populate the userform with all the appropriate control labels both in English and French.

I very much appreciate your help. And learned a lot to boot!

I now have to post another question in this forum . . .