Append Question

[Connor]

I am attempting to append a spreadsheet, however when I try to do it, and error message comes up saying : "set fields to null due to a type conversion failure. Didnt add x rows due to key violation, didnt add x rows because of Lock violation and didnt add x rows due to validation rule violations"
How do I fix this???
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> HELP PLEASE!!!!<v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>

 

[huh?]

Would I be correct in thinking that you are trying to import date from an excel spreadsheet into a table?

If the answer is yes then you have omitted some critical information to help troubleshoot the problem. Specifically, you should rerun your import and pay closer attention to the error message part, "Didnt add <b>x</b> rows due to key violation, didnt add <b>x</b> rows because of Lock violation and didnt add <b>x</b> rows due to validation rule violations". One of those"x"'s is going to be a number and the rest are going to be the number "0".
Where the "x" is a number is where your import is failing.
I've had problems usually with the first "x" being a number. This error is caused either by trying to import the same data twice or having a primary key in the excel sheet repeated in the table that I am importing to.

Hope this helps.

 

[Connor]

yes you are correct. However I only used the "x" variable for the post. There are 56 rows to be updated, however in the error message (the one I posted) where x is put 0. It is strange because I apened several other spreadsheets with no problem, all with the same design.

 

[Connor]

Ok its a type conversion failure that is the problem... how can I or do I fix that????

 

[huh?]

Connor,

For a good explanation of this error, see <b><a href='http://allenbrowne.com/casu-19.html' target="_blank" style="color: blue">HERE</a></b>.

Hope this helps.

 

[Connor]

Connor,

For a good explanation of this error, see HERE.

Hope this helps.

It did help, me for my own knowledge, however the problem continues, this is incredabliy frustrating. Would it help to know im using Access 97 (i dont know why)? Everything is the same yet there is only one table that is not appending!

 

[huh?]

Connor,

The explanation tells you that you have some data in an excel sheet column, that the matching column in the access table doesn't "like". Specifically, access thinks the excel data is the wrong data type. Why? Because there is something unusual in the data like a "$" or a " " or a "," for a column that is some type of number format in access.

Now that you have that explanation, you know where to look. Find your non-text columns in access. You can use the design view of the table to see this. Find the matching column in the excel data. Look in those excel columns for "bad" or "problematic" data.
When you find the offending data, you can hopefully manually "clean" it, i.e. remove the bad characters, if there isn't that much data.

If there is a lot of data you will probably want to clean it automatically. There are 2 ways to do this. The first way is to do it in excel using a macro or vba. The second way is to import your table into a temporary table with all text fields. Then create an append query that typecasts the data from the temp table and adds it into your real table with the permanent data. The previous link shows the commands to typecast. For help on excel macros or vba, search previous posts on the excel message board, then if necessary post your specific problem to that board.

Hope this is helpful.

 

[Connor]

Connor,

The explanation tells you that you have some data in an excel sheet column, that the matching column in the access table doesn't "like". Specifically, access thinks the excel data is the wrong data type. Why? Because there is something unusual in the data like a "$" or a " " or a "," for a column that is some type of number format in access.

Now that you have that explanation, you know where to look. Find your non-text columns in access. You can use the design view of the table to see this. Find the matching column in the excel data. Look in those excel columns for "bad" or "problematic" data.
When you find the offending data, you can hopefully manually "clean" it, i.e. remove the bad characters, if there isn't that much data.

If there is a lot of data you will probably want to clean it automatically. There are 2 ways to do this. The first way is to do it in excel using a macro or vba. The second way is to import your table into a temporary table with all text fields. Then create an append query that typecasts the data from the temp table and adds it into your real table with the permanent data. The previous link shows the commands to typecast. For help on excel macros or vba, search previous posts on the excel message board, then if necessary post your specific problem to that board.

Hope this is helpful.

I have and use the Macros in Excel. All of the fields are the same (text, Currency and date) with the all of the tables in access and excel. (I am appending several tables to make one big list of all of the company contracts) and there is only this one which is giving me toruble.
Thank you for your help and patience.