By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

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

P: 21
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
Oct 16 '07 #1
Share this Question
Share on Google+
22 Replies


missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 16 '07 #2

P: 21
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
Oct 17 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 17 '07 #4

P: 21
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
Oct 17 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 17 '07 #6

P: 21
Brilliant, I will try that. Thanks very much for the advise and have a gr8 day further. Shot, Donovan
Oct 18 '07 #7

P: 21
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
Nov 5 '07 #8

nico5038
Expert 2.5K+
P: 3,072
Create a UNION query for your mailmerge like:
Expand|Select|Wrap|Line Numbers
  1. select * from table1
  2. UNION
  3. select * from table2
  4. UNION
  5. select * from table3
  6. UNION
  7. etc...
  8.  
Nic;o)
Nov 5 '07 #9

P: 21
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!!!
Nov 6 '07 #10

nico5038
Expert 2.5K+
P: 3,072
In the query-editor switch to SQL mode and enter the SELECT statements as indicated.

Nic;o)
Nov 6 '07 #11

P: 21
It now says that the number of selected column in the union query do not match.....what now?
Nov 6 '07 #12

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 6 '07 #13

P: 21
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
Nov 7 '07 #14

nico5038
Expert 2.5K+
P: 3,072
When the datatype can be kept equal, you can use "dummy" fields in the table with 3 fields like:
Expand|Select|Wrap|Line Numbers
  1. UNION
  2. select field1, field2, field3, "" as FieldText4, 0 as FieldNum5, Null as FieldDat6, ... etc..
  3. UNION
  4.  
Nic;o)
Nov 7 '07 #15

P: 21
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
Nov 8 '07 #16

P: 21
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
Nov 9 '07 #17

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 9 '07 #18

P: 21
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
Nov 12 '07 #19

nico5038
Expert 2.5K+
P: 3,072
Use a UNION like:
Expand|Select|Wrap|Line Numbers
  1. select "1" as tableno, * from table1
  2. UNION ALL
  3. select "2" as tableno, * from table2
  4. UNION ALL
  5. select "3" as tableno, * from table3
  6. ...etc
  7. ORDER BY 1, 2
  8.  
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)
Nov 12 '07 #20

P: 21
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?
Nov 13 '07 #21

nico5038
Expert 2.5K+
P: 3,072
No. Normally this requirement points to the recording of redundant data and that's a "DON'T" in a normalized database.

Nic;o)
Nov 13 '07 #22

P: 21
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
Nov 14 '07 #23

Post your reply

Sign in to post your reply or Sign up for a free account.