Connecting Alchemex to Accpac via ODBC

From Sage Intelligence Knowledgebase
Jump to: navigation, search

This document is intended as a guide for creating an ODBC link from Alchemex to your Accpac Data. It is not intended to replace the in depth documentation provided for all the components involved.

For more detailed information and trouble shooting please consult the relevant documentation for each component.

This document will cover connecting Alchemex to Accpac on both Pervasive SQL and Microsoft SQL Server.

Outline of Accpac Pervasive Relationship

Accpac uses a Pervasive (formerly Btrieve) database for storing its data. To access this data it uses the Pervasive SQL Engine.

To access the Accpac Data files (DDF and DAT files) via a third party reporting system such as Alchemex the relevant Pervasive ODBC drivers must be installed.

Alchemex accesses the data by communicating with the ODBC driver, which in turn communicates with the relevant Pervasive Engine.

The Pervasive engine (i.e Pervasive SQL Engine) is installed with the Accpac installation so this should already be on the destination workstation.

The ODBC drivers that work with the Pervasive SQL Engines are an integral part of these engines and hence are also installed during the Accpac installation. If you are connecting to Accpac Enterprise Version 5.2 and you are using the Pervasive SQL 2000 engine (or higher) then follow the steps in the section:”Connecting to Accpac Enterprise version 5.2 (Pervasive SQL Engine)”

If you are connecting to Accpac Enterprise Version 5.2 and you are using Microsoft SQL Server then follow the steps in the section: ”Connecting to Accpac Enterprise 5.2 (Microsoft SQL Server)


Connecting to Accpac Enterprise version 5.2 (Pervasive SQL Engine)

Minimum Requirements:

  • Alchemex installed and licensed on a pc
  • Pervasive SQL Engine installed and running on a pc
  • Accpac Data files
  • Accpac Data Dictionary files (DDF files)

Assuming Alchemex and Accpac are successfully installed and functioning on the workstation then proceed with the two-step process below:

1.Create a connection within the Alchemex Administrator (as part of creating the connection you will also create a Pervasive Database Name to reference your data files and assign this database name to the connection you created)

2.Test the connection

Step1. Creating a connection for use on Pervasive SQL Data

Create the data connection by doing the following:

1.Open the Alchemex Administrator.

2.Double-click on Alchemex Enterprise in the Object window on the left hand side. All connection types are displayed.

3.Right-click on the Pervasive ODBC Client Interface and select the Add Connection from the displayed shortcut menu.

4.Enter a Connection Name in the Connection Name field. eg: Accpac

5.In the Server Name box change the Server Name property to the name of the machine running the Pervasive Engine (leave it blank if each machine is to use its own local engine).

You can select the server name by clicking the Select machine name button on the right of the server name field)

Now you are ready to create the database name

6. For the Database Name property click the button on the right to retrieve a list of existing Database Names that are under the control of the Pervasive Engine. (Note that if you are using a remote Pervasive SQL Server then you will be prompted for logon details. You will need a logon with administrative privileges on the server to perform the functions) 7. If you do not already have a database name setup for the Accpac Data then select the [Create New Pervasive DB Name] option, otherwise select the name from the list and proceed from point 10 below. 8. The window displayed is for the path of the DDF files. Browse to the location of your Accpac DDF files, and then click OK. (The DDF files could be stored within the same folder as the data files, you need to confirm their location) 9. The next window relates to the path of the data files. Browse to the location of the Accpac Data files, and then click OK. 10.If your database requires a userID and Password, then enter this information in the relevant fields.

11.Click Add in the Connection Info dialog box. The dialog box closes and the data connection is displayed below the Pervasive ODBC Client Interface connection type The properties of the connection to your Accpac data should look something like the illustration below. In this example the user is running a local engine (the local machine is acting as the server and the client for Pervasive) therefore the Server name field is left blank.

Connecting Alchemex to Accpac

Step 2. Testing the connection

To test that the connection is valid and functioning correctly, do the following:

1.Right-click on the data connection you have just created eg Accpac.

2.Select Check/Test. If a “Connection [Accpac] Check Succeeded” message appears then the connection is functioning correctly. It does not however mean that the Data File Path is accessible.

As a final check to see that the Data File Path is accessible do one of the following:

1. Import an Accpac template if you have one available, then run Check/Test on the container, which will appear beneath the connection after the import process has completed.

OR

2. Add a container to the connection (choose any single table for this test). Then run a Check/Test on that container.

3. Right click on the container, then select Sample Data


If a “Container [containername)] Check Succeeded” message appears and you are able to sample the data within the container then the Data File Path is accessible and you are ready to run reports from the Alchemex Report manager.


It is important to understand the components involved in this connection.

The layers are:-

1. Alchemex Pervasive ODBC Client Interface Layer (local)

2. Pervasive ODBC Engine Interface layer (local or remote) Pervasive SQL Engine

3. Pervasive SQL Engine (Workgroup/Workstation/Server)Pervasive Database Name

4. Pervasive Database Name Accpac DDF and DAT files


All of the necessary components must be functioning correctly for a successful connection.

To troubleshoot any of the components we recommend doing the following:-

1.To verify that the Pervasive SQL Engine is working correctly run Accpac and see that you can view data for the chosen Company.

2.To verify that the Pervasive ODBC Engine & Client Interfaces are functioning open the Pervasive Control Centre and drill down to the machine running the Pervasive SQL Engine.

Open the Databases node, then open a Database (SAMINC if it exists) and then the Tables node.

In the right hand pane select a Table and right click and press Open.

If data appears then the ODBC layers are functioning.

3.To verify that the paths you have set for your Database Name are correct select your Accpac connection and then choose Tools > PVS.SQL DB Manger. Open the server and select the relevant Database Name. Note down the DDF and DAT paths in the property pane and verify that these paths are valid and accessible from the point of view of the machine running the Pervasive SQL Engine (you may need to get onto the Server machine to verify this).

4.Using windows explorer you should see DDF files in the DDF folder (e.g fields.ddf and files.ddf).

In the DAT file folder you should see a number of DAT files

     (The dat files will be named according to the various Accpac modules installed such as AP_ _ _.dat, GL_ _ _.dat, AR _ _ _ .dat etc….).

For functionality available on creating joins and expressions consult the Pervasive SQL documentation.

This should be located on your windows start menu in Programs\Pervasive\Pervasive.SQL version\Documents


Connecting to Accpac Enterprise version 5.2 (MS SQL Server)

Minimum Requirements:

Alchemex installed and licensed on a pc Microsoft SQL Server installed on a pc Accpac SQL database


Consult MS SQL Server documentation for importing a SQL Database

Assuming Alchemex and Accpac are successfully installed and functioning on the workstation then proceed with the two-step process below:

1.Create a connection within the Alchemex Administrator

2.Test the connection

Step 1. Creating a connection for use on an MS SQL database

Create the data connection by doing the following: 1.Open the Alchemex Administrator.

2.Double-click on Alchemex Enterprise in the Object window on the left hand side. All connection types are displayed.

3.Right-click on the ODBC Driver for SQL Server and select the Add Connection from the displayed shortcut menu.

4.Enter a Connection Name in the Connection Name field. eg: Accpac52

5.In the Server Name box change the Server Name property to the name of the machine running the MS SQL Server components. (You can select the server name by clicking the Select machine name button on the right of the server name field)

6.For the Database Name property enter the name of the SQL database

7.If the SQL Server requires a UserID and Password, then enter this information in the relevant fields.

8.Click Add in the Connection Info dialog box. The dialog box closes and the data connection is displayed below the ODBC Driver for SQL Server connection type

The properties of the connection to your Accpac data should look something like the illustration below. In this example the user is running off an MS SQL Server named DBNServer and the name of their database within SQL is AccpacSample. Since they require a user name and

Connecting Alchemex to Accpac

password to access the SQL Server, those details have also been entered.

Step 2 Testing the connection

To test that the connection is valid and functioning correctly, do the following:

1.Right-click on the data connection you have just created eg Accpac52.

2.Select Check/Test.

If a “Connection [Accpac52] Check Succeeded” message appears then the connection is functioning correctly. It does not however mean that the Database is accessible.

As a final check to see that the Database is accessible do one of the following:

1. Import an Accpac template if you have one available, then run Check/Test on the container, which will appear beneath the connection after the import process has completed.

OR

2. Add a container to the connection (choose any single table for this test).

Then run a Check/Test on that container.

3.Right click on the container, then select Sample Data

If a “Container [containername)] Check Succeeded” message appears and you are able to sample the data within the container then the Database is accessible and you are ready to run reports from the Alchemex Report manager.

If your connection or your container checks are not resulting in a successful message, then you need to verify the details you have entered within the Properties screen of the data connection.

Ensure that you have entered the correct server name, database name and UserID and password.

Go into SQL Enterprise Manager or Query Analyzer to confirm the details.


For functionality available on creating joins and expressions consult the Microsoft SQL Server documentation called Books Online.