Access or something else?

[Lorrie]

My office has a delimma. We need to develop a system to store data for about
1500 records.

The data fields will total about 25 for each record but three of the fields
would be very long text fields...anywhere from one simply paragraph to three
pages of information.

We must produce an annual letter to each of the 1500 individuals, for our
auditors, so we want to be able to put the information into a system when
these people come on board and then use Word to mail merge the letters each
year.

I first thought that we could use Outlook with custom fields, but I have
been reading the Outlook Dincussion group and find that it won't handle the
1500 records and that each field has a limitation of 32Kb - that might not
work either.

Someone else told me that Access has a limitation of 250 characters per
field??? If that is correct that won't work either. Can anyone make a
suggestion as to which direction we should be looking in order to accomplish
this tast.

Bye the way - I have already built in some growth. We wouldn't expect to go
beyond 1500 records for the next 7-10 years.

I appreicate any advise that you can give me, as I don't want to spend hours
working on something just to find out that it has limitations that we would
exceed.

Thank you - I appreciate all help that I can get!
Lorrie

 

[Dirk Goldgar]

In

My office has a delimma. We need to develop a system to store data
for about 1500 records.

The data fields will total about 25 for each record but three of the
fields would be very long text fields...anywhere from one simply
paragraph to three pages of information.

We must produce an annual letter to each of the 1500 individuals, for
our auditors, so we want to be able to put the information into a
system when these people come on board and then use Word to mail
merge the letters each year.

I first thought that we could use Outlook with custom fields, but I
have been reading the Outlook Dincussion group and find that it won't
handle the 1500 records and that each field has a limitation of 32Kb
- that might not work either.

Someone else told me that Access has a limitation of 250 characters
per field??? If that is correct that won't work either. Can anyone
make a suggestion as to which direction we should be looking in order
to accomplish this tast.

Bye the way - I have already built in some growth. We wouldn't
expect to go beyond 1500 records for the next 7-10 years.

I appreicate any advise that you can give me, as I don't want to
spend hours working on something just to find out that it has
limitations that we would exceed.

Access certainly sounds suitable for this purpose, though I would want
to check to make sure that Word's mail-merge can handle what Access
calls "memo" (long text) fields. Your informant was only partly
right -- the "text" fields in an Access database can't exceed 255
characters, but "memo" fields can hold up to 2GB -- theoretically;
that's actually more than could fit an in an Access (Jet) database,
which is also limited to 2GB.

There's a minor, user-interface-dependent limitation on memo fields.
Entering data into a memo field via a text box on form is limited to (I
think) about 65,000 characters, though you can actually store much more
than that, as I noted above, if you the value by code. Still, I think
your "three pages of information" is much less than 65K characters, so
that shouldn't be a problem.

 

[Larry Linson]

My office has a delimma. We need to develop a
system to store data for about 1500 records.

1,500 records, or 15,000 records, is "child's play" for any version of
Access.

The data fields will total about 25 for each record
but three of the fields would be very long text fields...
anywhere from one simply paragraph to three
pages of information.

As Dirk said, you'll keep the "long text" fields in Memo Fields, so the size
of the Record is unlikely to be a problem. There is a limit of 255 Fields
per Record... and you're only a tenth of the way there. The "content" of
Memo Fields is not actually stored in the Record, but internally-linked, so
doesn't count against Access' Record size limit of 4K.

We must produce an annual letter to each of the 1500 individuals, for our
auditors, so we want to be able to put the information into a system when
these people come on board and then use Word to mail merge the letters
each
year.

There are several alternatives for handling large amounts of text...
including storing it in your database as OLE Objects in the native format
(e.g., Word or Excel documents, or pictures), hyperlinking to the data
stored outside, or using Memo Fields. In addition, Access 2007 has
introduced the feature/function/view of Attachments.

Someone else told me that Access has a
limitation of 250 characters per field?

As Dirk has said, Memo Fields are not subject to this limitation. Also see
what I mentioned before regarding other alternatives.

Finally, Access 2007 has introduced some Rich Text features so you might be
able to create the letters directly as Access Reports, with all the
formatting you need, instead of going to the effort of mail merging and
concerns over transferring Memo Fields between Access and Word.

Larry Linson
Microsoft Access MVP