Update query stopped working

[Chris]

Hello there,

I have a database in which my update queries stopped working. In essence
what I do is import data from excel and create tables. Then I run several
make-table queries on these tables finding data I need. Next I update a table
(BigTable) with data from these newly-made tables. I have been doing this for
several months now and as far as I know I have not changed anything in the
database and nobody else had access to it.

Here is a sample from one of the many tables. The make-table query does run
properly, I checked the data. In the case of the sample query the make-table
query lists 898 records, while the update query in database view only lists
807 and therefore updates these 807 records on the BigTable. Which is
incorrect. The update-query's sql statement is:

UPDATE BigTable INNER JOIN MadeTable ON BigTable.EMIS = MadeTable.EMIS SET
BigTable.HDATE = MadeTable.HDATE, BigTable.HVAL = MadeTable.HVAL;

where EMIS is the data on which the relationship is based and is a primary
key in the BigTable, HDATE AND HVAL being the fields that need to be updated.

I hope I included everything needed for information.
A friend of mine had a quick look at it and said the sql statement looks
good and she has experienced this problem before. I have rewritten the update
queries but that didn't change anything.

Thank you so much for any help you can give.

Chris

 

[Dennis]

Check the size of your DB. What you are doing will cause the DB to bloat very
quickly. When you get near to the 2gig size limit, it will start to fail but
does not tell you that it is too big. It may be so big that it will not
compress and you will have to create a new DB and import all your tables,
queries etc. into it.

 

[Chris]

Hello and thank you for the reply.

My database is 30MB in size and I always delete the old imported tables, so
that's probably not the problem. But thanks anyway.

I forgot to say previously that I use access 2003.