Solved

Compact & Repair: Cannot Find Field 'Description'

Posted on 2002-05-27
13
975 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
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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Missing Money orders... 6 70
Combobox issue 4 25
Access Date Query 28 29
Set Listbox Selected Items by Bound Column 6 13
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now