Questions and Answers

Q: This Solution ID article explains why voiding payable results in out of balance posting if an ODBC Data Source setting has been modified..
A: The following issues will occur in Accounts Payable (AP) in this scenario and are limited to Sage Pro using Microsoft SQL.

This situation occurs when attempting to void a payable and only the Control Account distribution is posted.  No error occurs but an out of balance posting is created without the Expense distributions.

This may be a result of Administrators changing ODBC Data Source settings.  It is strongly suggested to use the default ODBC Data Sources created by Sage Pro without changing the settings. Sage Pro, using Microsoft SQL automatically creates an ODBC Data Source when logging into Sage Pro for the first time. Issues will often occur based on the ODBC Data Source settings while a new company has been created or adding a module to a company (i.e. creating new databases and tables).

To correct this issue if encountered:

1. Identify which users have problem ODBC settings.  Most likely this can be achieved by looking at SYCCOMP.ADDUSER or SYCDATA.ADDUSER for the Sage Pro login.
   Important
   The user will most likely be a SQL Administrator since settings within the ODBC for SQL require login and password.
   
2. Correct their ODBC settings by comparing to a default one created by Sage Pro.  This could also be done by renaming the incorrect ODBC settings and then logging into Sage Pro.  This will create a new default ODBC Data Source. To go into the ODBC settings:
   1. Click Start menu, point to Settings, and then click on Control Panel.
   2. Double-click Administrative Tools,
   3. Double-click Data Sources (ODBC).
3. Create a new company in Sage Pro from a workstation with a correct (default) ODBC.
4. Copy all information from the problem company when prompted.
5. Add the modules to the company and copy all data from the problem company when prompted.  Tests have confirmed this will at least correct the voiding issue in Accounts Payable.

Important
Exporting and importing the problem company will not correct the issue.

To recreate this issue:

1. Verify when logging on the workstation that the user has administrative rights and the SQL Server to be used.
2. Perform a new install of Sage Pro using Microsoft SQL.  Provide a unique ODBC Data Source name when prompted during the SQL portion of the installation.
3. Exit Sage Pro.
4. From the Windows operating system, access the Control Panel.
5. In the Control Panel, click on Administrative Tools, then click on Data Sources (ODBC).
6. Select the ODBC Data Source that was just created.
7. On the first screen, click on the Configure button.
8. Click on the Next button.
9. Enter the Administrative login and password on the second screen.
10. Click Next.
11. On the third screen, uncheck the setting Use ANSI nulls, padding, and
    warnings.
12. Click Next.
13. Click Finish.
14. Click OK.
15. Create a new company in Sage Pro.
16. Add Accounts Payable as the only module and link it to Company 99 General Ledger.
17. When prompted select the option to copy data from Company 99 Accounts Payable 99.
18. When complete, enter a payable in the new company and then try to void it.  This will create an out of balance in the Accounts Payable.

© Copyright Sage Software