Cannot open SQL server

Problems printing non-datapiped reports in Accpac Advantage Series 5.2

The following error is returned:

Description: Cannot open SQL server

All databases in 5.2 use DSNs.  The problem occurs when multiple database types are used and the same database IDs' exist.

Example.  A Saminc and a Samsys DSN already exist on a machine from a DB2 install.  When the client accesses a Saminc database for Pervasive, a  Saminc and a Samsys DSN will be created for Pervasive on the same machine.  This creates duplicate DSNs on that machine and the above error will occur.

Remove the duplicate DSNs from Datasource (ODBC) setup.  If the database does exist with the same name for both types of databases, ie.  Pervasive and DB2, then use DBDump and DBLoad for one of them to a different database ID.

Error "A Strong SA Password is Required for Security Reasons" When Trying to Install MSDE

When trying to install MSDE included with Sage Accpac HRMS, the following error may occur:

"A strong SA password is required for security reasons. Please use SAPWD switch to supply the same. Refer to readme for more details. Setup will no exit."

The installation then terminates.This error normally occurs on Windows Server editions, but may also occur on Windows XP.

Follow the steps below to setup MSDE manually:

1. From the Start Menu, select Run...
2. In the Run dialog box, type in "cmd" and click OK.
3. Complete the following commands, pressing the Enter key after each command:

• "x:" (where x is the CD Drive where the HRMS CD is inserted)
• "cd\msde2000"
• "setup SAPWD="password" INSTANCENAME="name" SECURITYMODE=SQL (where password is the password you choose and name is the identifier which you choose to call the MSDE installation) (NOTE: the password must be a strong password, meaning it must contain both numbers and letters).

During the Accpac database setup, the login information will be as follows (unspecified fields can be left with their default values):

Server Name: \
User Name: sa
Password:

"Violation of PRIMARY KEY constraint 'ICUNIT_KEY_0'" when performing a DBload of data into MS-SQL

When importing data through DBLoad into a new Microsoft SQL database, the following error may  occur:

INSERT (error = 127, native code = 2627) message: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation  of PRIMARY KEY constraint 'ICUNIT_KEY_0' Cannot insert duplicate key in object 'ICUNIT'

This  error is not evident performing the same procedure in an environment running another database engine such as Pervasive. This problem may be related to an incorrectly defined Sort Order or Collation Name in the setup of the Microsoft SQL database.

To correct this problem, do the following:

Set Sort order to Case Insensitive
Set collation name to Latin_general_bin

Pop-up Calculator

When you are in any number field, you can access the calculator by pressing the + key in the numeric keyboard or by pressing Shift + = (shortcut).

You copy and paste your result directly to the number field using Ctrl + C (copy) and Ctrl + V (paste). If your calculator has a paste button, you can use it to paste the result directly to the number field without having to copy the result first.

Error Code: 2147467259 - Automation Error - Unspecified Error

If you receive the following error message:

"Error code: 2147467259 Automation error - Unspecified error."

This may be due to restrictions on the temporary directory paths that are set in the Environment Variables on the local workstation. Change the path to a shared temp directory such as C:\Windows\Temp.

To do this, do the following:

1. Before proceeding, please close out of all ACCPAC® programs.
2. Click the Start button, point to Settings and select Control Panel (in Windows XP, skip the Settings step)
3. Double click to open the System icon
4. In the System Properties windows, click the Advanced tab
5. Click the Environment Variables button
6. In the User variables for [username] field, highlight the TEMP variable in the list and click the Edit button
7. In the Edit User Variable window, change the Variable Value field to C:\Windows\Temp

Repeat the above steps for the TMP variable as well. If you want to retain the existing variable values, paste the current variables into notepad or other text file and save this file in a location where you can retrieve it at a later date if necessary.

Restart ACCPAC.

Shortcuts and Function Keys

Listed below are some Function Keys and Shortcuts that you can use in Sage Accpac ERP.

Using Function Keys - The following function keys or “Hot Keys” provide you with shortcuts:

F1 Key - Displays online help. Available anywhere in ACCPAC.
F5 Key - Displays a finder window to select records from.
F7 Key - Displays the information for a record (go button).
F9 Key - Enlarge the selected record to display more information about the record (zoom Button).

Using Menu Commands & Buttons:
Data entry forms display similar menus (for example, file and help menus) Thus you need to become familiar with only a few commands and buttons to work with ACCPAC data entry forms.
Advantage Series: Using Keyboard Functions instead of the Mouse:

Alt + S - Use to save changes made to the displayed record.
Alt + D - Use to delete the displayed records.
Page Up - Navigation buttons
Page Down - Navigation buttons
Ctrl + Page Up - Navigation buttons
Ctrl + Page Down - Navigation buttons
Alt + E - Use to set criteria to specify records to display in the finder.

Editing Columns of Data:

Insert - insert a blank row to add a new record.
Delete - remove a selected row.
Tab or Enter - Go to the next or previous column in a list.
Up or Down Arrows - Go up or down one row.
Right or Left Arrows - Go right or left in a column.
Page Up - Go to the top row in a view.
Page Down - Go to the bottom row in a view.
Home - Go to the first row in a list.
End - Go to the last row in a list.

Out of operating system limit - ACCPAC DOS

When processing a transactions (e.g. order entry), an error message is generated 'Out of operating system limit'. This is due to the operating system settings.

To avoid this, review your settings:

Windows 95/98

In the config.sys, make sure it contains the following:
device=c:\windows\himem.sys
device=c:\windows\emm386.exe ram
files=250
buffers=30
dos=high,umb

Windows ME/XP

MS DOS settings are not set inside the config.sys. The FILES setting is not used but the PerVMFiles settings is used. This setting increases the number of available file handles in each MS DOS virtual machine. To set the setting, add the following line to the System.ini file, under the [386Enh] section:

PerVMFiles=255

Restart Windows ME/XP

Missing CB Header/Title in Batch List / Entry screen

Occasionally when you launch cashbook, the batch list / batch entry appears but without any column headers or titles.

To resolve this, delete all files with $ in the user folder (normally located at C:\WINACCT\USER\UserID folder).

Common database error codes

Occasionally you may encounter the following error message:

Database error code 2 - Missing / corrupted / bad database
Database error code 98 - Unable to connect to database
Database error code 108 - harddisk full / not enough space to carry out operation
Database error code 113 - access denied to database
Database error code 118 - file, page, or record is locked
Database error code 40960 - database driver not defined / unable to authenticate
Database error code 49153 - PC is not mapped to server or PC unable to access server

Removing the print confirmation when printing directly to printer

When printing to a printer, the printer driver confirmation window prompts the user to enter the number of copies to print. In addition, it offers the option to change the printer options for the job at print time. If you are in a secure environment where printing privileges are restricted for security or other reasons, you may not want to offer certain users the ability to print multiple copies of a report.

You can bypass this prompt and force ACCPAC to only send one copy to the printer, on a user-by-user basis. In each user's PLUSUSER.INI file, you should find in the [options] section the following line:

confirm on print=yes

Changing the value of the above setting to "no" removes the printer driver prompt at print time and forces the document through directly to the specified printer.

If you are intending to use this feature to restrict the number of copies a user can print of a specific report, this setting alone will not be sufficient, as the user would still be able to return to the print UI and select the reprint option; that would enable them to generate a duplicate copy. To prevent this, you will need to customize the UI through Administrative Services -> UI profile Maintenance to remove the reprint select boxes in each of the print UIs, when you would like to restrict this function.

Export to MS Excel format is not available

In Crystal Reports 8.5 and later, the option to export to Microsoft Excel format is not available in the 'Format' box of the 'Export' dialog box. Previously, the option was available. Why does this behavior occur and how can the export to Microsoft Excel format be made available?

There are three possible reasons why the option to export to Excel is no longer available.
• A virus update was recently installed
• The Excel export DLL was moved, renamed or deleted
• A third party application was installed

NOTE: The name of the file that handles the exporting to Excel depends on the version of Crystal Reports in use.
• CR 8.5 may use both u2fxls.dll and crxf_xls.dll depending on the different Monthly Hot Fixes, Weekly Hot Fixes and/or Service Packs that have been applied. If you are using u2fxls.dll, it is located in the C:\WINNT\CRYSTAL folder. If you are using crxf_xls.dl, it is located in the C:\Program Files\Common Files\Crystal folder.
• CR 9 and 10 use crxf_xls.dll

To correct this behavior perform the following steps.
1. Quit Crystal Reports.
2. In Control Panel, double-click 'Add/Remove Programs'.
3. Select 'Crystal Reports' and then click 'Change'.
4. In the 'Crystal Reports Setup' dialog box, click 'Reinstall'.

If after performing the steps above, the Excel option is still unavailable in the 'Format' box then perform the following steps.
1. Quit Crystal Reports.
2. In Control Panel, double-click 'Add/Remove Programs'.
3. Select 'Crystal Reports' and then click 'Change'.
4. In the 'Crystal Reports Setup' dialog box, click 'Add/Remove'.
5. Expand 'Export Support' and then click the arrow next to 'Excel Format'.
6. Click 'Entire feature will be installed on local hard drive', then click 'Next' and then click 'Next' again.
7. Once the installation completes, click 'Finish'.
8. Open Crystal Reports and then export the report.

P&L account setup as Balance Sheet

If a P&L account code is setup with a wrong account type of BS (Balance Sheet).

After doing a Year End, the error was discovered i.e. the P&L account code did not transfer the balance to the Retained Earnings.

To rectify the problem, do the following steps:

1. Do a journal entry to transfer the balance to Retained Earnings
e.g. DR Sales 1,000.00
CR Retained Earnings 1,000.00
2. Change the account type to IE (Income & Expense)
3. Do a journal entry to reinstate the balance
e.g. DR Retained Earnings 1,000.00
CR Sales 1,000.00

Because the account type is now Income & Expense, ACCPAC will automatically do the closing entries to transfer to Retained Earnings.

Re-register ACCPAC DLLs and OCXs

Occasionally after reinstalling ACCPAC, you may find that ACCPAC takes a longer time to launch an operation after you double-click an icon. This maybe due to DLLs or OCXs not properly registered during the installation process.

A very quick way to register DLLs and OCXs for ACCPAC is to do the following:

Launch Windows Explorer > Browse to the folder you installed ACCPAC e.g. C:\PLUSWIN or C:\ACCPAC or C:\Program files\ACCPAC > Select the Runtime folder > Locate the Regacc.exe file > Double-click the file

This program will register all ACCPAC DLLs and OCXs.

Error message when activating a module

If you are activating a module and the following error message is generated:

Damaged data - Data dictionary inconsistent

Do the following:

1. Do a database dump and load
2. Login and do the module activation

Assigning a network printer to port - lpt1

Under Windows XP operating system - to assign a network printer to a local port e.g. lpt1, do the following:
Start > Run > type cmd > type
net use lpt1: \\computername\printername /persistent:yes

To delete / remove assignment - do the following:
Start > Run > type cmd > type
net use lpt1 /delete

Adding 7.0 Text Compatibility to Crystal Reports 9

In Crystal Reports (CR) 8.5, there is an option called '7.0 Text Compatibility'. This option is no longer available in CR 9.

Is it possible to add this option to CR 9?

To access the '7.0 Text Compatibility' option in CR 8.5, on the 'File ' menu click 'Options'. You will see the '7.0 Text Compatibility' check box on the 'Reporting' tab.

This option was removed from CR 9 but you can add this functionality by adding a registry value.

====================
WARNING:

Using the Registry Editor can cause serious problems that may require reinstalling the operating system. Use the Registry Editor at your own risk. It is recommended that you back up the registry before you edit it.
====================


Add the 'Use70TextCompatability' Registry Value

1. On the 'Start' menu, click 'Run'. Type 'regedit' then click 'OK'.
2. Browse to the following registry subkey:
HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\SavedData
3. Right-click the 'SavedData' sub key and then click 'New' > 'String Value'.
4. Name this string value 'Use70TextCompatability'.
5. Right-click the 'Use70TextCompatability' string value then click 'Modify'.
6. Type 'Yes' in the 'Value data' text box then click 'OK'.

After following these steps you will still not see the '7.0 Text Compatibility' check box in the 'Options' dialog box; however, having the new registry string value equal to 'Yes' in CR 9 is equivalent to selecting the '7.0 Text Compatibility' check box in CR 8.5. Changing this value to 'No' in CR 9 is equivalent to clearing the '7.0 Text Compatibility' check box in CR 8.5.

Receipts entered to wrong customer code

If you have accidently entered a receipts to a wrong customer code, Use Bank Services, Return Customer Check process to reverse the posting.
Upon completing, enter a new entry and post the check to the correct customer code in A/R.

Steps to fix "Control not properly installed" error

This error occurs when the user doesn't have sufficient security rights to the local workstations registry. Anyone who uses ACCPAC nees to be in the Power Users Group on the local workstation. ACCPAC users also need to have read/write access for the ACCPAC Data directory and access to the registry on the server.

Creating custom folder for all users

Create the custom folder on the ACCPAC desktop of one user.
Once this is complete, copy the -c.ism file for that user to all other user folders on the system. The file can be found in the ACCPAC>USER>username directory where ACCPAC is installed.
Make sure you copy the correct company_c.ism file from one user to the next.

MDAC issue in Cashbook

MDAC cannot be re-installed, when a re-installation is attempted the message displayed is "MDAC has installed successfully" but the installation only takes a few seconds therefore not reflecting a true installation.

MDAC can be located and downloaded from the Microsoft website: www.microsoft.com

If MDAC is not installed correctly.

Do the following:

1. Go to directory c:\program files\common files\system\ado by typing in the path in the run dialog box (or via 'My computer').

2. Highlight all the dlls.

3. Then go Right ClickOpen. It will ask you what program to use to open and you select 'Microsoft Register Server'. If it does not appear click the browse button and select it from \winnt\system32. A message should appear for most of the files saying Registered Succeeded.

Some of the files will give this error and some will say successful. That is ok as some of the dlls don't need to be registered.

...Program Files\Common Files\System\ado\ms**.dll was loaded, but the DllRegisterServer entry point was not found.

Cashbook - Taking on Unreconciled Transactions

Taking on Unreconciled Transactions

To ensure that your Cashbook Balance and Bank Statement Balance are correct, you must enter your Cashbook Balance, Unreconciled Transactions and a Contra Entry for the Unreconciled amounts. This is done via a take-on batch.
Note - We suggest that this take-on is done in the prior month, i.e. if you want to create your Cashbook for May, enter this take-on batch in April, so as to avoid reflecting these transactions on any reports.
Enter your Cashbook Balance, this can be taken from your manual Cashbook, if stand-alone, or taken from your Bank Account Balance in the ACCPAC for Windows General Ledger.
Enter all your unreconciled items i.e. all those transactions not yet on your Bank Statement.
Enter a Contra Entry for the exact amount of your unreconciled entries. If the total of unreconciled items is a net payment, your contra entry must be a receipt, (or vice versa).

For Example:
Cashbook Balance 10,0000.00 (Credit)
Unreconciled Transactions 500.00 (Credit)
Contra Entry (Use any account code for entry) 500.00 (Debit)

After completing the entry, print the batch from the File menu to check your batch details. Once your are satisfied that the batch is correct, proceed to post it.
Post this Take-on batch using the Post Batches function on the Entries Menu.
You must now reconcile the Cashbook Opening Balance amount as well and the Contra Entry amount. Go to your Cashbook display and use the Reconcile function to reconcile these amounts.
You can now check that the two bottom balances equal your Cashbook balance and Bank Statement balance respectively.
Finally, if interfaced to ACCPAC for Windows General Ledger, delete the batch created in ACCPAC G/L or print and Clear (without Transferring) the G/L Transactions, this will avoid duplication of transactions to the General Ledger.

Revaluation - General Ledger

You may encounter a situation where after running the GL revaluation process, an account with zero source amount generating revaluation entries.

To resolve this, please follow the steps outlined below:

Run the revaluation as normal > General Ledger > G/L Periodic Processing > Create Revaluation Batch
After the batch have been created by the revaluation process, click Close > click Close
Go to GL Batch List > General Ledger > G/L Transactions > Batch List
Open the GL batch created by the revaluation process.
At the G/L Journal Entry > select the entry that should not have any revaluation > unselect the Auto Reverse box (There should be NO tick in the box)
Save the entry and proceed to do the same for any other entries within the batch > Click Close after you have finished amending the entries.
Post the batch > After the batch has been successfully posted, re-open the posted batch
Select the wrong entry > click Reverse
Click New icon > Type/amend the batch & entry description as required
Click Process
Click Close when the message window appears
Click Close to close the ‘Reverse Journal Entry’ window

Select the next wrong entry and repeat the above process (except you do not have to create a new batch, you can select the batch you have previously created above e.g. Batch #002336)

After completing all the reversal entries, open the Reversal Batch > General Ledger > G/L Transactions > G/L Batch List > Open the Reversal Batch
At the Currency field > amend the USD currency to the home currency of the database you are in (e.g. AsiaPac = NTD, Thailand = BAH, etc) for both detail lines
Click Save > Repeat for all entries you have generated the reversal entries > Click Close
Post the Reversal batch.

This steps should eliminate the ‘recurring revaluation entries’ even though there is no source balances. When you run the next period revaluation, these entries should no longer recur.

Year End Processing

Cashbook Year End Procedure

At the Clear Reconciled Entries function there is a Year End option. If you wish to run the Year End Option, toggle this option on. This option will:

1. Update your fiscal calendar to the next year.
2. Re-set the batch number to 1 (this can also be performed when clearing your batch status report). Take note that if you reset your batch number to 1, you will not be able to see your posted batches.
3. Move the current balances in your Cashbook G/L accounts to the previous year. (If not interfaced to ACCPAC)
4. Move the year-to-date totals in your miscellaneous file to the previous year.

Set the clearing date to 01/01/1980 if you do not want to clear any transactions.

AP Year End Procedure

Use the Year End form to:
· Reset batch numbers, so that the next batch you create of all types will use number 1.
· Move vendor activity statistics for the current year into the previous year, and zero the totals for the new year.
· Reset to 0 (zero) the Total Invoices Paid, Number Of Checks, and the Total Days To Pay fields in the Vendor Activity Statistics tab, in the Vendors form.
Before you start
For the year you are closing:
· Post all outstanding batches.
· Create all outstanding general ledger batches, and print the G/L Transactions report.
· Print all unprinted posting journals and use the Clear History form to clear the data for the posting journals.
· Close all other ACCPAC Accounts Payable forms and ask other users to leave Accounts Payable.
Tips
You can use the Year End form at any time of year if you need to reset batch numbers. However, you must first complete all the steps listed in the preceding section, "Before You Start."
We recommend that you clear activity statistics and the Total Invoices Paid and Total Days To Pay totals only at the end of your fiscal year.
To use the Year End form
1. Select the options you wish to use.
2. Choose Process.

AR Year End
Use the Year End to:
· Reset batch numbers, so that the next batch you create of any type will use number 1.
· Move customer and national account activity statistics for the current year into the previous year, and zero the statistical totals for the new year.
· Set recurring charge Amount Invoiced To Date totals for the year to zero, so that you can resume invoicing the charges in the new year.
· Reset to 0 (zero) the number in the Total Days to Pay and Total Invoices Paid fields in customer and national account activity statistics.
Before you start
For the year you are closing:
· Process all billing cycles.
· Post all outstanding batches.
· Print all unprinted posting journals to a file or a printer, then use the Clear History form to clear the data for the posting journals.
· Create all outstanding general ledger batches (if you do not use the option to create the batches during posting), then print the G/L Transactions report.
· Close all other ACCPAC Accounts Receivable forms and ask other users to leave Accounts Receivable.
To use the Year End form
1. Select the options you wish to use.
2. Choose Process.
Tips
You can use the Year End form at any time of year if you need to reset batch numbers or clear recurring-charge amounts. However, you must first complete all the steps listed in the preceding “Before You Start” section.
We recommend that you clear activity statistics only at year end.

GL Year End Procedures
At the end of a fiscal year, you must close the income and expense accounts for the old year, and open them for the next fiscal year. To maintain clear and complete accounting records, there are other procedures you may also wish to follow at year end.
To prepare for year-end processing:
· Post outstanding batches for the year you are closing.
· Print and clear all outstanding posting journals.
· If desired, print financial statements and the other reports you require for your records.
· If desired, consolidate posted transactions.
· Make sure you have specified the number of years you want to keep fiscal sets and transaction detail (on the Posting tab of the G/L Options form), and the retained earnings accounts to which you will close income and expense accounts.
· If you use multicurrency accounting, update exchange rates and revalue account balances, if needed.
· Back up data.
To complete year-end processing:
1. Use the Create New Year form to:
· Create and post transactions to transfer the balances in the income and expense accounts to retained earnings.
· Enter the opening balances for the General Ledger accounts for the new year.
2. Print and clear the Batch Status report and the posting journal created by Create New Year.
3. Back up data.
4. Use the Period End Maintenance form to delete inactive accounts and prior-year fiscal sets and transaction details that have been kept for the number of years specified on the Posting tab of the G/L Options form.