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.

Apple has stopped supporting the ODBC Manager. Attempts to connect using macOS have failed because the macOS ODBC Manager crashes when creating a User/System DSN. Unfortunately, this is out of our control.

Using Windows for ODBC connections works as of September 2024 (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 Microsoft Excel on Windows. 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.

Video: Connect Excel to AcctVantage

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 (Windows)

This has been tested on Windows 10 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.1.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!