MYOB ODBC Import Customer - address details not importing

Question

I am using the MYOB ODBC Driver to insert new customer records into MYOB (AccountRight Premier V19) from a .NET application but the records are not being imported correctly - specifically the address part of the insert is not working correctly and that then appears to impact on the location of all fields after those address fields. Everything seems to be shifted back one column.

Here is an example SQL statement as generated by the application:

Insert Into Import_Customer_Cards (CoLastName, CardStatus, CurrencyCode, Address1AddressLine1, Address1City, Address1State, Address1PostCode, Address1Country, Address1Phone1, Address1Phone2, Address1Phone3, Address1Fax, Address1Email, Address1Website, Address1ContactName, Address1Salutation, ABN) VALUES ('1 AAA TEST', 'N', 'AUD', '116 My Street', 'My Suburb', 'QLD', '4000', 'Australia', '31033383', '', '', '', '', '', 'This Bloke', '', '12345678910')

The value of '116 My Street' is NOT being imported and all subsequent fields are moving "up" one column so that the city winds up in the Address1StreetLine4 column, The state winds up in the city column etc. within MYOB itself.

Also, the phone numbers and ABN fields just disappear! I cannot find them in MYOB anywhere in the customer record after the import has completed.

I have checked the MYOB error log file and there is nothing there to suggest something major has gone wrong.

I have tried everything I know to try and get this to work but I am now stumped.

Does anyone here have any idea at all as to what might be causing this?

My guess is that these address fields need some sort of "special" formatting. Am I close?

Answer 1

1. v19 address lines (1 to 4) are presented as if they are separate fields, but actually the driver maps them to a single field. One thing you could try is include Address1AddressLine[2..4] in your SQL statement, setting each to empty string. Be careful not to exceed 255 characters for all four (noted you're in no danger of that here).
2. Do not try to import a currency code unless you are sure your flavour of v19 supports currency settings. Strange things can happen if you try to import values that are not recognised by a specific version of v19.
3. The v19 ODBC driver actually invokes the same import/export routines exposed in the UI under the file menu. If you're not sure whether you're setting values correctly, try importing them using the UI (to do this take your import data and turn it into a csv). You will get better error responses.

Answer 2

It turns out this is a bug in the MYOB ODBC driver.

Rather than persevere with trying to find a resolution, we are upgrading to the latest version of MYOB so we can use the new API procedures and do away with the ODBC layer altogether.

This would appear to be the quickest and most reliable solution.