Access External Link and Required Fields - "data truncation error"

[GolfGripsDirect]

Hi guys, I'm currently setting up a new site and rather than the copy-paste-click method i've used to add products in the past I figured i'd try a more scalable solution in using the external linking feature.

I've collated a single table in an access database (in .mdb format) that contains fields for product reference, short description, price and my custom variable 'brand'.

I have no problem with mapping the fields but before the linking finishes I get a 'data truncation error'. The fields are all of the 'text' data type, except price which is listed in pennies as 'number' data type.

I wasn't sure if this was a problem with my table missing required fields or if my data types are incorrect, they are also new products that do not currently exist in my shop. Any assistance would be greatly appreciated.

I'm running version 9.0.5 JHZA creating an mdb format database in Access 2007.

Thanks, Rob

[NormanRouxel]

Sixteen threads await you if you search for data truncation error.

[GolfGripsDirect]

Hi Norman, I understand your keenness to shout search but that was the first thing I did!

After spending the early hours messing around with my records and fields I discovered that actinic will create and externally link to the products in my database (~800) but there are a few specific records that will cause it to spit out the error and stop processing the products after it. I got part of the way through my table removing the records it didn't like and storing them in another table. This allowed me to link the first maybe 200 products but my bed was calling before I could get to the end of the table.

These are the problematic records i've extracted so far. I was hoping they would run over the character limit in the reference field or something equally as obvious and fixable, but I just can't seem to figure out what is making these records not want to play ball.

product_ref Category product_name price act_price Brand
AZ4305 7- Piece Resin Airbrush Set £79.99 7999 Aztek
AZ9352C Tip Set - Assorted £19.99 1999 Aztek
JBX1048/S Various Beadcraft & Jewellery Making Tool set £39.95 3995 Jewel Tool
JDR4001 Drilling Microbox drill set - metric £8.95 895 Jewel Tool
JDR4004 Drilling Microbox drill set - numbers 61 to 80 £8.95 895 Jewel Tool
JDR4005 Drilling Microbox drill set - set of 10 shanked £10.95 1095 Jewel Tool

Formatting tables in forums really isn't so easy so i've attached a screenshot of the records too for clarity.
http://i.imgur.com/ODs4t.jpg

From what I can gather they don't contain illegal characters or run over the character limit (product ref limit is the default 20 chars), can anyone shed some light?

Thanks, Rob

[Mike Hughes]

I wasn't sure if this was a problem with my table missing required fields or if my data types are incorrect, they are also new products that do not currently exist in my shop. Any assistance would be greatly appreciated.

Are you saying you're trying to 'link' to an external database for products that don't exist in actinic? If so, that's not going to work. Actinic needs a product in it's database before it can link to another source for the data / content.

Mike

[leehack]

Is a "/" a valid character for prodref? sounds like a dangerous one if it is.

[GolfGripsDirect]

Is that strictly true Mike? It adds the first ~200 products in my table (up to the first problematic record) and throws them into an 'unallocated linked products' section with the names, references and prices intact despite not existing prior in my actinic database. It's the records specified that aren't playing along!

Thanks for you reply.

[GolfGripsDirect]

Hi Lee, from the help file:

"Your references can contain (- hyphen), (/ forward slash) and (\ back slash). The following characters are invalid ! " ' : _ | % * and should not be used to manually generate or import your product references."

Again the problematic records are in my post above, they seem to be well within the character count constraints and the data types must be okay since the rest of my products get added without an issue.

I'm stumped! Any thoughts?

[leehack]

There's been some chat about valid characters of late and it appears that the info on this is a little sketchy or incomplete. There was also a good thread recently about external linking and the need for one of the first few rows to have a large amount of data so fields were assessed as 'memo' (or something like that). Might be worth a read of that if you can find it, within last 2 weeks i'd say, it's an inherent excel/access bug as i recall. User 'feemish' was in on the chat, so search his posts if nothing else turns it up.

[GolfGripsDirect]

Ok thanks Lee, i'll have a hunt for that topic now.

[Mike Hughes]

Is that strictly true Mike?

No. It's my mistake. You're correct that it puts items not in the database into the unallocated products list.

Mike

PS. Lee's suggestions about allowable characters could be right. It's interesting that all the product titles in your list have a '-' or '&' in them.

[GolfGripsDirect]

PS. Lee's suggestions about allowable characters could be right. It's interesting that all the product titles in your list have a '-' '&' in them.

Yeah i thought the same, I tried the obvious which was to remove the -'s and &'s but it made no difference.

Just found the thread you mentioned thanks lee, looks promising. I'll have to try it out when I get home as i'm at work at the mo. http://community.actinic.com/showpos...6&postcount=29 Linked and quoted for convenience!

If I recal correctly, Its a bug/feature of ODBC. If you can ensure that there is one row in the 1st 8 rows of your spreadsheet with more than 255 characters then the link should work fine. Try adding all your products back in but make sure that the description field of row 1 contains at least 255 characters. (can be row 1,2,3,4,5,6,7,8 but has to be within the 1st 8 rows)

ODBS scans the 1st 8 rows to determine the datatype so if your field contains less it gets formatted as text and not memo.

Thanks for your help guys, i'll let you know how I get on this evening! Rob

[GolfGripsDirect]

Hi guys, i've been thinking about this all day at work. Since I only get the error when the link gets to specific records in my table would that mean my database setup is fine? I would've thought if my problem was the 'ODBC bug' as quoted above then I wouldn't get past the 8th record without the error, however once I remove the product after the last one that was added (the record that caused the error) the next time I create the link it will add the next products up to the next troublesome row.

I have been painstakingly removing each record that breaks the link and inserting them into a table but i'm seeing no correlation between them in terms of what could be causing the truncation error?

Any thoughts? I'm off to continue finding the rest of the troublesome records :S

Cheers, Rob

[leehack]

If it happens on the same product in the same place, I reckon the formatting of that cell in your spreadsheet is different. Select the column and set the format to make sure.

[feemish]

Hi guys, i've been thinking about this all day at work. Since I only get the error when the link gets to specific records in my table would that mean my database setup is fine? I would've thought if my problem was the 'ODBC bug' as quoted above then I wouldn't get past the 8th record without the error

Have you tried putting your largest record in the first line of the database as cren pointed out to me?

[GolfGripsDirect]

Well I finally managed to fix it without having to manually retype the offending fields! I duplicated my product table, changed the data field types (in this unlinked-unrelationshiped table) to something different then changed them back to what they should be. This table linked without any errors and then after copying all of these records into the original product table that worked fine also! It was as though some of the fields hadn't validated upon my initial copy pasting!

Thanks for all the suggestions guys and ultimately steering me in the right direction!

Rob