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.
We recently had a situation where we needed to combine three QuickBooks® company files into one single file. Overall this wasn’t extremely difficult and we had a plan in place to take care of all the tasks at hand; that was until we got a look at the list of memorized transactions that the client wanted to include.
QuickBooks® allows you to export most of the available lists, but the Memorized Transactions list is not one of them. Fortunately there are a couple ways to get your Memorized Transactions from one file to another.
The first way that you can accomplish this is to remove all transactional data from a copy of your file and once complete use this as the base for your new file. The second way to handle this issue is to run all your memorized transactions with a date in the future. Then use a tool such as the Data Transfer Utility by Karl Irvin to transfer those transactions to your new file. Once they have been copied you can then memorize them and you will now have a new Memorized Transactions list.
If neither of these works for you then you have one option left, and that is to manually recreate all of your memorized transactions. If you would like to use one of the methods above and need assistance please feel free to contact us, we’d be happy to help.
Question: We just added a new employee into QuickBooks® last week and I am trying to run payroll but the new employee isn’t showing up. I have verified all the employee’s data and I can’t see why they aren’t showing up. The hire date is 2/22, their earnings are filled in and the taxes are all setup, but I still can’t run 2/28 payroll for this one employee. Why?
Answer: I began to review an employee record and scan for a field that might just cause such a problem. As I completed the question, “What do you have in as the Payroll Schedule”, the customer stated that she fixed it. I asked what the problem was and she said, “I didn’t have a Payroll Schedule entered.”
In this case it really wasn’t a problem with the software it was a simple oversight by the user. But how often is that the case? We often overlook something during set up no matter how hard we try to be vigilant. This post is more of a reminder to always take your time and go slow whenever setting up anything within QuickBooks®. The problems that improper setup can cause are not always as noticeable as “I can’t pay my employee”. If you setup an item incorrectly you could cause activity in incorrect accounts which could go unnoticed for some time. Once you’ve realized there is a problem it could take a while to determine the source of the issue, and in the mean time you could be having problems at all sorts of levels.
So the lesson here is take your time, fill in all the data, then double check, and triple check your information.
I have not been a huge fan of the QuickBooks® registration process ever since version 10, when it became necessary to use an Intuit log in to register the product. As Intuit Solution Providers we are able to order product for our clients and have it pre-registered, which means we get the validation codes with our orders. With the Intuit login method this doesn’t really do much good as the client must still create an Intuit user name and password, which they might never use again. However, there is a way around this process. When you open QuickBooks®, click on Help and then About Intuit QuickBooks®… With the Help window open click Ctrl+R+P and the old registration window will appear, you can then enter your validation code. This is a much easier way to get the process done when you have clients who have never created an Intuit user name or if they do not remember the information.
Recently we received a call from a client letting us know that they had gotten a new computer and installed their copy of QuickBooks® on it. After 30 days it was now asking them to register the product before they could continue to use it. The individual whose login was used for the original registration was not in the office at the time, and there was work to be done. We had saved the product registration number on the license key label, so we were able to use the method above to get the product registered and keep the client running with no lost time.
It’s little tips and tricks like these that are nice to have tucked away for occasions such as the one above.