[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Compact & Repair: Cannot Find Field 'Description'

Posted on 2002-05-27
13
Medium Priority
?
1,203 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

650 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