an ab-normal bite in the rear end ?

[barret bonden]

Picked up a new client with an old MDB , a lawyer's office dealing with
state forms of great length and thus 100's of fields in each table; not only
can I not change

any of this, I have to allow for MORE fields for each record; Access wouldn't
let me add what I needed.

My solution was to create a 2nd table and link the two tables via a query.

The query is linked by the key field from the primary table and

a foreign key field in the new table.

This seems to work; when I choose an existing client and then open

the new form (which has a mix of fields from both the primary and new

table) I see existing fields with data, and when I save the record the

second table appropriately fills in the foreign key field with the data
from the

primary table.

Does this sound stable ? Will I get bit in the end ?

 

[John Spencer (MVP)]

Well, yes you will get bit.

A query is limited to roughly 2000 characters per record, so you stand a good
chance of running into a problem. MEMO fields (and OLE fields) only use a few
bytes (18?) to point to the data - so these fields allow you have up 32K
characters entered via the keyboard. If you shove data into a memo field
using VBA or a query, you can hold up to a gigabyte or more of data in the
memo field.

I would suggest that if you are not allowed to redesign the data structure
that you choose to walk (run fast) away from the project. Or redesign
everything using SQL Server as a backend and some other package as a front end.

Good Luck.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[barret bonden]

John:

Many thanks for the reply. The query reads (when I look at it in Access SQL
of course ) about 1000 characters , and this with one table represented by a
"*", which I assume is further translated into perhaps 1000 more; so if I'm
understanding you
(am I ? this is why I'm writing) then I'm up against it -
It's a shame - it all looks to be working - but I know better than to take
his check and buy into trouble.

 

[John Spencer (MVP)]

Example
20 text fields defined as 125 characters each.

If the user puts only 10 characters into each field then you have used
approximately 200 characters of the 2000 available (there is a bit of
overhead). However, if the user tries to use all 125 characters in each field
then the total number of characters will exceed the 2000 characters limit and
an error message will be generated and the record cannot be saved.

Queries have the same limit as tables
255 fields
2000 characters

The 255 field limit - I did not mention that earlier. Table 1 200 fields,
table 2 200 fields, join the two tables and try to display all four hundred
fields = error message.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[barret bonden]

John:

If I move some of the tables to an ODBC backend like MYSQL , but still
attempt to use an overlarge Access query will I still run the risk of
corrupted data ?
What would you suggest as an answer here ? Client is stuck needing these
reports, and also commited to Access as a practical matter-

I'll post this up top as a new message as well -

 

[John Spencer (MVP)]

I don't think you will get corrupted data per se. But Access is still limited
to 255 fields at any one time. So if you are ever in the situation where you
need to see 256 or more fields at one time you will find that you are out of luck.

Just moving some of the tables or fields to another database does not help you
if you want to use Access to work with the fields - the limit is 255 fields at
any one time in a report or on a form.

You can "cheat" by using sub-forms and sub-reports to handle additional
fields. Each sub-form (or sub-report) can handle up to 255 fields. In theory
that gives you the ability to work with a very large number of fields at one
time. I think that doing so will be slow and cumbersome.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[david]

But possibly workable in this situation, if it is like other legal
database's I have worked with, ie limited by the speed of the
printer, not the speed of the database access.

(david)