We were recently asked by several different users how the QuickBooks® Custom Reporting Tool interacts with Microsoft Excel and Access. We have found the Custom Reporting Tool to be very helpful and the interaction between it and Crystal Reports is great. However, we have noticed that there is an “issue” in the way that the product works with Access and Excel, which is what we want to talk about today.
The QuickBooks® Custom Reporting Tool works off of a File Based DSN. This means that the connection info is stored in a file which is located in the same folder as the QuickBooks® file. This file contains the “name” of the database that is being referenced. The “issue” is that the database name changes every time QuickBooks® is closed and reopened. For a product like Crystal Reports this isn’t an issue, because Crystal goes out to the file DSN and reads the database name every time it establishes a connection to the data. The Microsoft products work a little differently though.
When you connect Excel or Access to QuickBooks® via the Custom Reporting Tool instead of going out and reading the database name every time, it stores the name in the application the first time you establish the connection. The problem with this is that Intuit is changing that “name” every time you close and reopen the QuickBooks® file. This means you have to “re-link” the data connection every time you want to use the Excel/Access file you created. For example, in Access you have to go to Database Tools > Linked Table Manager and refresh the linked table information. While this is not overly difficult, it is a pain.
If Intuit didn’t change the database name OR if Microsoft didn’t hardcode the database name in the connection then we wouldn’t have a problem… BUT the “issue” is that they do. We have been in touch with the developer at Intuit several times about this issue and at this point they have not made any changes. They have informed us that they are looking into the issue, but we don’t know if or when they will make any changes to the way they handle the database name.
Hope this clears things up a bit on why the “issue” occurs, but unfortunately at this time there is no “good” way to make the two products play nicely with one another.