INSERT not working

  • Thread starter Thread starter Daniel Magnus Bennét Björck
  • Start date Start date
D

Daniel Magnus Bennét Björck

Hi!

I have the strangest thing (Access 2000).

First I create a table with a Make Table query like this:
SELECT [Filtered: Accounts].* INTO [Imported: Accounts]
FROM [Filtered: Accounts];

That works fine. Only one of the columns is too large, 50 characters instead
of 3, which is the maximum length of the data given in that column. (Does
anyone know how Access works out the length of the columns in a Make Table
query? Some columns are correct, whereas others are either 50 or even 255
when the length of the data actually is just 1 or 3).

So I change the column to be only 3 characters long, delete all the records,
and then try to reinsert the data using:
INSERT INTO [Imported: Accounts]
SELECT [Filtered: Accounts].*
FROM [Filtered: Accounts];


But then I get the "Can't append all the records" message, saying that it is
due to validation rule violations. None of the records are imported. There
are no validations or indexes (since the table was originally created with a
Make Table query). If I change the length back to 50 as it was originally
when it worked, I still get the same message and no records imported!!

What incredibly silly and stupid thing am I missing?

Brgds

Danny
 
Danny

A make table query wipes out the old table and starts over. An append query
adds records to an existing table.

Access has an option (Tools|Options) that lets you set the default field
size -- it comes preset for text fields at 50 characters. BUT! ... Access
only stores as many characters as it has, and doesn't "pad out" a field that
holds 3 characters but is sized for (up to) 50. So there's no problem with
Access deciding, since you have text, to use 50 characters.
 
Hi!

Are you sure it doesn't pad, at least in Access 97? I have the solution
running at an A97 installation where I noticed a very strange effect, which
is why I'm looking at this issue now. It imports data from another database,
which is just 170 MB. It does a straight copy with Make Table (the supplied
SQL) with no calculations or other additions. Yet the database I imported to
ended up in 1GB, after which Access stopped and said that was the maximum
size. When I deleted the table and compacted the database, it got down to
26MB. So I thought perhaps padding was causing it since there are several
columns that only contain 1 character, yet were sized at 255.

Brgds

Danny
 
Daniel

Adding and deleting rows/tables (sounds like a Make Table to me) results in
"bloat". Storing images in Access tables results in "bloat".

What happens if you keep the table, designed as you wish, delete all rows,
then append your new data?
 
Hi!

Yes, that's exactly what I wanted to do, but please refer to my original
post. It doesn't insert the data, but complains of validation rule
violations even though there are no validations or indexes. That's what I
wanted help with from the start.

Brgds

Danny
 
Daniel

There are (implicit) validation rules concerning data types. There's also
the possibility of corruption. If I were working on a problem like this,
I'd first throw out the table and create a new one, then try
appending/updating one field at a time until I either was completely
successful or hit a field that it choked on.
 
Hi!

I have found the problem now by testing each column. It was a text field
which didn't allow empty strings, but they were all empty in the source.
Apparently that works fine in a Make Table query, but not Append.

As to your statement that the length of the text fields don't matter, I've
tested it. After importing a small table with the original long text fields,
the database grew 1.7MB. After changing the length of the text fields to the
correct shorter lengths, it only grew 1.4MB.

Thanks for your help.

Brgds

Danny
 
Back
Top