Knowledge Base - Check out the latest updates, documentation and white papers on MYOB.
ODBC Test Connection Issues (ODBC 6 and 7)
1. The ODBC test connection application will not list System DSN's in the dropdown combo box. However System DSN's are preferred to User DSN's, and they work fine. For testing purposes however you need to set up USER DSN's
2. The ODBC Test Connection application gives a false error message when trying to test and ADO connection with no KEY. No key is required for read only access, therefore would not be entered. The Test connection returns "ERROR 20097 - Registration Key file not found or corrupt" which indicates a missing or invalid key. The connection still works with applications.
3. Windows ME and ODBC drivers 6 and 7 are not compatible.
4. Windows 2000 will only work with DSN's and not ADO in the tests we have done
Using FoxPro and MYOB ODBC
How to set up FoxPro to use the Clearwater ODBC connection.
Download - FOXPRO AND MYOB ODBC
Using Excel and AURORA ODBC
How to set up FoxPro ODBC with Excel to see Aurora Tables.
Download - FOXPRO ODBC and AURORA
ODBC ERROR - "Unable to determine if Import has finished"
The error message 200016 "Unable to determine if Import has finished" is generally caused by ODBC losing the connection to the MYOB Premier application. It is for this reason that an .imp file remains, as it is at this point that the connection is interrupted.
1. error "Unable to determine if import has finished" is being generated due to restricted access to the Win SENDMESSAGE API.
Is the network/domain account the service is using (to access the data file) a member of the Local Machines Administrator group?
As both the MYOB application and your service application are residing on the workstation, it is here that the communication between MYOB and the ODBC driver is taking place. The error message and behaviour indicates that the network/domain user does not have sufficient privileges to access and use this windows API on the local client machine.
To explain; When the ODBC driver sends a message to the MYOB process to perform the import using SENDMESSAGE, the driver must wait for a success (error code 0) or error (any other code) to move forward. The presence of the IMP file after the driver �times out� and returns this error indicates the response message is not being received by the ODBC driver. The inability to do anything other then wait is default behaviour of this API and cannot be altered, the timeout and error received is the best that can be achieved.
I understand why you cannot use the local system account of the client workstation (and receive company file does not exist when attempted) however should not receive this when administrative privledge is given to the account the service is running under to the local machine.
Having said this, dependant on the overall design of the your system (multi-user/single-user etc) this may not be the best way to achieve the most efficient and stable integration with MYOB.
2. This error is generally caused by overloaded network and/or computer resources. Error can be intermittent.
3. I was getting this error in a Sales Import routine after about 30 or 40 records were imported. I originally opened the connection to MYOB, and held it open while the program looped through the records for import into MYOB. A code change to open the connection before each insert and then close the connection made the problem go away. A subsequent import of 1000 records went perfectly. Envrionment was Windows Terminal Server 2003, MYOB Premier 4, ODBC 6.00.14
4.The error "Unable to determine if import is finished" will appear if the driver does not receive any communication from the MYOB application to advise that the insert was successful. As a result, the driver remains in a 'wait' state and eventually times out. There are two scenarios which will determine the cause of the error. If this error is received sporadically, then it will be caused by overloaded or insufficient system resources, reducing the system performance, causing the message not to be received in time. If the error is occurring consistently, where it has never operated successfully, then the error is due to privileges not allowing the MYOB application to access the Win32 API to send a message.
5. VFP Application
The application was a compiled VFP9 exe registered as a com server. It was working when originally installed, but then stopped. There was a security / permissions issue somewhere. It turned out that the Web developer calling the com server had set it up in DCom to always run with the USER (me) who had sufficient rights to access the correct folders etc. This meant that the .net app running in Internet Explorer would run with the appropriate permissions. I unregistered the app using xxx.exe /Unregserver then deleted the DCOM entry form Administrative Tools->Componenet Services and then registered the new build. Everything has worked well again using my login. The .net application developer comes in again tomorrow. so we will need to get his required permissions set up correctly.
For those interested... the idea is to have a com server ( my contribution being a foxpro com server exe) handling the extract from SQL Server 2005 front end and adding \ updating customers and invoices into myob, and then updating the SQL database with customers financial status. The com server can then be triggered from a SQL Server Stored Procedure which passes a parameter. The parameter being an invoice number or a file containing a list of invoice numbers. Alternatively a .net web app can call the com object. It all works very well and means that the import to MYOB is fired directly from other party front end completely transaparently to the user each time an invoice is saved. An ideal marriage with each of us doing what we do best....
For those interested in speed. The Premier 12 ( with patch applied) data file has been running for 4 months, is 69 meg, has 31000 customers. Using a new Win 2003 server running Terminal Services, writing 1350 invoices took about 10 minutes. Each invoice is run as a separate post. IE: comitted to myob after each invoice. writing the 31000 customers took over 3 hours on my laptop, but 16 minutes on the Terminal Server.
ODBC ERROR - ERROR [HZ080] [MYOB ODBC] - Unable to launch MYOB - Database specified in the connection string is already opened exclusively by another application.
Accessing the MYOB file with the ODBC Driver pointing to C:\ of the local system and the PC was accessing it remotely. As soon as the ODBC Driver pointed to the mapped path all worked.
Peer to Peer networks;
The driver will now allow connection to an MYOB datafile opened from a network location regardless of the other users that maybe open in the same file from another workstation, and will allow simultaneous ODBC connections to the datafile from different machines without the need to open MYOB first.
Terminal Services networks;
On a terminal services network there is one pre-requisite. The file location must be referenced from a network location in order to allow the connection regardless of other MYOB or ODBC connections.
E.g. Rather then reference the file from C:\Premier9\Clearwtr.MYO
Share the folder and reference the file as \\ComputerName\ShareName\Clearwtr.MYO (UNC path) or
Z:\Clearwtr.MYO (mapped drive to share)
To explain;
Within the driver there are checks performed upon connection to the MYOB datafile. One of them is to check that the file is not opened for single user access. At the time connection is executed, the driver checks the file state and then if it is in use, checks for an MYOBP.EXE (Premier) or MYOB.EXE process that has a handle on the datafile. In a network situation, the process that has the file open will usually be running on a different workstation or in the case of Terminal Services, another server session and as such cannot be read or detected by the session or workstation the ODBC connection is being executed from. The inability to detect another terminal server sessions process’s is a part of the server operating system security by design. In this new driver build (5.00.00.06), the check for file exclusivity has been bypassed for any file opened from a network location. This means that in order to ensure the connection is successful regardless of other users or connections, the database path needs to be a network path. It is possible to connect to a file via a network share even though the file is on the local drive and these local shares can be mapped to a drive letter.
Understanding the ODBC Driver
AccountRight uses a C-Tree database in the backend, which is a flat file. The ODBC Driver reads this information and represents it as the Read tables, hence it is not essential to have AccountRight application installed if doing a read only connection. To write information to the database, the ODBC Driver uses the import routines in the AccountRight user interface (File>Import data). Thus the write tables are a virtualization of the import routines. Therefore all the tables visible via the driver are virtual i.e. they do not exist in the database. In order to write to a datafile the ODBC Driver uses the MYOB Process (myobp.exe or myob.exe). During the import, ODBC Driver create .imp file which will be used by the MYOB import routine to import data into the datafile.
When MYOB ODBC driver is trying to establish a read-write connection to the AccountRight data file, it will first look to see whether the myobp.exe process is running, if MYOB Premier is open on the computer connecting then the MYOB ODBC, instead of launching its own MYOB process it will use the one currently running. If however the MYOB ODBC is connecting without MYOB Premier being open on this terminal, then it will launch its own myobp.exe instance.
Within the driver there are checks performed upon connection to the AccountRight data file. One of them is to check that the file is not opened for single user access. At the time connection is executed, the driver checks the file state and then if it is in use, checks for a myobp.exe (Premier/Enterprise) or myob.exe (Standard/Plus) process that has a handle on the data file. In a network situation, the process that has the file open will usually be running on a different workstation or in the case of terminal server session and as such cannot be read or detected by the session, the ODBC connection is being executed from.
Terminal Server environments are subject to this error for a slightly different reason. When Terminal Services is enabled on a server operating system, a security “template” is applied to the server and even though all users are logged into the same physical server, User1’s session cannot see the processes running in User2’s session. This means that if the file is open in a terminal services session, an ODBC connection attempted may result in the error message because the ODBC driver cannot find the process using the data file.
Points to keep in mind when troubleshooting
- All users are using the same network protocol (TCP/IP or NETBIOS).
- Please try deleting all lock files located in the datafile directory. Lock files have the extension .flk
- Using a mapped drive or UNC Path for the datafile e.g. \\Computername\Enterprise19\Clearwtr.MYO
- Ensure AccountRight and Add-on Solution is running in administrator mode. (Right Click and Run as Administrator in Vista/Win7)
- Using the ODBC test connection utility provided, check if it returns with the same error message.
- Try incorporating the key value within the connection string rather than specifying the .key file path.
- Ensure able to recreate the error when logged in as administrator on the server or workstation
- Does manually launching the myob.exe resolve’s the problem?
