query design appears to be overwritten - how, why?

[Helen]

Hi everyone,

I'm using Access 2000 with Windows XP. The database is broken into backend
and front end, but both files reside on the server (yes I know - don't
ask... )

I am working on a database that someone else has set up. He has append
queries running from one form that do some calculations and populate a table
of repayments (the db manages loans to clients). The queries draw data from
the Loans table via the LoanID which is displayed on the form. I want to
reuse these queries (that are working just fine in their current context)
with a couple of changes to the calculations, and run them from a different
form.

I've opened each one, done a File, SaveAs, given them a different name, made
the changes to the calculations and to the criteria specifying where the
LoanID is being picked up (ie. from my new form that i'm running the new
versions from), saved, and copied the names of the new versions into the
code on the click event on the button where i'm running them from, etc. etc.
Throughout this process I've saved multiple times...

Now comes my problem. When I test the queries the first couple of times they
work just fine. ie. the new calcs give the desired results, and the records
are appended to the table no problem. THEN, suddenly on the third or fourth
run-through, one of them doesn't work (it's always the same one - that would
seem to be significant). And when I go into design view, the original calcs
and criteria from the original version of the query are back!! It's like my
changes haven't been saved. Except that they have, many times. I have saved,
closed the query, opened it again and observed that my changes are there..

I've had someone sit with me and double check my actions, and that i'm not
just going into the wrong version of the query, or whatever. I've done the
usual things like create a new query from scratch, and build my criteria and
calcs from scratch. I've done a compact and repair on the database. I
actually started with a blank db and copied all the objects across in case
there was some residual corruption of the file. I even tried running the SQL
from the code, rather than running the query. That worked a treat also for
about 3 times. Then my changes to the code on click event disappeared!!!

Any other suggestions? Any ideas as to why this would be happening?

Many thanks in advance and regards to all,

Helen

 

[Allen Browne]

Hi Helen.

This sounds like a problem caused by Name AutoCorrect. If all users are
logging into the same front end file, that's probably triggering the issue.

To correct it, make sure all other users are out of the database, and follow
these steps:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

7. Give each user their own copy of the front end. If you want, you can put
the copy into the user's workspace on the server. The important thing is
that they are not actually opening the same front end file.

BTW if you have users with different versions of Access (e.g. 2000 and
2003), use the *oldest* version to perform these steps.

 

[Helen]

Hi Allen,

Thanks so much!! I can't try your solution until next week now, but I'm
optimistic...

A couple of questions - in your step 3, what do you mean the command prompt?
Start, Run? Or in the shortcut that we're using to open the db? or
elsewhere?

And there is always only one user in the database, which is why I haven't
been harassing them to give each user their own copy.... But now is a good
time to get that issue sorted out. Do you think the Name AutoCorrect would
still trigger the problem under those circumstances?

Thanks heaps Allen, I'll let you know how I go.

Regards,
Helen

 

[Allen Browne]

By "Command Prompt", I mean what we used to call the DOS Prompt.
These days it's usually under:
Start | Programs | Accessories | Command Prompt.
Start | Run will do if you prefer, though you may not see the results.

The link to Name AutoCorrupt article lists just some of the acknowledged
problems with this horror. There's *much* more, so even though some of those
issues have been addressed, I would not let it anywhere near one of my
databases. Earlier this year I made a copy of a client's database that had
some queries and forms linked to tables that had been deleted, so I made a
copy and enable Name AutoCorrect so I could programmatically trace the
dependencies. A2003 SP1 constantly crashed (shut down by Windows), *every*
time I tried to run the code, so the experiment only lasted a few minutes,
and it was clear that MS still has not fixed the many, major issues with
this nightmare.

 

[Helen]

OK, got it re: the command prompt many thanks Allen. And congratulations
on your site - it's great! I'll be having a good old read..

Hey do i have to do your steps on both the backend and front end? Or just
the front end?

re: your experiment: wow!! That's bad - I had no idea...I've worked a lot in
Access over the years and have clearly been very lucky to be unaffected by
the problem so far. I'll certainly be disabling Name AutoCorrect from now
on - this has been the most incredibly frustrating experience....

Thanks again for your help, and to all others who make these groups such an
invaluable resource.

 

[Allen Browne]

Turn off Name AutoCorrect (through Tools | Options | General) in both the FE
and the BE, and compact both.

The Decompile is only needed on the file that contains code, so probably
just the FE. Same with References (though minmal references never hurts in a
BE either.)

 

[Helen]

OK great, thanks Allen.

Turn off Name AutoCorrect (through Tools | Options | General) in both the
FE and the BE, and compact both.

The Decompile is only needed on the file that contains code, so probably
just the FE. Same with References (though minmal references never hurts in
a BE either.)