Setup the AcctVantage SQL Server and Client

This article was written when AV 2013 was current; the process has been verified and tested using AV 2017 with Mac & PC  Servers and MS Excel on Windows 10.(December 2017).

Apple stopped supporting the ODBC Manager some time ago and there appears to be a fatal bug. Attempts to connect using macOS 10.13 have failed because the macOS ODBC Manager crashes when creating a User/System DSN. Unfortunately, this is out of our control.

Technical Support Note:

Due to the complex nature of this feature, standard technical support will be limited to helping set up the SQL server and demonstrating the connection with either Microsoft Excel or Filemaker. Any additional customization or training may be requested as a paid project.

1. Setting Up AcctVantage ERP SQL Server

AcctVantage ERP has the ability to accept external connections to the data via Open Database Connectivity (ODBC), which is a standard software interface for accessing databases. To enable this feature, follow the steps outlined below.

Note: Each SQL connection into your data file uses one of your 4D concurrent user licenses.

1.1. SQL Server Settings

In AcctVantage ERP Server, navigate to File Database Settings and select the SQL tab.

See below for details on each section of this window.

SQL Server Publishing

  • Launch SQL Server at Startup: If you want SQL Server to automatically start when the AcctVantage Server is launched, check this box. The server can be also be launched manually (see Starting and Stopping the SQL Server below).
  • TCP Port: The default TCP Port is 19812. If you change this make sure the client side is also updated.
  • IP Address: Select the server’s IP address (usually 192.168.x.x).
  • Enable SSL: This setting determines if your server connection is encrypted or not. Any incoming connections must be SSL compliant if this box is checked.
  • Allow Flash Player Requests: Check if your SQL client software uses flash.

SQL Server Access

The SQL Server Access for Default Schema section details what type of connections the SQL server will allow. There are three group options to choose from for each connection type:

  • Everybody: This refers to everyone with an AcctVantage account setup in your company data file.
    • Note: Users will be able to query any table whether they have access to it in AcctVantage or not.
  • Nobody: This turns off access to this type of connection.
  • 4D Open Users: The list of 4D open users is defined by source code in AcctVantage and is currently restricted to the user External.
    • In order to use this account, you will need to log in as Administrator and set the user password through the User Access Levels screen in AcctVantage.

The connection types are:

  • Read Only (Data):  Allows read only connections to all tables for the chosen group. The recommended selection is either Everybody or 4D Open Users.
  • Read/Write (Data): Allows read and write connections to all tables for the chosen group. Writing to some tables and creating new records through this connection may cause system instability and/or data corruption. The recommended selection is Nobody. See warning below.
  • Full (Data and Design): Allows read and write connections to all tables and the ability to add new tables and fields. The recommended selection is Nobody. See warning below.

Warning about writing to the database via SQL.

We strongly recommend that you DO NOT USE THE SQL SERVER TO WRITE TO THE DATABASE.

  • When read/write or full is enabled, you will be able to add, delete or modify any record in the database.
  • There is an extremely probable possibility that you will lose your data integrity since all of the checks and balances [i.e. the business logic that occurs when saving data] are being circumvented.

WE ARE NOT RESPONSIBLE FOR ANY DATA ERRORS OR INSTABILITY THAT OCCUR IF YOU ENABLE THE READ/WRITE FEATURE VIA SQL!

SQL Engine Options

  • Auto-Commit Transactions: This setting determines if changes are saved into data as soon as the external command is made. If this setting is unchecked and Read/Write (Data) is enabled, any data changes must be manually committed. See warning above.
  • Case Sensitive String Comparison: This setting determines if external queries to the database are case sensitive.

1.2. Starting and Stopping the SQL Server

To start the SQL Server manually, select the SQL Server tab from the AV Server Administration window and click the Start SQL Server button. If Launch SQL Server at Startup is checked on the settings screen the SQL server will automatically start when the AcctVantage Server is launched.

To stop the SQL Server, click the Stop SQL Server button.

2. Connecting to the AcctVantage ERP SQL Server

You will need to install the current version of the 4D ODBC driver on the SQL Client.

Contact Support for the appropriate Drivers!

2.1. Setting Up the SQL Client (Mac)

In our testing (December 2017), we have found that macOS 10.13 (possibly previous versions as well) and Apple's ODBC Manager have a fatal bug that does not allow for creation of the User/System DSN. Unfortunately, this is not an AcctVantage and is therefore out of our control.

ODBC access to the AcctVantage database has been tested successfully using Mac OS 10.7 and Microsoft® Excel® for Mac 2011.

Install the ODBC Driver

  1. Install the iODBC Data Source Administrator Utility.
  • Note: An ODBC Administrator Utility was no longer included with Mac OS after OS 10.4. Apple does provide a free download of this Utility but it will not work if you want to query the database with Microsoft® Excel®. You must install iODBC if you want to use Excel®.
  • After the install is complete this Utility will be located at the following path: /Applications/Utilities/OpenLink ODBC Administrator

Install the 4D ODBC driver for Mac OSX.

  • Open iODBC and click on the ODBC Drivers tab and verify that the 4D driver was successfully installed.
  • You should see an entry named 4D (v12, v15, etc.) ODBC Driver (see below) and the version should match the current version of 4D that is installed. For AcctVantage 2013, the 4D version is v12.5. Future versions of AcctVantage will require an updated ODBC Driver.
Install the ODBC Driver

Set Up the User DSN

  • Next we need to set up the User DSN (Data Source Name) which contains the information about the SQL Server we are going to establish a connection with.
  • Navigate to the User DSN tab and click the Add button.
Set Up the User DSN

Choose A Driver

Select the 4D ODBC Driver from the list of available drivers and click Finish. This will open up the configuration window.

Choose A Driver

Configure data source

  1. On the Basics tab:
  • Enter a Name and Description for this connection (e.g AcctVantage SQL Server)
  • Enter the IP address and Port of the SQL server. You can find this information by clicking on the SQL Server tab of the AcctVantage Server window (as seen in Step 1.2 above).
    • The default port is 19812. If you changed this value on the AV Server, it will need to match here.
  • Check the SSL check box if you have it enabled on the server.
  • You can also enter Default user credentials if you choose. If you enter nothing you will be prompted for the credentials when the connection is established.

After you've entered this information it is a good idea to test the connection.

Configure data source

Connection Test Success!

Click OK here and then click OK again on the Configure data source window to save.

You are now ready to query the AcctVantage Database!

Connection Test Success!

2.2. Setting Up the SQL Client (Windows)

This has been tested on Windows 7 and outlines how to set up the Windows SQL Client. The connection is managed through ODBC and uses drivers supplied by 4D.

Install the ODBC Driver

  • Install the 4D ODBC driver for Windows.
  • Open the ODBC Data Source Administrator.
    • Windows XP: Navigate to Control Panel ➤ System and Security  ➤ Administrative Tools ➤ Data Sources (ODBC).
    • Windows 7: You will need to open up the 32bit version of this utility which can be found at the following path: C:\Windows\SysWOW64\odbcad32.exe
    • Windows 8: Navigate to Control Panel ➤ System and Security  ➤ Administrative Tools ➤ ODBC Data Sources (32-bit).
  • Click on the Drivers tab and verify that the 4D driver was successfully installed. You should see an entry named 4D ODBC Driver (see screenshot).
  • Next we need to set up the User DSN (Data Source Name) which contains the information of the server we are going to establish a connection with.
Install the ODBC Driver

Set Up the User DSN

  • Navigate to the User DSN tab and click the Add button.
  • Select the 4D  ODBC Driver from the list of available drivers and click Finish. This will open up the configuration window.
Set Up the User DSN

Configure data source

  • On the Basics tab:
    • Enter a Name and Description for this connection (e.g AcctVantage SQL Server)
    • Enter the IP address and Port of the SQL server. You can find this information by clicking on the SQL Server tab of the AcctVantage server. The default port is 19812. If you changed this value on the server, it will need to match here.
    • Check the SSL check box if you have it enabled on the server.
    • You can also enter Default user credentials if you choose. If you enter nothing you will be prompted for the credentials when the connection is established.
  • After you've entered this information it is a good idea to test the connection.
Configure data source

2.3. Connection Test Success!

Click OK here and then click OK again on the Configure data source window to save.

You are now ready to query the AcctVantage Database!

Connection Test Success!