AccountMate Version 7.x for SQL/Express
Program Update from
AccountMate Version 7.x for
SQL/Express
UPDATE INSTRUCTIONS
As of November
28, 2007
Important!! Read this document first.
Do not
run the AccountMate 7 (AM 7) for SQL/Express update until you read and
thoroughly understand all pages of this document. If you have any questions
about the update process after reading this material, please contact our Product
Support team at (415) 883-1019 or via e-mail at support@accountmate.com
before you attempt the update.
What’s
New?
This entire document has
been designed to address your update needs. This document is made up of two
parts:
·
Checklist –
step-by-step list of the procedures to be performed
·
Installation
& Update Details – detailed discussion of
the procedures specified in the checklist
The following section(s) has (have) been
updated:
·
Pre-installation
Steps
·
Updating Data
·
Post-Update
Process
CHECKLIST
Note: For a detailed discussion of the specific steps in the checklist
below, please refer to the Installation & Update Details section.
A. Pre-installation Steps
|
|
1.
Correct Version |
|
|
2.
Do Modifications Exist? |
|
|
3.
Install SQL Server with Mixed Mode Authentication |
|
|
4.
Choose Computer |
|
|
5.
Check Disk Space |
|
|
6.
Check Database Compatibility Level |
|
|
7.
Verify Database Properties |
|
|
8.
Run Reports |
|
|
9.
Payroll Module Preparations |
|
|
10.
Backup |
|
|
11.
Configure ODBC DSN |
|
|
12.
Check Database Owner |
|
|
13.
Check root folder for .fxp, .scx and .sct files |
|
|
14.
Rename Folders |
B. AM 7 for SQL/Express Installation
|
|
1.
Verify Exclusive Access |
|
|
2.
Run Setup.exe |
|
|
3.
Select Destination Location |
|
|
4.
Select the “Custom” Installation Option |
|
|
5.
AccountMate Product Key |
|
|
6.
Select Components to Install |
|
|
7.
Back up AM 7 for SQL/Express |
C. Updating Data
|
|
1.
Download and Extract Latest Update Scripts |
|
|
2.
Run Foreign Key Checker |
|
|
3.
Launch the Administrator Program |
D. Post-Update Process
|
|
1.
Activate Modules |
|
|
2.
Initialize Modules and Install Latest PR Tax Update |
|
|
3.
Access Maintenance Records |
|
|
4.
Compare GL Transfer Report and GL Financial Statements |
|
|
5.
Review Updated Data (by running reports) |
|
|
6.
Perform “Typical” or “Compact” Installation on Workstations |
E. Troubleshooting Errors and Messages
|
|
1.
Update does not occur |
|
|
2.
Invalid Product Key |
|
|
3.
Program Patches Error Message |
|
|
4.
Foreign Key Violation Error |
|
|
5.
Recalculate FUTA/SUTA Error |
--- Checklist Ends
(Detailed Discussion Follows) ---
INSTALLATION & UPDATE DETAILS
A.
Pre-installation Steps
This data migration program is designed to update:
o
Databases on AccountMate
7 for SQL to AccountMate 7.2 for SQL or higher; or
o
Databases on AccountMate
7 for Express to AccountMate 7.2 for Express or higher.
Check the version number of your current
installation by accessing the “About AccountMate” function under the Help menu.
Verify that the version
number on the update CD is AM7.2 or higher. If you want to update to any other version
contact AccountMate Product Support at (415) 883-1019, support@accountmate.com or call Customer
Service at 1-800-877-8896 ext 752.
To use the generic update program, your AM 7 for
SQL/Express system and data structure must be unmodified. This update
will remove any modifications or custom reports in the source AM 7 for
SQL/Express system. If you still need
your customizations, you must have a programmer apply them into your new version.
If you modified
the data structure or system, please do
not proceed. Instead, e-mail our Development Consulting Service, devconsulting@accountmate.com,
to inquire about the procedures to modify the generic update program to work
with your modified AccountMate data or system. Regular consulting charges
will apply.
3. Install SQL Server with Mixed Mode Authentication
Verify that SQL Server
or Express is properly installed and set up for use with AccountMate.
a. Make
sure that SQL Server/Express is installed under Mixed Mode to support
both SQL and NT Authentication.
b. Be sure
to assign a non-blank password to the ‘sa’ SQL login ID. Take note of
the password as you will need it to configure the ODBC DSN connection.
c. Take
note of the SQL Server/Express Instance name as you will also need it to
configure the ODBC DSN connection.
Note:
AccountMate does not sell SQL Server. For assistance on installing your SQL
Server or Express, refer to your IT Professional.
4.
Choose Computer
We recommend that you
run the update at the computer that holds the SQL Server/Express. If for any
reason you cannot perform the update at the server, run it at a computer that
has the SQL Client Tools.
5.
Check Disk Space
You should have free disk space of at least 2-3
times the size of your current AM 7 for SQL/Express data in the server drive
that holds your data.
6. Check Database Compatibility Level
The compatibility level of the AMWSYS, sample and
live company databases to be updated should be 70 or higher; otherwise, you will encounter syntax errors during the
update.
To verify the compatibility level of the databases
using Microsoft SQL Server 2005, perform the following steps:
a.
Access
the Microsoft SQL Server Management Studio.
b.
Connect
to the SQL Server that holds the databases you are going to update.
c.
Expand
the Databases folder; point the cursor to the affected database (e.g. AMWSYS) and
right-click on your mouse.
d. Select Properties to display the Properties window. Access the Options page. In the Compatibility Level field, make sure that the Database Compatibility Level is 70 or higher. Do this for each of the AMWSYS, sample, and live company databases.
To verify the compatibility level of the databases
using Microsoft SQL Server 2000, perform the following steps:
a.
Access
the SQL Server Enterprise Manager.
b.
Connect
to the SQL Server that holds the databases you are going to update.
c.
Expand
the Databases folder; point the cursor to the affected database (e.g. AMWSYS)
and right-click on your mouse.
d. Select Properties to display the Properties window. Access the Options tab. In the Level field of the Compatibility section, make sure that the Database Compatibility Level is 70 or higher. Do this for each of the AMWSYS, sample, and live company databases.
7. Verify Database Properties
Using Microsoft SQL Server Management Studio (SQL
Server 2005) or SQL Enterprise Manager (SQL Server 2000), verify that the Data
Files and Transaction Log are set to Automatically Grow File for all the
AccountMate databases (i.e. AMWSYS, sample, and live companies). Set the File Growth to at least 10%. You can change the settings back
to what they were after the update is complete.
8. Run Reports
Run the GL Transfer
Report from each of the non-GL modules that you use. Also generate the GL
financial reports (e.g. Balance Sheet, Income Statement, GL Listing, etc.). If you
encounter any error (e.g. Out of balance, Transfer date is not defined, etc.),
resolve the data problem before proceeding with the update. It is advisable to
keep a hard copy of the final, correct reports to compare against the same
reports generated after the update.
9.
Payroll Module Preparations
Note: If you are not updating from AM 7.1 or if you do not have the PR module, please proceed to step 10 of this section.
If any of your earning
codes starts with the following characters, you must rename them before
performing the update:
o
SALARY
o
HOURLY
o
TMECRD
o
OTTAX
o
OTNTAX
You can use the data renaming feature of the AccountMate
Data Wizard 7.3 for SQL to rename the affected earning code records. If you are an AccountMate End User, contact your
AccountMate Reseller and ask them to help you with this.
10. Backup
Back up all databases including AMWSYS, sample and
any other company databases. Back up your AM 7 for SQL/Express front end as
well. In case the update fails or you decide to postpone it for another time,
you can delete the AM/SQL folder used in the failed update, restore the files from
your backup (both the AM/SQL folder and the databases) and either try again or
reschedule the update. DO NOT SKIP THIS STEP!
11. Configure ODBC DSN
Note: If you are updating to AM 7.3
or higher, please skip this step.
Before installing AM 7 for SQL/Express, verify that
your System DSN is configured correctly for AccountMate. The procedures described below
must be performed on the server and on every workstation that will run
the AM 7 for SQL/Express program.
Verify that:
a.
Your System Data Source name is “AMMS”.
b.
Type in the correct SQL Server/Express instance
name.
c.
Set the ODBC to use SQL Server Authentication.
d.
Mark the check box “Connect to SQL Server to
obtain default settings for the additional configuration options”.
e. Use ‘sa’ for the login ID and use a valid password; and, as much as possible, do not use a blank password.
f.
Test
the connection and verify that your test results will read, “Tests completed
successfully”.
Check that all the
databases to be updated to AM 7 for SQL/Express have ‘amlogin’ for the database
owner. This includes the AMWSYS and sample databases.
13. Check root folder for .fxp, .scx and .sct files
Check your root folder for any *.fxp, *.scx and
*.sct files. They are known to cause problems and must be removed before
you install the new AM 7 for SQL/Express version. THIS IS A MUST!
In the server and in every
workstation where AM 7 for SQL/Express will be installed (i.e. AMSQL folder),
rename the Forms, Patches and RPTMOD folders before installing the new version. The
installation of the new AM 7 for SQL/Express version will create new Forms, Patches
and RPTMOD folders.
B.
AM 7 for SQL/Express Installation
Check that no one
accesses AccountMate while you perform the installation and update. You can
verify this by selecting the Current Login User List function; make sure
you are the only user currently logged in. Exit AccountMate and continue with
the installation and update.
2.
Run Setup.exe
Insert the AM 7 for SQL/Express CD into a CD-ROM
drive. If the installer does not automatically run, click on Setup.exe. You
will be shown the AccountMate 7.x for SQL or the AccountMate 7.x for Express Setup
screen.
3. Select Destination Location
On the Choose
Destination Location screen, accept the default Destination Folder by
clicking the Next> button; or
click the Browse… button to change
to the desired path.
o This should
be the folder in which your source AM 7 for SQL/Express program is installed.
o If you are
running the installer at the machine where the AM 7 for SQL/Express file server
is physically located, select that local folder name (e.g. C:\AMSQL).
o If you are running the installer from
a machine other than the file server where your source AM 7 for SQL/Express program
is physically located, select the network path where the AM 7 for SQL/Express folder
has been mapped (e.g. F:\AMSQL).
4. Select the “Custom” Installation Option
Click on the Next button to proceed to the Setup Type screen. Since you are
installing on the server where you will perform the update, make sure
that you choose the Custom installation
option.
5.
AccountMate Product Key
In the Product
Key window, enter
the 25-character AccountMate Product Key
printed on the License File CD that
was shipped to you with your AM 7 for SQL/Express package. Should you encounter
problems with your Product Key, contact AccountMate Customer Service at
1-800-877-8896 ext 752.
Note: The
Product Key is version specific, which means that the product key for version AM7.3
will not work for any other version and vice versa.
6.
Select Components to Install
In the Select Components window, you will see
three Components to install. These are:
o
Program Files => these are the executable files, which will
allow you to run the main AccountMate 7 for SQL/Express program and all its
functions and reports.
o
Administrator Program => installs the
o
Run-time Files => will install the Crystal Reports and Visual
FoxPro run-time libraries (DLL’s) necessary for running various AccountMate 7
for SQL/Express functions and reports.
Be
sure to select the Program Files and Run-time Files option boxes in this window so that the
executable program as well as the Crystal Reports and Visual FoxPro run-time
libraries (DLL’s) will be installed.
For the server, the computer
where the update will be performed (if other than the server), and those workstations
that need to run the
Continue with the rest
of the installation screens until you see the InstallShield Wizard Complete
window.
7.
Back up AM 7 for SQL/Express
Back up the AccountMate 7 for SQL/Express front end
files you just installed. In case of failure, you can delete the AM/SQL folder and
databases used in the failed update and restore this backup and the database
backup without having to redo the AM 7 for SQL/Express installation. DO NOT SKIP THIS STEP!
C.
Updating Data
1. Download and Extract Latest Update Scripts
Make
sure you have the latest update scripts (i.e. Update File for AM7). The latest version of this file is
available for download from the Download
Center\Conversion & Upgrade Tools section of the AccountMate website. If
you are an AccountMate End User, you must ask your AccountMate Reseller to download
these files for you.
Extract
the contents of the download file (SQL_to_AM7.zip) into the AccountMate 7
application folder. When extracting the contents of the zip file, verify that
the “overwrite existing files” and “use folder names” checkboxes are marked.
Note: If you
are updating to AM 7.2, please skip
this step.
Locate the Foreign Key Checker (AMFKCHK.EXE) in the Upgrade
subfolder of your AccountMate 7 application folder. This file should have been
installed along with the update scripts you downloaded from the AccountMate
website.
This tool must be run
against all AccountMate company databases, including the sample company.
It checks for possible foreign key violation errors that could occur during the
update process. Depending on the validation results, you must fix the data
and then create another backup before proceeding with the update. The Upgrade
folder also contains the AccountMate for
SQL Foreign Key Checker Utility_User Guide (PDF). Please read this document
carefully before using the tool.
3.
Launch the Administrator Program
Run the AccountMate Administrator program.
If
you are updating to AM 7.3 or
higher, you should be prompted for the SQL Server or Express instance where
your AccountMate data is stored (Figure 1). Enter the computer name and the SQL
Server or Express instance name (e.g. COMP1\SQL1NSTC); then, press OK.
Note: Those who are updating to AM7.2
will not see this message.
Figure 1.
Update Dialogue Box
After
you’ve entered the Supervisor password, you should see a message telling you
that a new program has been detected (Figure 2). Click Yes on the message box to proceed with
the update.
Figure 2.
Update Dialogue Box
The next screen (Figure 3) is a reminder to download the
latest version of the update scripts and instructions from the Conversion and
Upgrades section of the AccountMate website. If you have already performed
these steps, click on Yes to proceed
to the next screen.
Figure 3. Update Dialogue Box
Before proceeding with the update, the system will remind you
to make a backup of the various existing databases used in AccountMate 7 for
SQL/Express, including AMWSYS (Figure 4). This is most important and
must be complied with at all times.
Figure 4. Update Dialogue Box
Click the Yes button. At this stage, you will note that
the system is being updated one database at a time starting with AMWSYS. This
can be a prolonged process depending on the number of companies to be updated
and the size of each company’s data. Please
be patient!
Figure 5. Update Status Window
When
all the databases have been updated, you will get the message shown in Figure 5.
Figure 6. Help/About AccountMate Screen
One way of verifying that you have successfully completed
the update is by checking the information contained in the Help/About AccountMate
window. Check that the Version#, System DBC and Company DBC Versions are
as shown in Figure 6. The Program Directory will not necessarily change since
this is just the directory/path where the AM 7 for SQL/Express front end is
installed.
D.
Post-Update Process
Activate all your modules.
a. In the
Administrator program, select Setup > Licensing > Activate Module. The system will display the
Important Notice window that contains the AccountMate License and Copyright
Notice. Click on “I Agree” to proceed.
b. Verify the value entered in the
space provided for the AccountMate
Product Key. If the Product Key supplied is valid, the system will list all
the modules that you acquired with a check mark on the Purchase column.
c. To activate a module, click on the
box in the Activate column beside the
module you want to be able to use.
Note: If you
entered the AccountMate Product Key upon installation, you will see the same
information upon accessing the Activate Module function window. If you purchased
any new modules since the new version was installed, you will be issued another
Product Key. You must enter the new Product Key in the space provided in the
Activate Module window to activate the new modules purchased.
2.
Initialize Modules and Install
Latest PR Tax Update
For
each activated module that you use, you must access the Housekeeping menu and
perform Module Setup.
If you are using the Payroll module and have a PR
Tax Subscription for the current tax year, you must also verify that you have
the latest PR tax updates installed. You can check this by running the
Federal/State/Local Tax Table Maintenance functions. If you do not have the
latest PR tax updates, you must download them from the
Go through the different Maintenance functions and
verify that all required fields are set up or assigned the desired values. This
must be performed for each module you have activated and initialized. Pay
particular attention to the following Maintenance records:
o
Inventory Maintenance => verify that an
inventory item marked for Use in Work Order is not set up to Use Specification,
and vice versa. If both checkboxes are marked, you must verify which one you
want to apply to the inventory record and unmark the feature that does not
apply to the item.
o
Employee Maintenance => verify the
accumulated earnings, paid leave, deductions, and taxes of each employee. You
must also verify that the correct Earning Codes, Paid Leave Codes, and
Deductions have been assigned to each employee record. Finally, verify the
Expense GL Account IDs assigned in the Earning Code tab for each employee
record.
o
Earning Code Maintenance => verify the
assigned Expense GL Account IDs and the Accumulate Paid Leave settings defined
for each earning code record.
4.
Compare GL Transfer Report and GL
Financial Statements
Run the GL Transfer Report for your non-GL modules
and the GL financial statements (i.e.
Balance Sheet, Income Statement, and GL Listing) and compare the information
generated against the same reports from your previous AM 7 for SQL/Express
version. This will help identify any issues that may have resulted from the update
and will make it possible for you to correct these problems before data
processing is resumed in the new version.
5. Review Updated Data (by running reports)
You
should run the newly updated company in AM 7 for SQL/Express and review the
data to ensure that everything has been updated successfully. Here is a
partial list of reports you can run to help verify the update:
o
GL : Trial Balance, Income Statement, Balance
Sheet
o
AR : AR Aging, Payment Distribution Report
o
AP : AP Aging, AP Check Register
o
SO : Open Order Report, SO Shipment Report
o
PO : Backorder Report,
o
IC : Inventory Transfer In-Transit
Report
o
MI : Back Order Report
o
BR : Bank Reconciliation Report
o
PR : PR Check Register Report, Earning Code
Transaction Report, Paid Leave Transaction Report, Deduction Transactions
Report, QTD or YTD Tax Withholdings Report
o
CL : Consolidated Account Balance Report,
Consolidated Account Balance Analysis
6.
Perform “Typical” or “Compact” Installation
on Workstations
Caution: You need not run the workstation installation on the
computer where you performed the server installation. If you
previously performed the server update through one of your workstations,
running the installer on that same workstation will display the Uninstall
AccountMate window.
a. Access
each workstation where you will run the new AM 7 for SQL/Express version. Insert
the AM 7 for SQL/Express CD into a CD-ROM drive. If the installer does not
automatically run, click on Setup.exe. You will be shown the AccountMate 7.x
for SQL or AccountMate 7.x for Express Setup screen.
b. On the Choose Destination Location screen,
accept the default Destination Folder by clicking the Next> button; or click the Browse…
button to change to the desired path.
o
For workstations with separate client
installations: this should be the folder where the old AccountMate program is
installed. If a workstation is running on Windows Vista, we strongly
recommend that you do NOT install the application in the C:\Program Files
directory.
o
For mapped workstation installations: since you are
installing into a machine other than the file server where your AM 7 for
SQL/Express program is physically located, select the network path where the AM
7 for SQL/Express folder has been mapped (e.g. F:\AMSQL).
c. Click on
the Next button to proceed to the Setup Type screen. Since you are
installing on a workstation, make sure that you choose either the Typical or Compact installation option.
o
Choose the Typical
option if you want to run a separate client installation from each workstation.
This option installs the AM 7 for SQL/Express program and run-time files on the
workstation.
o
Choose the Compact
option if you want to run the AM 7 for SQL/Express program through a mapping
from a file server. This option installs just the run-time files on the
workstation.
d. Next, the wizard will take you
through the selection of a Program
Folder, after which the files for the version update will be installed.
Click Finish to complete the
workstation installation.
e. Configure ODBC DSN on each workstation unless you
are updating to AM 7.3 or higher. Refer to step 11 in Part A for more
information.
E.
Troubleshooting Errors and Messages
The
succeeding sections will provide tips for troubleshooting some of the more
commonly encountered update error messages.
This
usually happens when the files for the new version are not properly installed
or when the backup is not properly restored after a failed update. To resolve
this:
a.
Make
sure that the main AMSQL program and the Administrator program for the new AM 7
for SQL/Express version are properly installed. Check the modified date of the
amsql.exe and amsqladm.exe in the AMSQL folder where you are running the update.
b.
Make
sure you are running the Administrator program, and not the main AMSQL program.
Only the Administrator program will trigger the version update.
c.
If
you are launching the Administrator program from a program shortcut, verify that
the shortcut is mapped to the AMSQL folder where you installed the new AM 7 for
SQL/Express version.
d.
If
you have previously performed a failed update, make sure that you delete the AccountMate
folder used in the failed update, restore the backup made of the AM 7 for SQL/Express front
end (step 7 of Part B) and restore the backup of the AMWSYS and live company
databases before you resume the update.
This
error may be caused by a difference between the product key and the license
file. The system checks the information coded in both and verifies that they
are for the same End User.
This
error may also be the result of using a product key for the wrong version. For
example, you cannot use the AM7.2 product key for an AM7.3 installation and
vice versa.
If
you receive this error, contact AccountMate Customer Service.
3. Program Patches Error Message
If
you did not rename the Patches folder before performing the update, you will
receive the error shown in Figure 7 the moment you launch the main AccountMate
program after the update. To resolve this error:
a.
Verify
whether you still need the program patches. If you are uncertain about this,
contact your AccountMate Reseller or AccountMate Product Support.
b.
If
you no longer need the program patches, delete them from the Patches folder by
clicking ‘Yes’ in response to the error message.
c.
If
you still need the program patches, copy them to a folder outside AccountMate
before launching the program. When prompted with the error message after
launching AccountMate, click ‘Yes’. Copy the program patches back to the
Patches folder before you resume processing transactions in the new version.
Figure 7. Program Patches Error
4. Foreign Key Violation Error
One of the possible issues that could cause the
update to fail is a foreign key violation. You may find an error entry similar
to the following in the log file:
The UPDATE statement conflicted with the
FOREIGN KEY constraint “FK_…” The conflict occurred in database “database
name”, table “dbo.tablename”, column ‘cacctid’.
This
problem occurs if you do not perform step 2 of Part C (Run Foreign Key
Checker) before performing the update. To resolve this problem, you must:
a. Delete the AM 7 company database and application
folder used in the failed update.
b. Restore the AM 7 application folder backup (step 7
of Part B) and the database backup (step 10 of Part A) made prior to
the update.
c. Run the
foreign key checker utility against each company database (step 2 of Part C).
5.
Recalculate FUTA/SUTA Error
If
you did not install the latest PR Tax Subscription program and you perform the
Recalculate FUTA/SUTA function in your updated system, you may get an error
message similar to the following:
“[Microsoft][ODBC SQL Server
Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY
constraint ‘FK_prdist_cwageacc_209A25F7’. The conflict occurred in database
‘sample999’, table ‘glacct’, column ‘cacctid’.
SQL Stmt: insert into prdist(cuid,
cempno, cwageacc, ctaxacc, ctogl, ctrsno, dpost, nwageamt, ntaxamt, ndistpct)
values(?pcUid, ?Prtrst.cEmpNo, “, ?Prempg.cTaxacc, ‘F,?Prtrst.cTrsNo,
?Prtrst.dPost, 0, ?pnTaxAmt, ?Prempg.nDistPct)”
To
resolve this issue, be sure to install the latest PR Tax Subscription program.
Downloads: The following can also be
obtained from our website; visit: /businesspartner/info/downloads/conversion_upgrades.asp
· Update File for AM7