MYOB ODBC Error #7495

Unexpected internal c-tree(R) error #7495 (uerr_cod=0)

That’s the error message I was getting one day. Nothing major happened, the update routines just stopped working on day!

I’m importing a bunch of data from the usual tables (CustomLists, Customers, Items, Sales) in to the SQL Server back end for faster processing against the company’s CMS. I do this because of the way MYOB has done it’s ODBC driver, every time you do a query, all the table records are read in to memory and THEN the filters are applied, it’s horrendous. Especially if, like this company, you have upwards of 100,000 sales lines. So anyway, it happily dumps the data into the SQL Server tables (using MS Access 2007 as an intermediary) without issue.

The error occurs when I start to insert new records in to the MYOB file.

I’ve tried using the same connection (Reading from a RW connection). I’ve tried using a two connections (Reading from a RO connection and writing to a RW connection); to no avail.

The solution?

ONLY read from or write to the MYOB file while the connection is open. I have taken this to the extreme and put a switch in at the start of the run; if it’s 4am – write any new sales to the MYOB file; if not – read all the MYOB details and update the CMS accordingly.

The error is gone and the work flow is easy to understand for the staff:

• Each morning new invoices should be in MYOB and;
• During work hours the payment details entered in MYOB will be reflected in the CMS every hour.

Obviously the memory handler for the underlying database couldn’t deal with the large number of rows being returned.

Membership System

The association needed to replace it’s outgrown membership database without losing any data or functionality. Weekly member lists are generated to provide simple forms for treasurers to record payments as they are received. Member statements, which include tax exempt notifications and customisable reminders, can be generated as required. Summary reports enable the executive to monitor the status of their constituency without getting bogged down in details.

The solution needed to replace and expand upon the existing system that had reached it’s user limit. All the existing functions were replaced/recreated before creating additional functionality. A new series of detailed financial reports allow for reconciliation and fast data entry into the accounting package; instead of entering individual transactions for each member the client is now able to make summary transactions for the total amounts to be allocated to their different destination accounts. A date driven approach has allowed for continued expansion of payment methods, specifically direct payment via internet banking, without compromising the traditional collection process.

The solution is complete, occasional development of new functions is undertaken when the clients business model changes. In fact, the system has been so popular it’s now being used by quite a few affiliated associations in the south west of Western Australia and Tasmania.

This solution uses:

• Microsoft Access 2007

Recruitment Database

Global Health Source aims to provide their clients with a short list of high quality candidates for review, ideally within a few days. Candidate details, such as primary professions, skills and their proficiencies, accreditations, and visa history are all recorded and instantly searchable. Client details, including candidates presented for interview and their outcomes are also recorded to get a complete picture of their success rates over time.

Supplying allied health professionals to locations in Australia and New Zealand. The database tracks members from all over the world (Australia, Ireland, New Zealand, South Africa and the United Kingdom to begin with); their current qualifications; future plans; visa arrangements; and local accreditation progress. Placement officers are constantly in contact with candidates and clients throughout the day so data entry must remain a simple process. The search form allows for very detailed criteria so that the least number of enquiries can be made while finding candidates who are available, and interested, in the position.

This system has recently been migrated from an Access 2007 back end over to SQL Server 2008, the process was done in-situ over a period of weeks (and while it was being used in a live environment!). A more detailed search function is being investigated, along with increasing the list of details being stored for clients and candidates.

Technology used

• Microsoft SQL Server
• Microsoft Access 2007

A form with a view

The full data conversion from MS Access to MS SQL Server last Friday night has been very successful! Not only was there no data loss, but very few changes were required to the interface system to keep it working through the weekend.

Now the final chapter of the full conversion begins: creating views for all the long process queries that used to take minutes to return results.

I have already begun the process, starting with the most troublesome queries that are regularly used. The idea being that the most user time will be saved by optimising them first. The pay off is huge for a small amount of work. As the list to be converted shortens management can decide if they want to continue with the process.

I will also be using triggers to add some proper auditing of various tables (those related directly to cash value transactions mostly) which is sewhat of a worry as the company is going to be able to see just how often records are changed by their staff. All in the line of performing their jobs, of course, but through full auditing analysis of the “surface area” of the various systems can be done and I can begin to reduce it to as small a size as possible.

SQL Server conversion

I’ve been having great success with converting some of my split (front-end/back-end) MS Access 2007 databases to an SQL Server back-end.

The upgrade wizard was only so helpful, I had to turn off all the extra options to get the many years of data into the SQL Server. It has become clear to me that over time the data has had various levels of ‘goodness’ and the integrity rules have been quite open to interpretation, or sometimes just automatically turned off when the situation demands it (compact database function).

What the conversion process has allowed me to do is revisit all the data constraints for all the tables and make sure they correspond to the new system’s requirements. I have had to replace quite a few null values, but the overall effect has been to really focus in on how the system has been used in the past. In spite of the way it was designed to be used.

I am also looking forward to having a much less invasive auditing mechanism through the use of triggers. The old method relied heavily on calling the right sub-routines at the interface level which is not the most ideal solution, but certainly provided a level of functionalty that worked.