Problem

As I'm working with MS-Access, I'm learning all sorts of things.

I had to write my own SQL because the database management was such that a simple form just did not fit well (i.e. things like multiple unique keys to be checked between MS-Access and a QuickBooks database...)

The SQL is relatively easy to write (there are a few tricks with MS-Access, for instance, dates are written between hash characters: #2009/01/01 00:00:01# and you cannot use all balls or it fails!)

Now, I tried to use the same form to:

1. Create
2. View
3. Edit

a customer. The Create was fairly easy. The View was very easy once I read the data back in (although, the Year to Date sales took me a whole day because of a bug in QODBC...)

The Edit was then causing many problems. I would get many requests by the ODBC system asking me whether I wanted to connect to QuickBooks. Strange because so far it was working great and never had to do that... Then the Save (i.e. the UPDATE statement) would fail pretty much every time (I got it to work ONCE until...)

The MS-Access onscreen message was just saying that the ODBC connection failed in some way. Nothing more. Weird because that was my first UPDATE but I already made the SELECT & the INSERT work, so I could not fathom why it would fail.

The fact is that QODBC writes info in a log file (although it seems that at times it does not...) By looking into that file, I could see that it was not happy saying that the connection was Read-only. Hard to understand why that connection would be Read-only when the table I'm working with is clearly marked as Read/Write and the connection to QuickBooks is definitively Read/Write...

And inserting a new customer (Create) was still working just fine.

Solution

The fact is that when I added the code to generate the Year to Date (YTD) sales amount, I accessed the Invoice table. That table I had it linked before I had the fully valid certificate to run QODBC in Read/Write mode. This means at first I could only have Read-only access. Which was fine while I was just testing and figuring out whether everything could be done, and how it would be done.

When I got the license, I re-linked the Customer table and started to work on that one Read/Write. The Create and View worked fine until I added the YTD, then the View would, once in a while, ask me whether I wanted to connect again and again to the QuickBooks database. Well... That was that.

Then I create the Edit version and that one just did not want to save.

The fact is, since the Invoice table was still in Read-only mode, MS-Access would create a new connection that would be a Read-only connection (opposed to the Read/Write connection used for the Customer table when creating a new customer.) And when the UPDATE instruction was called, MS-Access would reuse the "wrong" connection and tell me that it was Read-only!

I re-linked the Invoice to be a Read/Write connection and since then it works just fine. I can Create, View and Edit and I do not even get all of these extra "Do you want to link to QuickBooks?" messages!

Call it a Bug!

Note that I call this a bug in MS-Access since (1) it should never use a Read-only connection with a table that's marked Read/Write and (2) it should not have to ask you whether you want to connect, over and over again, just because some tables are in Read-only mode and others are in Read/Write.

This being said, it means all the tables you link with must be linked as Read/Write if you need to have write access to even just one table.