Design issue - how to handle document objects

[night_writer]

I need some advice from more experienced designers on how to handle
this issue. I am creating a data base of forms used by my company. In
addition to information on the form's usage, I would like have the
users be able to open the form if they want to, either to review it or
to actually fill it out and use it. There will be several thousand
documents in this data base.

I quickly found that putting forms in as objects makes the data base
size grow huge. It might be possible for the forms to be deposited in
a directory and linked to, but we are continually switching servers
around, and directories don't necessarily keep the same names.

One idea I had was to put the forms in a table on our SQL server and
just link to that table, but I'm wondering if that has its own set of
problems.

I have created a table with a few forms in my data base, just to try
to replicate what I might do with a linked table on the SQL server. I
would like to create a report with a link to the form object, but I
can't seem to figure out how to do that.

Any thoughts and advice would be helpful at this point. I'm open to
solutions.

Thanks!

Alice

 

[Access Developer]

"Forms" in Access are the screens with which the user interacts, but it
appears to me that you are using "forms" in a generic sense, as in paper
forms on which information is recorded in specific locations. Because you
cannot "create a database of paper forms", I would understand that you are
talking about storing _images_ of the paper forms, which would include the
text that is printed on each copy of the forms as well as the information or
data and would be a very inefficient way to handle your information and in
which the information in the image would not be searchable or directly
accessible via Access.

If that is correct, have you considered storing only the information or data
that a user would enter into the forms in records in your database, and
creating appropriate Access Forms (screens) and Reports to display the data
in a way that your users expect to see it? That way the headings, text, and
information only need be stored once (or twice if you have a Form _and_ a
Report for each of your company's forms) and the data stored for each
instance. An added benefit is that the actual data will be directly
accessible and searchable in Access.

If I have misunderstood, please post back here and explain.

In answer to your other question, using a server database, such as Microsoft
SQL Server, for your data store, and using Access to create the user
interface and 'programs' is very common. The development approach and
design techniques are a little different, but "little" is the operative
word. The majority of the paying work I've done with Access since 1993 has
been using ODBC-compliant server databases (sometimes but not necessarily
Microsoft SQL Server) for the datastore. But using a server DB for your
data store still would not make it a good idea to store images of each
instance of use of the forms... you will still want to store only the
information for each instance, and the headings, etc., in presenting that
data to the users.

Larry Linson
Microsoft Office Access MVP