Access automation

[Jan Kronsell]

Starting in Access I can rather easy create VBA code that opens a Word
document and insert data from a form into bookmarks in word, but is there
any way I can go the other way around, that is, having the code in Word, to
open an Access database, get information from a table or maybe query,
without using mailmerge.

Jan

 

[Jezebel]

Also easy to do, and using more or less the same code. You'll need to add
one of the data access libraries to your project (DAO or ADO), and go from
there.

 

[Jan Kronsell]

Thanks. I now can get access to Access but now i have run into another
problem . I have to loop though the recordset and gather information.
In all of my records I have 6 fields, and I want information from the first
five, but for the last record in the recordset, I need the information from
the sixth field too.

I Word I need the information placed like this

R1F1 R1F2 R1F3 R1F4 R1F5
R2F1 R2F2 R2F3 R2F4 R2F5
....
RnF1 RnF2 RnF3 RnF4 RnF5


RnF6

I am not quite sure how to achieve this.

Jan

 

[Doug Robbins - Word MVP]

Hi Jan,

Use the following method:

Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Long
'Open a database
Set myDataBase = OpenDatabase("J:\Drive D40\Access\ResidencesXP.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners")
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not myActiveRecord.EOF
For i = 0 To 3
ActiveDocument.Range.InsertAfter myActiveRecord.Fields(i) & vbTab
Next i
ActiveDocument.Range.InsertAfter myActiveRecord.Fields(4) & vbCr
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.MovePrevious
ActiveDocument.Range.InsertAfter myActiveRecord.Fields(5) & vbCr
'Then close the database
myActiveRecord.Close
myDataBase.Close


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Jan Kronsell]

Thanks, that did the trick. But of course I have an additional question

Can I put all fields of a record into each own table cell, så that the first
5 fields of each record are placed in the table (one record pr row) and
Fild6 of record n, is places below the table.

Jan

 

[Jan Kronsell]

I figured something out myself, inspired by your code. Not elegant but
working

Jan