Copy Table Problem

[AC5FF]

I could have sworn I have inquired on this problem before but I could not find any information... Soooo Here we go again!

Occasionally - due to computer issues or whatever - my db has crashed/locked up. To get things back up and running I use a previous day's backup copy and then copy the main work tables out of the crashed DB over to the backup. However I have a problem doing that with one of the tables and I have never figured out why.

Basically - copy the table from one DB and then when I paste it into the new DB I get this error:

"Operation is not supported for this type of object"

From everything I know to look at, this is just a regular table - I do not believe or see where it would be anything different. Yes, it is linked to other tables within the DB, but I don't see where that would stop a copy/paste operation. There are other tables that I have to do this to and they copy/paste just fine w/out any of these errors.

Any ideas on why this might be happening or what else I should be looking at?

Thx!

 

[boblarson]

You don't COPY and paste them. You should IMPORT them. Copying and Pasting will propagate the same corruption. Importing will clean it up.

 

[AC5FF]

Importing the table gives the same error.

I did find a way that appears to work (still checking things out). But if I take the corrupt db and export the file to Excel, then go into my backup and import from Excel that seemed to do the trick.

The corruption problem does not appear to be centered in the tables. When a user logs into the DB their name, computer name, date/time, etc are stored. When the network pushes down changes to the computers the DB seems to corrupt at that time; not being able to pull the users information; and when that happens nothing is accessable/updateable. Again; how/why this happens is beyond me...

But the table that I am trying to copy (or import) is not corrupt. I get this same error when I am doing work on my own backup copy of the DB and want to copy the updated data from the tables over into my working copy. So, whether or not the DB is crashed/etc does not effect this error I am getting.

 

[War Horse]

If I understand your issue clearly, here's my advice:

First, copying your data to excel and then importing it to a fresh Access db does overcome your initial problem. However, your field properties in your new table will have Access default properties. For example, if you have a field for StaffID and your IDs are 6 Characters long, your field property will have 255 Character long property. This means your Access table is carrying more than necessary load. What happens is you carry a load more than you can carry? you crash.

so, check the field properties and make sure you change the 255 to the maximum number of characters you may enter for each field. To do this,

1. Right Click the table in Access
2. Click on Design View
3. Click on each field
4. Look below at the field properties and adjust field size

This way, your Access file size will reduce and even make it faster to work with.

Secondly, I will suggest you split your database. Have all your table on one access file and from another access file, link to your tables so as to create queries, forms etc.
Sometimes, crashes are due to opening and closing a database too many times. Using splits, this is most unlikely as the table database is opened and closed less of the time.

I hope this works for you.

 

[boblarson]

However, your field properties in your new table will have Access default properties. For example, if you have a field for StaffID and your IDs are 6 Characters long, your field property will have 255 Character long property.

ONLY if you have those set as your defaults. There is a place to set them.

This means your Access table is carrying more than necessary load.

You are mistaken there. Access does not pad fields like SQL Server char fields. Setting a default text length of 255 does not mean that it stores 255 characters for each field entered. In fact, it doesn't even mean that the paging does that.

This way, your Access file size will reduce and even make it faster to work with.

Again, you are mistaken as Jet is not affected by the default size you set for text lengths. Setting those ONLY affects how much data you CAN save in the field. It has no effect on size of the database and no effect on the speed or performance. You are running on very OLD information.

Secondly, I will suggest you split your database. Have all your table on one access file and from another access file, link to your tables so as to create queries, forms etc.

Yes, that is something very true.

Sometimes, crashes are due to opening and closing a database too many times. Using splits, this is most unlikely as the table database is opened and closed less of the time.

Well, that is actually a very untrue statement. Where did you come up with that one?

I'm afraid you have what seems to be quite a few "interesting" beliefs regarding Access and unfortunately most of them are simply not valid.

 

[LPurvis]

So... Copy, paste and importing all fail... (I agree that the latter is, by far, preferable).
And yet exporting to Excel works?

Have you attempted linking to that data - rather than accessing the table directly?
Then, at least, you could SELECT INTO and get off and running quicker.
(i.e. your data types would be preserved).

You'll lose indexing though - so you should really be performing any imports you do into a pre-existing copy of your table. (It can't be hard to find one surely - indexes et al intact).

Though I agree that the size of the varChar field isn't relevant as it is a Char field (in either SQL Server or Jet), I feel that it's not the right evenue to even be persuing.
Get your data into a pre-prepared version of your table. Don't be chasing data types.

And, of course, do split.
Give your app every chance of not corrupting. (It's not related to "opening" - establishing a connection is a relatively safe operation).
If you're at all keen on knowing why it would, Albert's article is as good as you'll get.
http://members.shaw.ca/AlbertKallal/Wan/Wans.html


Cheers.

 

[War Horse]

Hello All,
I have attached, two Access 2007 database files.

In Database1, a created a few fields and a record and edited the field size in the table design view. As can be seen, it is 336kb in size.

In Database2, I copied the table in Database1, paste in Excel and then imported it to Database2. I did not alter the default field size of 255. As can be seen its size is 380kb

While my initial post above may be wrong as pointed out above, It will be nice to help AC5FF (The Initial Poster) to see why imports from excel with all of access default field properties can unnecessarily increase the size of his access database.(I will also be learning that too. After all, this is a learning community)

Both Database1 and Database2 contain the same content.

And, for that of Opening as a reason for crashes, my explanation may not have been enough. Opening,closing and deleting objects in Access without compacting and repairing can crash Access. I advice that Access should be set to compact on close. To do this,

1. Click on the "Office button"
2. Click "Access Options"
3. Click on "Current Database"
4. Under Applications Options, check "Compact on Close"

I assume Access 2007.

 

[datAdrenaline]

>> Both Database1 and Database2 contain the same content. <<

Have you Compacted them both? -- they will both shrink. Also, in Database2.accdb the Import specs are consuming some space, those specs are not present in Database1.accdb. If you delete the Import/Export specs from Database2.accdb then Compact and Repair, both databases are identical in size: 299,008 bytes.

It is important to note that a database will not decrease in size when you delete records or objects until the space those objects or records consumed is re-claimed with a Compact & Repair operation.

This phenomenon is true in virtually all database engines, its just that some manage the reclaiming process in a more automated fashion, but --- AFAIK --- the reclaiming of space is not real time in any of the "major" players in the database world.

If you are insterested in the effects of the data types and how they effect the database size check out the following thread/post:
http://www.access-programmers.co.uk/forums/showthread.php?p=724034#post724034