Compact & Repair: Cannot Find Field 'Description'

I have a database that keeps crashing during Compact & Repair. The message i get is "Cannot find field 'Description'"

The trigger for the error seems to be either copying a v large table under a new name or copying all the records from this table into another

cnCurrent.Execute "INSERT INTO [Scenario] SELECT * FROM [Base]"

If I delete the table [Scenario] then the database will compact without a problem.

Unforuntately i need the user to be able to copy the whole of [Base] as a new table [Scenario] and then compact the file.

Oh one last thing - there is no field called Description!
alanmurrayAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NetminderConnect With a Mentor Commented:
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0
 
RichardCorrieCommented:
The Description field is on a msys table (access system table).

I have had a similar problem.  The issue for me was that I had a table with a memo field for "additional info". In use however the users were cutting a pasting entire e-mails and/or word documents into the field.  The field got corrupted on one record and threw everything out.

The work around that I had to come up with was to create a seperate "data" mdb that had one table with the memo field and key field from the original table.

The repair and compact on this MDB has worked so far!!

Richard
0
 
alanmurrayAuthor Commented:
Which msys table is it supposed to be in?

I need to be able to read and write to the Scenario Table so i can't really have it in another mdb.

I still don't see why copying a table should damage one of the msys tables *confused*
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
alanmurrayAuthor Commented:
Also with your mdb there was a problem with what was being put into a field. What confuses me here is that there isn't anything wrong with the table - it's just a copy.

Is there any chance it could be a size issue? the table is about 280,000 records and the mdb is almost .5 gig big now (altho it will shrink a lot if i can get it to compact again)...
0
 
RichardCorrieCommented:
I do not know why the Mys  tables get confused.  The size could be a contributing factor; Acess can be 2GB in size.


The other MDB is attached to the original MDB
(use File|Get External Data|Link) so that you can treat it as as "native" table.


BTW You should have the Data and "objects" in seperate MDB files as this then helps the upgrade process
0
 
alanmurrayAuthor Commented:
Hmmm I've tried putting the Base table in an MDB all on its own. But even in this new MDB, if i copy it then i get the same error on compact...
0
 
alanmurrayAuthor Commented:
I wonder if it's anything to do with the size of clipboard? I can't manually copy all the records in the table because the copy limit is 65,000. Don't know if this is relevant or not...
0
 
RichardCorrieCommented:
You should be copying by using a query; there is no limit.

Does the Base table have a memo field? if so try copying it without the memo field to see what happens.

Richard
0
 
RichardCorrieCommented:
You should be copying by using a query; there is no limit.

Does the Base table have a memo field? if so try copying it without the memo field to see what happens.

Richard
0
 
alanmurrayAuthor Commented:
i get the same problem using SQL, copying all the records by hand, copying the table by hand and using docmd...

i haven't got any memo fields unfortunately...
0
 
RichardCorrieCommented:
ummm,
grasping at straws here....

1) Create a NEW mdb file and import all the objects, except Base and Sceanario tables

2)Create Base and Sceanario manually

3) Link to old DB Base and Sceanario tables (they will appear as Base1 and Sceanario1)

4) Create queries to copy from base1 and Sceanrio1 to new table

5) try compacting.

Richard
0
 
alanmurrayAuthor Commented:
hehe a nice idea but sadly i can't recreate Base very easily. It was made using recordset append loop which would randomly go wrong and miss out records. After many attempts I got it to work but it's inconsistent and...well suffice to say copying Base is the only way to make Scenario.

However if i create the structure manually and then use SQL to copy the records in it seems to work! It seems to have a problem copying table structures...
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.