Extract info from Acc2003 and insert into a Word document at a specific point

[donovans]

Hey there,

Is there a way to extract information from Microsoft Access 2003 and insert it into a specific location in a Microsoft Word 2003 document?

Please assist,
Thanks and Regards,
Donovan

[missinglinq]

This posting has been edited to facilitate reading. A portion of the post, related to Java development, has been moved to the Java Forum.

Wlcome to TheScripts!

Linq ;0)>

[donovans]

Thank you. I am really hoping that this helps, as I am out of ideas now. I am a little edgy as to the processes to follow now. But thanks for the site that has been set up to assist us unhappy and unlucky souls.

Regards,
Donovan

[nico5038]

Basically you have two options:
1) Use the Mailmerge option from MS Word
2) Use automation from VBA code to manipulate the document using the Word object model.
The Mailmerge is "the easy way out", for the other option you'll first need to ask yourself how good your VBA coding is.

Nic;o)

[donovans]

Hey there. Thanks very much for the advice. I definately want to go the Mailmerge route, however I have never heard of it, much less used it...any insight into it will really be appreciated. Regards, Donovan

[nico5038]

Just open MS Word and look for the Mailmerge menu/wizard.
One option is to get data from "outside" and an Access database is one of them.
Best to create a Query that holds the needed data, thus making sure that future table changes can be handled within the Access query and has no changes for Word.

Success !

Nic;o)

[donovans]

Brilliant, I will try that. Thanks very much for the advise and have a gr8 day further. Shot, Donovan

[donovans]

Hey there again,

Please assist, I am using MailMerge and it is working brilliantly, but I can only import information from one place in my database, but I have 11 tables, how do I create multiple mailmerges?

Thanks,
Donovan

[nico5038]

Create a UNION query for your mailmerge like:

select * from table1
UNION
select * from table2
UNION
select * from table3
UNION
etc...
 

Nic;o)

[donovans]

How do you do this? I have never done a union query before. I will try the Help file on a union query through Word, hopefully this works...thanks very much for the help, ur a life saver!!!

[nico5038]

In the query-editor switch to SQL mode and enter the SELECT statements as indicated.

Nic;o)

[donovans]

It now says that the number of selected column in the union query do not match.....what now?

[nico5038]

Only select the fields needed and the same number of fields per table.
Make sure they have the same datatype (Text, Number, Date) in the same column.
Names don't matter.

Nic;o)

[donovans]

Thanks for the reponse, I have up to 11 fields at a go on some tables and then about 3 on others....is there any way? If so please help me.....Thanks

[nico5038]

When the datatype can be kept equal, you can use "dummy" fields in the table with 3 fields like:

UNION
select field1, field2, field3, "" as FieldText4, 0 as FieldNum5, Null as FieldDat6, ... etc..
UNION
 

Nic;o)

[donovans]

Thanks,

I'll try that and let you know how it goes. I hope I can get this right now :)
Shot for all the help so far.

Kindest Regards,
Donovan

[donovans]

Joy, I pulled my first report yesterday and it is working like a charm :) Thank you so much for all the brilliant advice.

But....

Now there is another problem...When I have more than one dataset in the database, ie. more than one row, it looks like when I do a mailmerge, it carries on looping between the two or more datasets and creating all those reports over and over. If the report is about 8 pages, it ends up being about 2,500 pages long....

Any ideas about this?

Thanks,
Donovan

[nico5038]

Strange, did you check or the query does deliver the rows properly ?
I get the impression you get a "Cartesian" product, but a UNION query shouldn't suffer from that.

Is the number of copies for the Word document = 1 ?

Nic;o)

[donovans]

Hey there,

I feel like a bit of a nut now...it was an error in my program to begin with...but it is sorted now :)

I now am heading toward my final stretch...all I need to sort out now is the following: When I enter data into the table, from the program I created, the table get's sorted alphabetically by default...however, this is not good in an 11 table environment as I need the information inserted to remain the same throughout the tables ie. row one must be row one in all the tables, row two must be row two in all the tables etc. If I can get this working, I will have my program finished :) At long last.

Do you have any ideas? Please save me ... hahaha

Thanks and Regards,
Donovan

[nico5038]

Use a UNION like:

select "1" as tableno, * from table1
UNION ALL
select "2" as tableno, * from table2
UNION ALL
select "3" as tableno, * from table3
...etc
ORDER BY 1, 2
 

The ALL is needed, and the added field Tableno will sort the table's in sequence for the added number and the first column of the table. Just make sure that the first table field is the unique ID in sequence needed.

Nic;o)

[donovans]

Another question please...is there a way to automatically populate all the fields in a database that share a name with the information in one table through to the different tables?

[nico5038]

No. Normally this requirement points to the recording of redundant data and that's a "DON'T" in a normalized database.

Nic;o)

[donovans]

Hey there,

Shot for all the help, you have been a life saver and hopefully one day I can help aswell. My program is running perfectly and I have completed my project to present on Monday :)

Thanks again and Have a good one,
Kindest Regards,
Donovan