Appending Table (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 12:24
Joined
Oct 30, 2008
Messages
1,083
I needed to change some data types and was told there wasn't enough memory or something.
A google solution was
1. Rename the table to tablename_old
2. Copy and paste it to tablename, using the option design mode only
3. Change the datatype in the new empty table
4. Run an Append query to migrate the data

Sounds ok but when I do that I get a worrying message:
Microsoft Access can't append all the records in the append query.
Microsoft Access set 173 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query click Yes.
For an explanation of the causes of the violations, click Help.

What does that actually mean?? If I run it, it seems to do it fine. Clicking Help doesn't give much of an explanation. When it says "didn't add 0 Records" does that mean it did add them all. Can I see what 173 fields weren't set to null ? Will it matter ?

Alternately there's a reg setting to adjust. Would that be a better solution ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:24
Joined
Oct 29, 2018
Messages
14,041
I think it means some of the existing data don't agree with the new data type you're trying to assign to them.
 

moke123

AWF VIP
Local time
Today, 18:24
Joined
Jan 11, 2013
Messages
2,229
Use copies, keep backups, and see what happens
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
4,464
Do you really have more than 173 fields? Or was this a spread sheet and it appended the blank columns?
 

kirkm

Registered User.
Local time
Tomorrow, 12:24
Joined
Oct 30, 2008
Messages
1,083
I will keep a backup. DBGuy how is that possible ? The data is what it is in the source table, and the new one is a copy?
I also have 2 fields now that are Yes/No in Table View but text in design view. This was after the inital attempt changing data types. Sort of done half of it.
 

kirkm

Registered User.
Local time
Tomorrow, 12:24
Joined
Oct 30, 2008
Messages
1,083
Majp yes It was a spreadsheet and there are > 173 fields. Not necessarily blank though.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:24
Joined
May 21, 2018
Messages
4,464
Obviously that data is not normalized. Are you planning to normalize or why put into a database? That design is not really workable in a database
 

kirkm

Registered User.
Local time
Tomorrow, 12:24
Joined
Oct 30, 2008
Messages
1,083
It's my design and how we want it to work. But that should still have let me append it, I'd have thought.
 

kirkm

Registered User.
Local time
Tomorrow, 12:24
Joined
Oct 30, 2008
Messages
1,083
I ran a check of every field in the original and new table and all was well. :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:24
Joined
Oct 29, 2018
Messages
14,041
DBGuy how is that possible ?
Unfortunately, it happens to me all the time. I get an Excel file regularly with one column labeled "Qty." All the columns get imported into the table as Short Text. But when I try to change the data type of the Qty column to Number, I get the same error as you, because some of the records have numbers like 1 or 5, but some of the records have "NA" or "None" or ZLS in the Qty column.
 

kirkm

Registered User.
Local time
Tomorrow, 12:24
Joined
Oct 30, 2008
Messages
1,083
Right.. but I would have thought appending one Access table to another would be a 1:1 copy and there could be no key violations or type conversion problems (without being apparent/fixed already). Anyway I'm happy with the data now and have deleted the original table after a backup off line.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:24
Joined
Oct 29, 2018
Messages
14,041
Right.. but I would have thought appending one Access table to another would be a 1:1 copy and there could be no key violations or type conversion problems (without being apparent/fixed already). Anyway I'm happy with the data now and have deleted the original table after a backup off line.
Yeah, glad to hear you got it sorted out. Cheers!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:24
Joined
Jan 20, 2009
Messages
12,219
Obviously that data is not normalized. Are you planning to normalize or why put into a database? That design is not really workable in a database
It's my design and how we want it to work.
You should stop and understand why 173 fields is not the way to design your database. I expect you have built it like a spreadsheet.

Designs like this are laborious to build and make querying the data difficult. Next you will probably be trying to find which of a number of fields contains the largest values or adding values in different fields.

The out of memory error you had is just the beginning of your woes.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:24
Joined
Feb 28, 2001
Messages
19,021
@kirkm - of course it is your design, and if you like it, we really have little room to comment.

I think some of us are thinking that in converting to Access from Excel, you probably LOST functionality because you cannot be formulas in cells for Access datasheets - because they aren't cells. If your users were accustomed to that ability, I wonder if they will be so happy with you when they realize those ad-hoc computations aren't available.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:24
Joined
Feb 19, 2002
Messages
31,026
Ditto the normalization issue. I'll leave it at that. No sense in burying you with our disagreement. You will eventually come to realize that you hate Access because it doesn't work like a spreadsheet and you aren't attempting to use it as an RDBMS which is what it is so you are not taking advantage of what it will do for you.

You need to be really careful when you rename objects or column names because the default setting for Access is Change Auto Corrupt (sic) is on by default. ALWAYS turn this setting OFF before you start following directions regarding name changes unless you understand EXACTLY how Name Auto Correct actually works and want to use it. Otherwise, you'll end up with a mess on your hands because behind the scenes, when you change a table name from MyTable to MyTableSave, Access will be helpful and change other objects that reference MyTable to instead reference MyTableSave. So, just be careful and the safe action is to turn off Name Auto Correct.
 

Users who are viewing this thread

Top Bottom