Solved

Compact & Repair: Cannot Find Field 'Description'

Posted on 2002-05-27
13
1,093 Views
Last Modified: 2008-02-01
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!
0
Comment
Question by:alanmurray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 7037245
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
 

Author Comment

by:alanmurray
ID: 7037270
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
 

Author Comment

by:alanmurray
ID: 7037328
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 10

Expert Comment

by:RichardCorrie
ID: 7037348
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
 

Author Comment

by:alanmurray
ID: 7037380
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
 

Author Comment

by:alanmurray
ID: 7037396
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
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 7037566
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
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 7037696
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
 

Author Comment

by:alanmurray
ID: 7038599
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
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 7038667
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
 

Author Comment

by:alanmurray
ID: 7038686
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7227002

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
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7248097
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question