1

AccountRight Enterprise v19, using ODBCAU1001.

I'd posted this to the MYOB forum before finding out that ODBC questions for my MYOB version should be posted to StackOverflow, so here it is.

I need to clear a few fields in the customer card, but MYOB is not allowing me to do so, and I'm hoping someone is able to show me the correct way.

The way I'm trying to do it is to write to the Import_Customer_Cards table, providing updated data for several customer contact fields. Specifically, I'm trying to clear Address1Phone1, Address1Salutation, and so on, while at the same time overwrite other fields, such as Address1Email.

I'm doing this from Microsoft Access VBA using ADO. The code I'm using is as follows:

strSQL = "INSERT INTO Import_Customer_Cards (CoLastName, Address1Phone1, Address1Phone2, Address1Phone3, Address1Fax, Address1Email, Address1ContactName, Address1Salutation)
VALUES ('Customer Name', '', '123456789', '987654321', '555333222', 'some@email.com', 'Contact Name','')"

cn.BeginTrans
cn.Execute strSQL
cn.CommitTrans

I've been programming against MYOB for many years; I used to be an MYOB developer partner, and I'm very familiar with using MYOB ODBC, however believe it or not, I've never had to clear fields before.

The behaviour I'd expect from the above code is that all fields would be updated as per the VALUE clause. Specifically, I would expect Address1Phone1 and Address1Salutation to be blank, by virtue of the zero-length strings. The observed result is frustrating, though. The other fields successfully update as expected, however, the values in Address1Phone1 and Address1Salutation do not change, regardless of whether they previously held values.

I've tried using zero-length strings for those two fields, and I've tried using nulls, and I've tried using single space characters. Nothing seems to work to clear those two fields. If I use a printable character, say a period ( . ), the fields' values get changed as expected, but I can't bring myself to do such a thing in a production environment.

Am I doing something wrong, or is there a way to do it that I'm just not aware of?

Many thanks for taking a look at this issue.

Regards, Graham

Graham
  • 11
  • 2
  • One idea comes to mind: do you need to have those fields specified within the INSERT at all? Do those 2 fields possibly default to an empty string (AllowZeroLength) or are they required? – Paul T. Jan 30 '19 at 02:25
  • ...another thing: you do say "updated per the values", but the query is an insert. Do you need an update, or is the insert the correct operation? – Paul T. Jan 30 '19 at 02:35
  • Hi Paul. Like any other SQL, the affected columns need to be included. I only include the columns I want to change; that table actually contains many more columns than I've listed. In fact, if we're getting technical about it, the addresses and contacts are actually stored in a completely different table, but MYOB obfuscates it. This is just how MYOB exposes those fields for writing. Yes, the query uses the INSERT keyword, but that's because the ODBC driver does not support the UPDATE keyword. Think of all MYOB inserts as merges. Regards, Graham – Graham Jan 30 '19 at 02:45

0 Answers0