Using Access Data in Word Doc

[Jacob.T]

Good afternoon everyone. I have been trying to come up with a unique solution to a problem I have been trying to conquor for 3 days now. I have created a database in Access 2007. This database has a bunch of SQL logic in it that I have to email out to a number of people every month to certify that this logic is still current. I send the emails directly from Access and then the database gets automatically gets updated with the responses which is wonderful.



What I am trying to accomplish is when the logic gets updated in the Access database I want the documentation around it in a word file to get automatically updated as well. I have tried linking the two via mail manager (Insert Merge Field) and VBA however I have been upable to accomplish this. I have currently 117 records, and this list is growing every month. Doing this manually is a major waste of resources.

[Dal Jeanis]

Basically, that's a MS Word VBA question.
1) You have a Word Document that you'd like to update, using information that happens to be available in Access.
2) Each location in the Word document that must be automatically updated must have a unique way of being referenced. Either it could be a unique section of the document, or a unique style name, or it could consist of a fixed number of paragraphs starting with a unique key word, or whatever. You know your document. Just pick any one consistent method of identifying the 117 spots that will be updated.
3) You will use Office automation to find and open the file, find the section to be updated, replace it with the new data, and save the file.

[Abacus1234]

Basically, that's a MS Word VBA question.
1) You have a Word Document that you'd like to update, using information that happens to be available in Access.
2) Each location in the Word document that must be automatically updated must have a unique way of being referenced. Either it could be a unique section of the document, or a unique style name, or it could consist of a fixed number of paragraphs starting with a unique key word, or whatever. You know your document. Just pick any one consistent method of identifying the 117 spots that will be updated.

"How would you use vba to create 6 sections on one page?"

3) You will use Office automation to find and open the file, find the section to be updated, replace it with the new data, and save the file.

"How would you program to repeat for each section?"


I am using the following code to import a multi-row query. I would like to import a second and third multi-row query, and a total for one column of each section to be placed at the beginning of each section. All this should be on one page of the word document.
Private Sub Command0Copy_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Dim intRecords As Integer
Dim intColumns As Integer
strSQL = "QTopLevelPage2a"
Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.EOF = TrueThen
MsgBox"No records were retrieved. Cannot continue.", vbCritical,"Request Aborted"
rs.Close
Set db =Nothing
Exit Sub
End If
'lets get some counts
rs.MoveLast
intRecords = 0
intRecords = rs.RecordCount
Debug.Print "rs.RecordCount = " & intRecords

'Name the Range for the data added
intRecords = intRecords '+ 1 'add one row for the headernames


'lets see how many columns we have
intColumns = 0
intColumns = rs.Fields.Count

'*************************************


Dim myWordApp As Word.Application
Dim docNew As Word.Document
Dim docTable As Word.Table

Set myWordApp = CreateObject("Word.Application")
myWordApp.Visible = True
Set docNew = myWordApp.Documents.Open("C:\Test\WordDocFolder\Te stMailMerge.doc")

'Create a table that has the correct number of cells
docNew.Tables.Add Range:=docNew.Range(Start:=0, End:=0),NumRows:=intRecords, NumColumns:=intColumns

Set docTable = docNew.Tables(1)
'Get some header names in the first Row
For i = 1 Tors.Fields.Count
docTable.Cell(1, i).Range.text = rs.Fields(i - 1) '.Name
Next i
rs.MoveLast
While rs.BOF = False
'Populate the last row
For i = 1 Tors.Fields.Count
If NotIsNull(rs.Fields(i - 1).Value) Then
docTable.Cell(intRecords, i).Range.text = rs.Fields(i - 1) '.Value
End If
Next i
Debug.Print intRecords
intRecords = intRecords - 1
rs.MovePrevious
Wend
docNew.Range.InsertAfter _
" Hello "

'docNew.Activate
'docNew.PrintPreview
docNew.Save
docNew.Close
myWordApp.Quit
Set docNew = Nothing
Set myWordApp = Nothing
End Sub