Access 2: Can't Perform Join- Combined Fields Too Long

[Fish Womper]

I am at best a part time developer of Access databases. I use Access
2.0, as this is all my employer has on its computers. Even so, to use
this ancient version requires a fairly convoluted installation
procedure on each PC on which it is used. I am self-taught from the
help files that come with Access 2.0 and from painful experience. I've
never attended any type of training course on how to develop databases
in Access.

I have no idea how to make a module, but I can do simple commands
attached to Event Procedures.

My current project is a database to record meeting minutes, agendas,
etc for a Workplace Safety Committee of which I am the secretary.
Perhaps this isn't exactly the intended use of Access but it works
quite well. It's (for me) a complicated animal with 17 tables, 20
queries including several append and delete queries, 24 forms
(including a couple of Help forms as I don't have any other way of
making Help files), dozens of macros, and 9 different reports that can
be generated. It's probably not as efficient as it could be but like I
said it works. It has taken me a month to do from scratch including
inputting 8 months worth of data. This of course has been additional
to my normal work. My core job is not developing Access Databases.

The tables contain a number of text fields, and just one Memo field,
which is the body of the minutes for each issue. One of the text
fields is the maximum 255 bytes, the others are all the default 50
bytes or less. None of the text fields or the memo field is used in
joins or in sorts.

It's working perfectly at the moment, but a problem I've had on
occasions as I've put the data into it has been that the queries
underlying the main minutes report will not execute.

The error message reads "Can't perform join, group or sort. Combined
fields are too long". It says the combined length of fields used to
join, group or sort cannot be over 255 bytes".

The grouping and sorting is done at the report stage, but running the
underlying query produces the same message.

I have examined all my primary and indexed fields and reduced them as
much as possible, but I cannot reduce them any more.

I've been very careful with my joins, in fact the whole shebang
doesn't have all that many of them. Mainly Meeting Dates and Issue
Numbers, that's just about all.

I have managed to work around the error by breaking up my main report
into several subreports, and using a total of four queries to get the
data for the report body. Each query calls the previous one and adds
additional fields. This seems like a quick and dirty solution to me
but so far it works. I've been able to add the last 2 months worth of
data without any further problems.

My question to any Access 2 experts out there is this: Is there any
more elegant way to avoid this error, which I fear will rear its head
once I'm relying on this thing to put out minutes, agendas, etc.

Does this sort of error happen in later versions of Access or only my
version?

[Larry Linson]

"Fish Womper" <fi********@xnospam.com.au> wrote

My question to any Access 2 experts out there
is this: Is there any more elegant way to avoid
this error, which I fear will rear its head once
I'm relying on this thing to put out minutes,
agendas, etc.
I really don't know what "this error" is, much less the cause in your
database, so I couldn't answer this question. If you are encountering it
with test data adapted from real production, then you are right to fear that
it may occur in production, too.
Does this sort of error happen in later
versions of Access or only my
version?

I have never encountered the particular error you describe in any version of
Access.

I think you are going to have to dig in and dig around to try to find the
combination of things that are causing the error and then determine if they
can be fixed.

Unfortunately, Access 2.0 is long since "out of support," so you aren't
going to get any "live human help" from Microsoft on Access 2.0 issues. I
think someone claimed here that even some Knowledge Base entries and
articles on the Microsoft site dealing with Access 2.0 have been removed.

Larry Linson
Microsoft Access MVP

[Fish Womper]

On Sun, 19 Feb 2006 03:42:35 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:

"Fish Womper" <fi********@xnospam.com.au> wrote

My question to any Access 2 experts out there
is this: Is there any more elegant way to avoid
this error, which I fear will rear its head once
I'm relying on this thing to put out minutes,
agendas, etc.
I really don't know what "this error" is, much less the cause in your
database, so I couldn't answer this question. If you are encountering it
with test data adapted from real production, then you are right to fear that
it may occur in production, too.

The data I have entered is actual data going back the 8 months since
our Committee started. Once I put in the fourth month I got the error,
then got it working again by putting some of the report into
subreports each with their own query. After putting in the fifth month
and again the 6th month the error occurred again. Each time I added a
query to that underlying the main report, and this worked. The last
two months have gone in without the error.

It seems like it's to do with the long text fields I am using, but
they aren't used in any joins, groups or sorts. They are the data I
want to return using combinations of meeting dates and issue numbers
to identify them.

Nonetheless I'm still quite in awe of this almost 15 year old computer
program, which still today is an extremely useful tool.

fish

Does this sort of error happen in later
versions of Access or only my
version?

I have never encountered the particular error you describe in any version of
Access.

I think you are going to have to dig in and dig around to try to find the
combination of things that are causing the error and then determine if they
can be fixed.

Unfortunately, Access 2.0 is long since "out of support," so you aren't
going to get any "live human help" from Microsoft on Access 2.0 issues. I
think someone claimed here that even some Knowledge Base entries and
articles on the Microsoft site dealing with Access 2.0 have been removed.

Larry Linson
Microsoft Access MVP

[Randy Harris]

From the description you've provided it will be difficult for anyone to help
with the problem. If you were to post the SQL for the query that is causing
the error, perhaps Larry or someone else, might be able to suggest something
to help.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
"Fish Womper" <fi********@xnospam.com.au> wrote in message
news:43***************@news-vip.optusnet.com.au...

On Sun, 19 Feb 2006 03:42:35 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:

"Fish Womper" <fi********@xnospam.com.au> wrote

My question to any Access 2 experts out there
is this: Is there any more elegant way to avoid
this error, which I fear will rear its head once
I'm relying on this thing to put out minutes,
agendas, etc.

I really don't know what "this error" is, much less the cause in your
database, so I couldn't answer this question. If you are encountering it
with test data adapted from real production, then you are right to fear thatit may occur in production, too.

The data I have entered is actual data going back the 8 months since
our Committee started. Once I put in the fourth month I got the error,
then got it working again by putting some of the report into
subreports each with their own query. After putting in the fifth month
and again the 6th month the error occurred again. Each time I added a
query to that underlying the main report, and this worked. The last
two months have gone in without the error.

It seems like it's to do with the long text fields I am using, but
they aren't used in any joins, groups or sorts. They are the data I
want to return using combinations of meeting dates and issue numbers
to identify them.

Nonetheless I'm still quite in awe of this almost 15 year old computer
program, which still today is an extremely useful tool.

fish

Does this sort of error happen in later
versions of Access or only my
version?

I have never encountered the particular error you describe in any version ofAccess.

I think you are going to have to dig in and dig around to try to find the
combination of things that are causing the error and then determine if theycan be fixed.

Unfortunately, Access 2.0 is long since "out of support," so you aren't
going to get any "live human help" from Microsoft on Access 2.0 issues. I
think someone claimed here that even some Knowledge Base entries and
articles on the Microsoft site dealing with Access 2.0 have been removed.

Larry Linson
Microsoft Access MVP

[Fish Womper]

On Sun, 19 Feb 2006 07:56:11 GMT, "Randy Harris" <ra***@SpamFree.com>
wrote:

From the description you've provided it will be difficult for anyone to help
with the problem. If you were to post the SQL for the query that is causing
the error, perhaps Larry or someone else, might be able to suggest something
to help.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

If it happens again I will do that.
fish
"Fish Womper" <fi********@xnospam.com.au> wrote in message
news:43***************@news-vip.optusnet.com.au...

On Sun, 19 Feb 2006 03:42:35 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:

>"Fish Womper" <fi********@xnospam.com.au> wrote
>
> > My question to any Access 2 experts out there
> > is this: Is there any more elegant way to avoid
> > this error, which I fear will rear its head once
> > I'm relying on this thing to put out minutes,
> > agendas, etc.
>
>I really don't know what "this error" is, much less the cause in your
>database, so I couldn't answer this question. If you are encountering it
>with test data adapted from real production, then you are right to fearthat >it may occur in production, too.

The data I have entered is actual data going back the 8 months since
our Committee started. Once I put in the fourth month I got the error,
then got it working again by putting some of the report into
subreports each with their own query. After putting in the fifth month
and again the 6th month the error occurred again. Each time I added a
query to that underlying the main report, and this worked. The last
two months have gone in without the error.

It seems like it's to do with the long text fields I am using, but
they aren't used in any joins, groups or sorts. They are the data I
want to return using combinations of meeting dates and issue numbers
to identify them.

Nonetheless I'm still quite in awe of this almost 15 year old computer
program, which still today is an extremely useful tool.

fish

>
> > Does this sort of error happen in later
> > versions of Access or only my
> > version?
>
>I have never encountered the particular error you describe in any versionof >Access.
>
>I think you are going to have to dig in and dig around to try to find the
>combination of things that are causing the error and then determine ifthey >can be fixed.
>
>Unfortunately, Access 2.0 is long since "out of support," so you aren't
>going to get any "live human help" from Microsoft on Access 2.0 issues. I
>think someone claimed here that even some Knowledge Base entries and
>articles on the Microsoft site dealing with Access 2.0 have been removed.
>
> Larry Linson
> Microsoft Access MVP
>
>

[Arno R]

If you could zip me the database with *some data* that produce the error I would be willing to look at it.
I am still maintaining Access 2.0 databases, though for development I *only* use Acces97, Access 2000 and Access 2003 here.
I still think that Access 2.0 was/is a very good version. I assume the problems you describe will appear in any 'higher' version.
If you would send me the db I could test that also.

BTW: I do not understand what you said concerning 'a fairly convoluted installation procedure on each PC'.
IME the Access 2.0 install is a breeze... For instance I can install the runtime with a 3 MB zip ...

Arno R
"Fish Womper" <fi********@xnospam.com.au> schreef in bericht news:43***************@news-vip.optusnet.com.au...

On Sun, 19 Feb 2006 03:42:35 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:

"Fish Womper" <fi********@xnospam.com.au> wrote

> My question to any Access 2 experts out there
> is this: Is there any more elegant way to avoid
> this error, which I fear will rear its head once
> I'm relying on this thing to put out minutes,
> agendas, etc.

I really don't know what "this error" is, much less the cause in your
database, so I couldn't answer this question. If you are encountering it
with test data adapted from real production, then you are right to fear that
it may occur in production, too.

The data I have entered is actual data going back the 8 months since
our Committee started. Once I put in the fourth month I got the error,
then got it working again by putting some of the report into
subreports each with their own query. After putting in the fifth month
and again the 6th month the error occurred again. Each time I added a
query to that underlying the main report, and this worked. The last
two months have gone in without the error.

It seems like it's to do with the long text fields I am using, but
they aren't used in any joins, groups or sorts. They are the data I
want to return using combinations of meeting dates and issue numbers
to identify them.

Nonetheless I'm still quite in awe of this almost 15 year old computer
program, which still today is an extremely useful tool.

fish

> Does this sort of error happen in later
> versions of Access or only my
> version?

I have never encountered the particular error you describe in any version of
Access.

I think you are going to have to dig in and dig around to try to find the
combination of things that are causing the error and then determine if they
can be fixed.

Unfortunately, Access 2.0 is long since "out of support," so you aren't
going to get any "live human help" from Microsoft on Access 2.0 issues. I
think someone claimed here that even some Knowledge Base entries and
articles on the Microsoft site dealing with Access 2.0 have been removed.

Larry Linson
Microsoft Access MVP

[Fish Womper]

On Sun, 19 Feb 2006 13:47:40 +0100, "Arno R"
<ar***********@tiscali.nl> wrote:

If you could zip me the database with *some data* that produce the error =
I would be willing to look at it.
I am still maintaining Access 2.0 databases, though for development I =
*only* use Acces97, Access 2000 and Access 2003 here.
I still think that Access 2.0 was/is a very good version. I assume the =
problems you describe will appear in any 'higher' version.
If you would send me the db I could test that also.
A colleague has tried to convert it into Access97 and it gives an
error message and won't open, even though it's working OK in Access 2.
I have Access2k (I think) on a laptop and could test it there.
BTW: I do not understand what you said concerning 'a fairly convoluted =
installation procedure on each PC'.
I work in an organisation that has literally hundreds of small
offices. In the area my Committee operates there are around 45
locations each with identically set up PC's running W2K. Some larger
locations have more than 1 PC, some many more. They are all on a WAN.

Access 2.0 was originally installed on the (then) PC's as part of
Office 4.3 (the version that included Word 6). When they upgraded all
the PC's they bought Office 97 but without Access. Office 97 is still
used on all these machines. Someone put together a self-extracting exe
with the Y2K fixed Access 2.0 and just dumped this file onto all the
machines, figuring I suppose that anyone who wanted to use Access
should be able to work out how to install it correctly. This file
(8.66mb including the Northwind and Solutions databases) has to be
placed into a dedicated directory and run to unpack the contents. Then
a file association needs to be set up. Then Access needs to be started
so that a MSACC20.ini file is created in the WINNT directory. Now the
file of the same name unzipped in the unpacking process needs to be
opened and its contents selected and copied, ensuring that the paths
specified in that file are correct, which they aren't because they
point to the former Office installation which was on Drive C (which on
these machines is completely locked down), so you have to change all
the paths you find. Next the MSACC20.ini in the WINNT directory needs
to be found using a search (the entire C drive is completely hidden on
these machines), and the contents of the clipboard pasted into this
file. At a pinch the messing about with MSACC20,ini can be omitted but
Access will then not be anywhere near fully functional.

I would call this a fairly convoluted install procedure. It's not
hard once you know how, and I now have the whole Access dir on a USB
drive so I can just copy it across and fix up the ini file and
association IF I am physically present, but I have my own job to do
and you try guiding someone who has trouble sending an e-mail through
the procedure above.

Some of the people in charge of these machines are not very computer
literate at all, and the procedure above is way too much to expect of
them.

As far as sending the database, I'll have to try to get the error to
occur again, perhaps in a query I create for this purpose. If I can do
that I'll forward it to you. I will have to trust you with the data in
it (which is actual Safety Committee minutes). Thanks for your offer.
fish
IME the Access 2.0 install is a breeze... For instance I can install the =
runtime with a 3 MB zip ...=20

Arno R
"Fish Womper" <fi********@xnospam.com.au> schreef in bericht =
news:43***************@news-vip.optusnet.com.au...

On Sun, 19 Feb 2006 03:42:35 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:
=20

"Fish Womper" <fi********@xnospam.com.au> wrote

> My question to any Access 2 experts out there
> is this: Is there any more elegant way to avoid
> this error, which I fear will rear its head once
> I'm relying on this thing to put out minutes,
> agendas, etc.

I really don't know what "this error" is, much less the cause in your=20
database, so I couldn't answer this question. If you are encountering =it=20with test data adapted from real production, then you are right to =fear that=20it may occur in production, too.

=20
=20
The data I have entered is actual data going back the 8 months since
our Committee started. Once I put in the fourth month I got the error,
then got it working again by putting some of the report into
subreports each with their own query. After putting in the fifth month
and again the 6th month the error occurred again. Each time I added a
query to that underlying the main report, and this worked. The last
two months have gone in without the error.
=20
It seems like it's to do with the long text fields I am using, but
they aren't used in any joins, groups or sorts. They are the data I
want to return using combinations of meeting dates and issue numbers
to identify them.
=20
Nonetheless I'm still quite in awe of this almost 15 year old computer
program, which still today is an extremely useful tool.=20
=20
fish=20
=20
=20

> Does this sort of error happen in later
> versions of Access or only my
> version?

I have never encountered the particular error you describe in any =version of=20Access.

I think you are going to have to dig in and dig around to try to find =the=20combination of things that are causing the error and then determine if =they=20can be fixed.

Unfortunately, Access 2.0 is long since "out of support," so you =aren't=20going to get any "live human help" from Microsoft on Access 2.0 =issues. I=20think someone claimed here that even some Knowledge Base entries and=20
articles on the Microsoft site dealing with Access 2.0 have been =removed.
Larry Linson
Microsoft Access MVP

[Arno R]

"Fish Womper" <fi********@xnospam.com.au> schreef in bericht news:43*************@news-vip.optusnet.com.au...

Access 2.0 was originally installed on the (then) PC's as part of
Office 4.3 (the version that included Word 6). When they upgraded all
the PC's they bought Office 97 but without Access. Office 97 is still
used on all these machines. Someone put together a self-extracting exe
with the Y2K fixed Access 2.0 and just dumped this file onto all the
machines, figuring I suppose that anyone who wanted to use Access
should be able to work out how to install it correctly. This file
(8.66mb including the Northwind and Solutions databases) has to be
placed into a dedicated directory and run to unpack the contents. Then
a file association needs to be set up. Then Access needs to be started
so that a MSACC20.ini file is created in the WINNT directory. Now the
file of the same name unzipped in the unpacking process needs to be
opened and its contents selected and copied, ensuring that the paths
specified in that file are correct, which they aren't because they
point to the former Office installation which was on Drive C (which on
these machines is completely locked down), so you have to change all
the paths you find. Next the MSACC20.ini in the WINNT directory needs
to be found using a search (the entire C drive is completely hidden on
these machines), and the contents of the clipboard pasted into this
file. At a pinch the messing about with MSACC20,ini can be omitted but
Access will then not be anywhere near fully functional.
Couldn't you copy the MSACC20.ini with a batch file or similar!.
No need to first create the file IIRC.
All you would need to do IMO is install Access from the original Office4.3 CD and
install the Service Pack. Don't forget the service pack!!
You don't *have* to bother with the Y2k Fix, but yes you can use that also.
If you would have the ODE you could simply copy the needed files in one directory (the app-dir) and off you go...
But that was not your question, so don't bother if it works for you.
As far as sending the database, I'll have to try to get the error to
occur again, perhaps in a query I create for this purpose. If I can do
that I'll forward it to you. I will have to trust you with the data in
it (which is actual Safety Committee minutes). Thanks for your offer.

You are welcome.
You would make your data anonymous I suppose?

Arno R

[Fish Womper]

On Mon, 20 Feb 2006 09:56:12 +0100, "Arno R"
<ar***********@tiscali.nl> wrote:

Couldn't you copy the MSACC20.ini with a batch file or similar!.
Yeah, I guess, if I knew how to write such a thing. I will give it a
try.
No need to first create the file IIRC.
All you would need to do IMO is install Access from the original =
Office4.3 CD and=20
install the Service Pack. Don't forget the service pack!!
All I have is this one self extracting exe file. No CD, no disks. The
resulting installation shows as licenced to our organisation. This
self extracting exe has been created by someone in our IT section to
keep Access 2 available when they switched to Office 97 many years
ago.

I am assuming that Windows 2000 does not have a file msacc20.ini until
Access 2 is run on it. I'm not even sure if the service pack is
included. How do you tell?
You don't *have* to bother with the Y2k Fix, but yes you can use that =
also.
Until they added the Y2K fix it would recognise the year 00 as 1900,
and so on. Maybe the service pack also included the Y2K fix.
If you would have the ODE you could simply copy the needed files in one =
directory (the app-dir) and off you go...
But that was not your question, so don't bother if it works for you.
=20

As far as sending the database, I'll have to try to get the error to
occur again, perhaps in a query I create for this purpose. If I can do
that I'll forward it to you. I will have to trust you with the data in
it (which is actual Safety Committee minutes). Thanks for your offer.=20
You are welcome.
You would make your data anonymous I suppose?

Would you believe I created a huge query and could not get the error
to occur?
fish
Arno R

[Arno R]

"Fish Womper" <fi********@xnospam.com.au> schreef in bericht news:43***************@news-vip.optusnet.com.au...

I am assuming that Windows 2000 does not have a file msacc20.ini until
Access 2 is run on it. I'm not even sure if the service pack is
included. How do you tell?

Check the version of the file msajt200.dll (Right-click the file and choose properties, tab version)
It 'could' read like 2.50.0.1606 (left part *must* read like 2.50)
Without the SP it reads something like 2.00.xxxx

Arno R