Should I switch from Excel to Access as the data source

[GLGO]

I have a collection of motions I have developed over the years with Microsoft
Word. Each one has a number of variables such as name. cause number, date,
charge, etc. I have been using Excel as the data source for these variables
and Word mail merge to fill in them into variable fields in the documents.
The Excel data worksheet has become unwieldy due to 121 separate non-repeated
fields.

Any suggestions for using Acess 2003 the data source?

 

[John W. Vinson]

I have a collection of motions I have developed over the years with Microsoft
Word. Each one has a number of variables such as name. cause number, date,
charge, etc. I have been using Excel as the data source for these variables
and Word mail merge to fill in them into variable fields in the documents.
The Excel data worksheet has become unwieldy due to 121 separate non-repeated
fields.

Any suggestions for using Acess 2003 the data source?

You should be able to import your Excel spreadsheet into an Access table, and
use that table with Word Automation or mailmerge to create your document.

You might run into the record size limit: a single record can contain no more
than 4000 bytes (2000 Unicode characters). If a lot of these 121 fields are
Text you could be pushing or exceeding this limit.

*HOWEVER*.... I'll be very surprised indeed if there are in fact 121 separate
non-repeated fields which cannot be decomposed into one or more one-to-many
relationships. Maybe you have good reason not to do so but I find it a bit
surprising (I've worked with a couple of tables with 60+ fields but didn't
like the experience...)

 

[Jeff Boyce]

I'll echo John's observation ...

Access is a relational database. If you've been using Excel, then I'm
sorry, but you may have to UN-learn ways of handling things if you want to
get good use from Access' relationally-oriented features and functions.

Why bring this up? Because it is, as John points out, quite rare to have a
well-normalized table with more than, say, 30 fields.

If "normalization" and "relational" are unfamiliar, plan on spending some
time coming up to speed on them if you plan to use Access.

This is a "pay now (learn relational database design) or pay later (keep
coming up with work-arounds)".

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[vbasean]

I hope to offer a little advice. This appears to be a 'custom' solution to a
problem you have. I don't think there's a problem with custom solutions as
long as they are understood to be such.

Because this is an 'Access' forum there is plenty of advise on Access but
little advise on needs versus ability.

To use Access 'effectively' one needs a rudimentary understanding of
databases. In your case, the time and effort invested in learning Access to
'effectively' employ it may outweight reexamining your original problem at
hand.

You currently have 121 fields. How many rows do you have? Do you reuse
this data? Have you concidered making a form in Word? (The learning curve on
Word forms is conciderably shorter than that of Access Databases) I would
assume that some of these fields problably act as partial clauses in your
documents.

Word documents can serve as a database of sorts. With some of Windows new
searching and indexing tools you can locate documents pretty quick.

Rethinking of your problem from different avenues (Even that of Access) is a
great way to come up with a new and better solution if the current one is
become unwieldy. Access is not the be-alll end-all solution.

 

[John W. Vinson]

Rethinking of your problem from different avenues (Even that of Access) is a
great way to come up with a new and better solution if the current one is
become unwieldy. Access is not the be-alll end-all solution.

As a strong Access advocate... let me just say Thank You, Chris; you're
absolutely right. Use the right tool for the job, and (though I would have no
clue how to do it in practice) Word may indeed be more appropriate in this
case.