Setup the AcctVantage SQL Server and Client

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.

Initially, this process was verified and tested using AV 2017 with Mac & PC  Servers and MS Excel on Windows 10. (December 2017).

However, since publication, Apple stopped supporting the ODBC Manager and there appears to be a fatal bug when using SQL and macOS versions of AV. 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.

This does still work on Windows as of December 2023 (AV 2023).

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 service project.

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

1. Setting Up AcctVantage ERP SQL Server

SQL Server Settings

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

See below for details on each section of this window.

Database Settings - SQL

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 (via the ODBC Data Source Administrator)
  • IP Address: Select the server’s IP address (usually 192.168.x.x).
  • Enable TLS: This option indicates whether the SQL server must enable the TLS protocol for processing SQL connections.
    • Note that when this protocol is enabled, you must add the ":ssl" keyword to the end of the IP address of the SQL server when you open a connection using the SQL LOGIN command.
  • Allow Flash Player Requests: This option can be used to enable the mechanism for supporting Flash Player requests by the 4D SQL server.

SQL Server Access

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. We recommend using the settings in the above screenshot.

  • Everybody: This refers to everyone with an AcctVantage account setup in your company data file.
    • Note: AcctVantage User permissions do not apply here. SQL 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.

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 below.
  • Case Sensitive String Comparison: This setting determines if external queries to the database are case sensitive.

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!

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.

To stop the SQL Server, click the Stop 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.

Starting and Stopping the SQL Server

3. 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 AV Support for the appropriate drivers!

3.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!
3.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

  1. Install the 4D ODBC driver for Windows.
  2. Open the ODBC Data Source Administrator.
    • Windows 10: Navigate to Control Panel ➤ Administrative Tools ➤ ODBC Data Sources (64-bit).
  3. Click on the Drivers tab and verify that the 4D driver was successfully installed. You should see an entry named 4D v18 ODBC Driver 64-bit (see screenshot).
  4. 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.
Windows ODBC Data Source Administrator - Drivers

Set Up the User DSN

  1. Navigate to the User DSN tab and click the Add button.
  2. Select the 4D v18 ODBC Driver from the list of available drivers and click Finish. This will open up the configuration window.
Windows ODBC Data Source Administrator - User DSN

Configure data source

  1. Enter a Data Source Name and Description for this connection (e.g AcctVantage SQL Server)
  2. 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.
  3. Check the SSL check box if you have it enabled on the server.
  4. Enter the User and Password. These need to match the credentials of the AcctVantage User who will be connecting to the database.
  5. After you've entered the above info, click Test to verify the connection is working.
DSN Configuration

3.2.1. Connection Test Success!

Click OK here and then click OK again on the ODBC Data Source Admin window to save.

You are now ready to query the AcctVantage database!

Connection Test Success!