I think I am doing things the hard way

[Guest]

I have created an employee database with three report packets to print "new
Hire pkt" ,"Training Pkt" etc. When the control button is pushed, I have
reports created that will print with the employees data on pertinent fields.
The database is getting pretty large because of all the forms. Is there a way
to link forms and have a mail merge so we can do some of this outside the
database to save on room?

 

[Guest]

How close to 2 Gb are you getting?

Maybe do a backup and then a Compact and Repair.

 

[Guest]

Note that 2Gb is the absolute maximum filesize for an Access database. Once
you hit that size, your database will be trashed (and usually irrecoverable.)

As to MailMerge, that's a function of Word. You can use the Access database
as the data source for the MailMerge. OR, you can open a Word doc
programmatically in Access, spin through it, and insert data of your choice
into bookmarked locations. Then you can programmatically print the document
and close it. This can be done in "hidden" mode, so you don't have to see
Word open.

 

[Guest]

Printing reports really has no effect on your database size. It is not
creating any new data, so It should not be an issue.
It is a good idea to occasionally compact and repair your database.
Also, hopefully, you have a split database where the tables and relations
are in one mdb and the forms, reports, queries, macros, and modules are in
another. If not, read up on splitting your database. That is the correct
way to implement an Access application.

 

[Guest]

Thank you for the reply. The way I set up the database is that for each pkt
there are about 10-15 forms that get filled out from data input into the
employee record. I inserted the original document into the report module,
added fields to the report and then named the report and left them in the
database. There are approximately 40 reports, that is why the database is so
large. Can you give me an example of the program I would have to write to
open the orginal report outside of the database? for instance, new hire
packet will use an Emergency Contact Form, W-4 etc.

 

[Larry Linson]

Using mail merge to prepare the Reports in Microsoft Word will not
_necessarily_ make the database smaller -- you'll still have to collect the
data, you'll still have to store it, and you'll have to retrieve it and
export for the mail merge. Your database should be split into a "front end"
database with queries, forms, reports, macros, and modules and a separate
"back end" database with tables, relationships, and data. The front end
should be linked to the back end... you can accomplish that with the Linked
Table Manager.

And, some people have told you what a "big" database is (of course, the
front end can be near maximum and the back end can be, also), but you have
only said your database is "large", not _how_ large. First, visit MVP Tony
Toews' site http://www.granite.ab.ca/accsmstr.htm and look for information
on splitting the DB. Once you've done that, run Compact and Repair on both
the front end and the back end databases and TELL US how big they are -- if
they aren't 'way over 1GB... yes, that's gigabyte, not megabyte... then you
don't have a size problem.

I've worked on databases with over 1,000 Access objects (table defs,
queries, forms, reports, macros, and modules) that were not "too big".
Thus, 40 reports doesn't really sound _outrageously large_ to me.

If you mean you placed a picture of the document in the report, then created
the report by placing controls on top of that in the appropriate places, and
if there have not been some significant improvements over working with
controls on top of a picture since the last time I did so (well over ten
years ago), then I sympathize with you for the effort and the time you
expended.

I'm not sure what you mean by "open the original report outside the
database". I fear you have an over-optimistic hope that using mail-merge
will be much easier and much more productive than it is really going to be.

PLEASE, do the split, compact, and repair and let us know the size of the FE
and BE databases. Then, tell us what, other than your view of the "size
problem" makes you think you need to do _something_ external to the
database. Thanks for helping us to help you.

Larry Linson
Microsoft Access MVP