| Multi User MS Access Application |
| Author |
Message |
| cwz |
| |
| Group | : Members |
| Posts | : 14 |
| Joined | : 2009-08-19 |
|
| Profile |
|
| Posted : 2009-08-19 09:02:05 |
Hi,
I’m starting a project that requires merging data from QuickBooks with some data entered manually. There is also a requirement to add and update items in QuickBooks. As many as 5 people could be using the data entry side of the application at a time. I was thinking about using MS Access to store the manually entered data with some QODBC linked tables to get the related info from QuickBooks. My development environment is on a PC running Windows XP, Access 2003, QuickBooks Enterprise Solutions - Accountant Edition 9.0 and the evaluation copy of QODBC ver. 9.00.00.261. The production environment will be on a Windows server.
The problem is when I use a linked table in Access then try to start QuickBooks I get an error “An Integrated Application is currently sharing data with QuickBooks. Please wait until the application is finished and try again”. If I’m logged into QuickBooks, use a linked table then try to shut QuickBooks down before I exit Access I get the error “The company file cannot be closed at this time because there is another application (FLEXquarters QODBC) which is using it. Before you can close the company file you must either close that application or instruct the application to end it’s session with QuickBooks”. Neither of these scenarios is acceptable in a multi user environment. I’ve tried all configuration QODBC/QuickBooks options I can think of to eliminate these errors with no luck.
The question is, am I using the right tools for this new application? Will these problems be solved by using the server version of QODBC? I’ve read some scary posts about QODBC (difficulty updating data, multi user problems, performance issues), is it a viable tool for a muti user, read/write QuickBooks application? Is there a read/write, evaluation version of QODBC for servers so I don’t have to buy it to find out there are insurmountable problems?
Thanks in advance for your help! |
|
|
|
|
|
| Posted : 2009-08-19 10:45:40 |
When using unattended mode access to QuickBooks (as in How do I setup QODBC to auto-login into QuickBooks and start it if it's not running? ) you need to check the "Launch QuickBooks UI" option if you want to use QuickBooks at the same time.
Likewise you can't close QuickBooks if there's still a LIVE Integrated Application connection. You will need to close MS Acess first. (That's exit MS Access not just close the project/application you're running).
Each copy of QuickBooks however must be logged in using different user names. The issue here has nothing to do with QODBC, it's the QuickBooks Integrated Applications Certificate that's getting in the way here. Each Integrated Applications Certificate in a company file can only handle one application, so you have to force QuickBooks to see your application at each workstation as a different application.
See: QODBC Multi-user setup for more information. |
|
|
|
| cwz |
| |
| Group | : Members |
| Posts | : 14 |
| Joined | : 2009-08-19 |
|
| Profile |
|
| Posted : 2009-08-20 00:29:57 |
If I set "Integrated Application Default - Launch QuickBooks UI" to on and each user has a unique login I won't lock up QB? If so that would be an acceptable solution. Unlike Vlad, all my users have QuickBooks. I set up a user in QB just for Access/QODBC. Is this required if QB is always running?
I'm new to QB - has there been any talk of offering a version of QuickBooks that runs on SQL Server or Oracle? |
|
|
|
|
|
| Posted : 2009-08-20 14:30:23 |
| QuickBooks already uses SQL Anywhere as its backend. Each QuickBooks user needs their own QuickBooks login account. If QODBC uses the same QuickBooks login account on all workstations, then only one connection will be able to work at the one time. QODBC should be setup to use the QuickBooks user account on every workstation. |
|
|
|
| cwz |
| |
| Group | : Members |
| Posts | : 14 |
| Joined | : 2009-08-19 |
|
| Profile |
|
| Posted : 2009-09-30 05:39:13 |
| I'm ready to implement my Access application and I'm getting odbc errors with multiple users, most everything works fine if a single user is active. All users have QB 9.0 and the latest version of QODBC on their desktops. My Access database sits on a server with the QB company file and connects via linked ODBC tables using a DSN called QBTest. When I set up QODBC on the second desktop I expected to get a request to create a new IA certificate in QB but that didn't happen. Apparently both desktops are being recognized as using the same application so I'm not able to set up an IA certificate for each user. If this is correct I don't see a way to set up Access as a multiuser application. Can you help me understand how to set up QODBC to use the QuickBooks user account on every workstation? |
|
|
|
|
|
| Posted : 2009-09-30 11:10:57 |
| This is a known issue as discussed at great length in: QODBC Multi-user setup. The problem has not yet been rectified by FLEXquarters.com Limited in Hong Kong. |
|
|
|
| cwz |
| |
| Group | : Members |
| Posts | : 14 |
| Joined | : 2009-08-19 |
|
| Profile |
|
| Posted : 2009-09-30 22:26:38 |
| If it's a known issue why didn't you just say QODBC will not work with a multi-user Access database instead of refering me to a thread that proposes a solution? This is the reason I asked this question 6 weeks ago before I started development. |
|
|
|
|
|
| Posted : 2009-10-01 22:39:59 |
My reply to your post on : 2009-08-19 09:02:05 on: 2009-08-19 10:45:40, only 1hr 43 mins later, clearly states each copy of QuickBooks however must be logged in using different user names. The issue here has nothing to do with QODBC, it's the QuickBooks Integrated Applications Certificate that's getting in the way here. Each Integrated Applications Certificate in a company file can only handle one application, so you have to force QuickBooks to see your application at each workstation as a different application.
This is normally done be changing the name of the executable. |
|
|
|
| cwz |
| |
| Group | : Members |
| Posts | : 14 |
| Joined | : 2009-08-19 |
|
| Profile |
|
| Posted : 2009-10-02 01:26:44 |
| OK, so are you saying there is a solution? Can you explain how to do it? Do you have any customers using Access in the way I've described the application? |
|
|
|
|
|
| Posted : 2009-10-02 12:57:09 |
The simplest method is, for example, create invoices in Access and then use just one workstation (or the server) to run a Access program script that loops around every five minutes and writes the new invoices into QuickBooks and copies any new data from QuickBooks into Access tables that mirror the QuickBooks tables.
See: Can I use DoCmd.TransferDatabase command in MS Access? for the easiest method to copy tables from QuickBooks. |
|
|
|
