911! 911! MakeTable Queries Won't Work/Error 3001

[LarryP]

This morning my database (A2000 format, running within Access XP on Windows
2000 Pro) worked fine. Then IT showed up, gave me a new computer, and
upgraded me toWindows XP Pro/Office XP Pro. Now every one of my maketable
queries throws an "Error 3001 -- invalid argument" error. I get the normal
"You are about to run..." and "Old table will be deleted..." warnings, then
the error. No references flagged as missing.

Any ideas? This is a major disaster!

 

[John Nurick]

Hi Larry,

No specific ideas but from what I've read this usually seems to be
related to corruption or things getting out of sync in the MDB file. Or
to the file approaching the 2GB size limit. There's also been a
suggestion that Name AutoCorrect may be implicated; it's usually better
turned off anyway.

For the former, see
http://www.granite.ab.ca/access/corruption/symptoms.htm. Another
suggestion I've seen is to copy the SQL of each affected query, delete
the query, create a new one, and paste the SQL into that before saving.

Otherwise, all I can suggest is a search at http://groups.google.com,
afte which you'll know more about this than I do.

 

[LarryP]

Thanks, I'll look into those. Size limits are certainly a possibility, as
this is a VERY big database by Access standards. several of the tables are
well in excess of a million records, and of course when queries run one of
those against another, the numbers get pretty staggering. I achieved a
partial fix yesterday by going into the Registry and raising the record lock
limit from the default to 1,000,000, and also installing the latest Jet MDAC
to be sure I was current. After that it didn't happen every time, but did
continue to happen intermittently, at which time the only cure seemed to be a
repair and compact.

 

[John Nurick]

If compacting fixes the problem temporarily, it could be the file size.

How big is the .mdb file after compacting?

From what you've said I get the impression that the everyday operation
of your database involves creating quite a few new tables. If these are
temporary tables to store intermediate results it's probably worth
keeping these in a separate, temporary .mdb file. That way your "main"
..mdb file won't continually be bloating from the temporary tables and
having to be compacted.

If however the routine use of make-table queries is for anything but
temporary tables, it's usually a sign that the data structure is
incorrect.

 

[LarryP]

Compacted, the database is presently about 750mb; when the processes run, it
bloats to around twice that. Yes, there are many make-table queries. In
most cases, we resorted to those because trying to use the "source files"
directly (source files are linked pipe-delimited text files) caused errors.
Best I can recall, they were "must use an updateable query" errors, but
that's been some months ago. At any rate, our work-around was to pull the
raw source data into a temporary "local" table that supports subsequent
queries.

I've thought about deleting each of these temporary tables once the
process(es) that make use of it has completed, but this DB is still in
development to some degree, and it's often necessary to go back step by step
through the process to figure out why a calculated value is coming out wrong.
Also, I'm not sure that deleting the temp table will actually reduce the
size of the DB unless an actual repair and compact is done.

 

[John Nurick]

The Jet ISAM can't update delimited text files, and because of the way
Access or Jet (I'm not sure which) handles queries there are update or
append queries that produce a "must use an updateable query" error even
though they are not attempting to modify the linked table in any way.

Temporary tables are one way round this problem, but you don't need to
have them in the same .mdb file as the rest of the database. Instead,
create the temporary .mdb and modify your make-table queries to create
tables there rather than in the main .mdb file (using an IN clause in
the SQL statement). Then access these as linked tables in the usual way.

There's more information here http://www.tek-tips.com/faqs.cfm?fid=5980
on using temporary .mdb files.

 

[LarryP]

Seems like a clever workaround. Will try it. Thanks, Joh.