Unlock Dynamics GP Data: Connect to SQL with VBA - A Practical Guide

Table of Contents

This article delves into the technique of using a Microsoft Visual Basic for Applications (VBA) script to establish a connection with a Microsoft SQL database. This database serves as the backend for Microsoft Dynamics GP. Understanding this connection method is crucial for users who need to access or manipulate Dynamics GP data directly from within the application’s interface using custom logic. The approach leverages standard database connectivity methods available within the VBA environment.

The methods discussed are applicable to certain versions of Microsoft Dynamics GP, specifically focusing on techniques demonstrated in environments like Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0. While the core principles of using ADODB for SQL connections in VBA remain consistent across many versions and applications, specific object names or procedures within Dynamics GP might vary slightly. The example provided illustrates a fundamental pattern that can be adapted.

Introduction to Dynamics GP and SQL Connectivity¶

Microsoft Dynamics GP is a comprehensive business management solution widely used for financial management, human resources, manufacturing, supply chain, and more. At its core, Dynamics GP stores all its critical business data within a Microsoft SQL Server database. While Dynamics GP provides a rich user interface and numerous built-in reports and functions, there are often requirements for custom automation, data validation, or presenting specific pieces of information directly within a Dynamics GP window.

VBA, embedded within Dynamics GP through Dexterity customizations, offers a powerful way to extend the application’s functionality without full-scale development. By combining VBA’s scripting capabilities with industry-standard database connectivity technologies like ActiveX Data Objects (ADODB), users can write code that interacts directly with the underlying SQL database. This allows for retrieving, validating, or even updating data in response to user actions within Dynamics GP forms. This article provides a practical example of how to achieve this connection.

Prerequisites and Scope¶

To effectively follow and implement the concepts described in this guide, you should have access to a Microsoft Dynamics GP environment and its corresponding Microsoft SQL Server database. The specific example focuses on Dynamics GP 9.0 or Great Plains 8.0, but the core ADODB principles apply broadly. You will need administrative access to both Dynamics GP (to access the customization tools) and potentially the SQL Server (to understand the database schema and permissions, although the example uses SA which is generally not recommended for production).

A basic understanding of SQL query syntax is beneficial, as you will need to construct SQL statements to interact with the database. Familiarity with the Microsoft Dynamics GP user interface and its windows is also necessary, particularly the concept of fields and events within those windows. Finally, prior exposure to VBA scripting, including variable declaration, object creation, and basic control flow structures (If...Then...Else), will make understanding the code example much easier. This guide assumes you have the necessary software installed and accessible.

The scope of this article is specifically demonstrating the connection and data retrieval process using ADODB within a Dynamics GP VBA context. It provides a functional code example for a specific scenario: retrieving an account index based on the account number in the Account Maintenance window. It does not cover data modification, complex transactions, advanced error handling, or deployment strategies beyond pasting the code into the VBA editor.

Connecting to SQL Server using ADODB in VBA¶

The standard and most common way to connect to external databases, including Microsoft SQL Server, from within Microsoft applications’ VBA environment is by using ActiveX Data Objects (ADODB). ADODB provides a set of objects that represent database connection, commands, recordsets, and errors. The core objects used for a simple data retrieval task like the one in our example are:

• Connection Object (ADODB.Connection): Represents an open connection to a data source. It is used to establish the link between your VBA script and the SQL Server database. You configure this object with a connection string specifying the database location, authentication method, and other parameters.
• Recordset Object (ADODB.Recordset): Represents a set of records from a database table or the results of executing a command. Once a query is executed against the connected database, the results are typically returned into a Recordset object, which allows you to navigate through the rows and access the data in each column.
• Command Object (ADODB.Command - not used in the simple example but common): Represents a command, such as a SQL query or a stored procedure call, that you want to execute against the data source. While the example uses the Execute method directly on the Connection object, for more complex queries or stored procedures, using a dedicated Command object is often preferred.

The process generally involves creating instances of the Connection and Recordset objects, defining a connection string, opening the connection, defining the SQL query, executing the query to populate the Recordset, processing the data in the Recordset, and finally, closing the connection and cleaning up the objects.

Let’s visualize the basic flow using a Mermaid diagram:

mermaid graph TD A[Dynamics GP Window Event] --> B(VBA Script Triggered); B --> C(Create ADODB.Connection Object); C --> D(Define Connection String); D --> E(Open Connection); E --> F(Define SQL Query String); F --> G(Execute Query via Connection Object); G --> H(Receive ADODB.Recordset Object); H -- Process Data --> I(Access Data in Recordset); I --> J(Update Dynamics GP Field); J --> K(Close ADODB.Connection); K --> L(Release Objects); L --> M(Script Ends);

This diagram shows the sequence of operations performed by the VBA script to connect to the SQL database, retrieve data, and use it within the Dynamics GP window.

Deconstructing the VBA Code Example¶

The provided VBA script is designed to run within the context of the Account Maintenance window in Dynamics GP. It’s triggered by the Description_AfterGotFocus event. This means the script will execute after the user tabs into or clicks into the “Description” field on the Account Maintenance form. Let’s break down the code line by line to understand its function.

Private Sub Description_AfterGotFocus()
    ' Declare variables to hold database objects and query string
    Dim objRec As Object
    Dim objConn As Object
    Dim cmdString As String

    ' Create instances of the ADODB Recordset and Connection objects
    Set objRec = CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")

    ' Define the connection string for the SQL database
    ' Provider=MSDASQL: Specifies the Microsoft OLE DB Provider for ODBC Drivers.
    ' DSN=GreatPlains: Specifies the ODBC Data Source Name configured for Dynamics GP.
    ' Initial Catalog=TWO: Specifies the database name (TWO is a common sample company database).
    ' User Id=sa: Specifies the SQL login user (SA is the system administrator).
    ' Password=password: Specifies the password for the SA user.
    objConn.ConnectionString = "Provider=MSDASQL;DSN=GreatPlains;Initial Catalog=TWO;User Id=sa;Password=password"

    ' Open the connection to the database
    objConn.Open

    ' Define the SQL query string
    ' Select ACTINDX: Select the Account Index field.
    ' from GL00105: From the Account Index Master table.
    ' where (ACTNUMST='...'): Filter where the Account Number String matches the value in the Dynamics GP 'Account' field.
    cmdString = "Select ACTINDX from GL00105 where (ACTNUMST='" + Account + "')"

    ' Execute the SQL query and load the results into the Recordset object
    Set objRec = objConn.Execute(cmdString)

    ' Check if any records were returned by the query
    If objRec.EOF = True Then
        ' If no records found, clear the User-Defined1 field
        AccountMaintenance.UserDefined1 = ""
    Else
        ' If records found, assign the value of the ACTINDX field from the first record to the User-Defined1 field
        AccountMaintenance.UserDefined1 = objRec!ACTINDX
    End If

    ' Close the database connection
    objConn.Close

    ' Clean up the object variables (optional in modern VBA, but good practice)
    Set objRec = Nothing
    Set objConn = Nothing
End Sub

Each section of the code plays a vital role. The variable declarations prepare the environment. The CreateObject calls instantiate the necessary ADODB components. The ConnectionString is arguably the most critical part, telling ADODB how and where to connect. The Open method establishes the live link. The cmdString variable holds the SQL command to execute. The Execute method sends the command to the database and retrieves the results. The If...Else block checks if the query returned data and processes it accordingly. Finally, the Close method terminates the database connection, which is important for releasing resources.

The Connection String Explained¶

Let’s take a closer look at the connection string structure used in the example:

Component	Value	Description
Provider	MSDASQL	Specifies using the OLE DB Provider for ODBC. This is common for DSN connections.
DSN	GreatPlains	Specifies the name of the pre-configured ODBC Data Source Name.
Initial Catalog	TWO	Specifies the default database to use after the connection is made. ‘TWO’ is a sample company database.
User Id	sa	Specifies the SQL Server login ID used for authentication.
Password	password	Specifies the password for the SQL Server login. Highly insecure!

Using a DSN (DSN=GreatPlains) relies on an ODBC data source being configured on the client machine where Dynamics GP is running. This DSN typically points to the SQL Server instance and database. The alternative is a DSN-less connection string, which includes the server name directly (e.g., Server=YourSQLServerName;Database=TWO;...). While the DSN approach centralizes connection details, a DSN-less string can sometimes simplify deployment as it doesn’t require pre-configuring the ODBC source on each client. However, the DSN method as shown is typical for older Dynamics GP VBA customizations.

The use of User Id=sa and Password=password hardcoded directly in the script is a significant security vulnerability. The ‘sa’ account is the most powerful administrator account in SQL Server. In a real-world scenario, you should use a dedicated, low-privilege SQL login with minimal necessary permissions, or preferably, use Windows Authentication (Integrated Security=SSPI) if the user running Dynamics GP has domain credentials that can be authenticated by the SQL Server. We will discuss security further below.

The SQL Query Explained¶

The SQL query Select ACTINDX from GL00105 where (ACTNUMST='\" + Account + \"') is a simple SELECT statement targeting a specific table and filtering based on a condition.

• GL00105: This is the Account Index Master table in Dynamics GP. It stores information about the chart of accounts, including the mapping between the formatted account number string and an internal integer index (ACTINDX).
• ACTINDX: This column stores the internal integer account index. This is often used internally by Dynamics GP for performance reasons.
• ACTNUMST: This column stores the formatted account number string (e.g., “100-1101-00”).
• WHERE (ACTNUMST='...'): This clause filters the results to find the row where the formatted account number (ACTNUMST) matches a specific value.
• \" + Account + \"': This is the VBA part that dynamically inserts the value from the Account field (presumably the account number string entered by the user) in the Dynamics GP window into the SQL query string. The quotes (') around Account are necessary because ACTNUMST is a string field in the SQL database.

The query essentially says: “Go to the GL00105 table, find the row where the ACTNUMST column is equal to the account number currently in the Dynamics GP Account field, and give me the value from the ACTINDX column for that row.”

Processing the Recordset¶

After the objConn.Execute(cmdString) line runs, the objRec Recordset object contains the results of the query. Since the query is expected to return at most one row (as account numbers are unique), the script checks if any rows were returned using objRec.EOF = True. EOF stands for “End of File” (or End Of Recordset). If objRec.EOF is True immediately after execution, it means the recordset is empty.

• If objRec.EOF = True Then AccountMaintenance.UserDefined1 = "": If no account matching the entered number was found in GL00105, the script clears the UserDefined1 field in the Dynamics GP window.
• Else AccountMaintenance.UserDefined1 = objRec!ACTINDX: If at least one row was found, objRec.EOF is False. The script then accesses the data in the ACTINDX column of the first (and only, in this case) row using objRec!ACTINDX and assigns its value to the UserDefined1 field in the Dynamics GP Account Maintenance window. The ! syntax is shorthand for accessing a field within an ADODB Recordset.

Finally, objConn.Close releases the connection to the database, and setting the object variables to Nothing helps the VBA garbage collector clean up memory resources, though VBA is often lenient with this for local variables in event procedures.

Implementing the Script in Dynamics GP¶

The original instructions provide a clear step-by-step guide to add this VBA script to the Account Maintenance window in Dynamics GP. Let’s elaborate slightly on each step for clarity.

Step-by-Step Implementation:¶

1. Open the Account Maintenance window: Launch Microsoft Dynamics GP and navigate to the Account Maintenance window (usually under Financial >> Cards >> Account). This is the window where you will be adding the custom VBA logic.
2. Add Current Window to Visual Basic: In Dynamics GP, go to the “Tools” menu, then “Customize,” and select “Add Current Window to Visual Basic.” This action registers the currently active window (Account Maintenance) within the Dynamics GP VBA project, making its objects and events accessible in the Visual Basic Editor. If the window is already added, this option might be greyed out or offer an update option.
3. Add Fields to Visual Basic: Still under “Tools” > “Customize”, select “Add Fields to Visual Basic.” A list of fields on the current window will appear. You need to select the specific fields your script will interact with or be triggered by. For this example, you must add the “Account Number” field (likely named Account), the “Description” field (named Description - where the event triggers), and the “User-Defined 1” field (where the result is displayed). Adding these fields exposes them as objects within your VBA project, allowing your script to read their values (Account) and write to them (UserDefined1).
4. Open the Visual Basic Editor: Go back to “Tools” > “Customize” and select “Visual Basic Editor.” This launches the standard Microsoft VBA development environment.
5. Navigate to the Account Maintenance Code Object: In the VBA Editor’s Project Explorer pane (usually on the left), expand the “Great Plains Objects” node. You should find an object corresponding to the Account Maintenance window you added in Step 2. Double-click this object (likely named AccountMaintenance) to open its code window in the main editor area.
6. Paste and Modify the Code: In the code window for the AccountMaintenance object, locate or create the Description_AfterGotFocus() event procedure. If you correctly added the Description field and the window to VBA, this event stub might already exist. Copy the VBA code provided earlier and paste it into this event procedure. Important: You might need to modify the ConnectionString based on your actual SQL Server configuration, database name, and the (preferably non-SA) login credentials you intend to use. Replace "DSN=GreatPlains;Initial Catalog=TWO;User Id=sa;Password=password" with the correct details for your environment. After pasting, save the changes in the VBA editor (File > Save or the save icon).

Once saved, close the VBA Editor and return to Dynamics GP. When you open the Account Maintenance window and enter an account number, then tab or click into the Description field, the script should execute. If an account with that number exists in the GL00105 table of the specified database, its ACTINDX value should appear in the User-Defined 1 field.

Security Considerations¶

As highlighted earlier, hardcoding the ‘sa’ user ID and password directly in the VBA script is a major security risk. Anyone with access to the VBA project could potentially see these credentials. Furthermore, the ‘sa’ account has unrestricted access to your entire SQL Server instance, not just the Dynamics GP databases. If these credentials were compromised, it could lead to severe data breaches or system damage.

Recommended Security Practices:¶

• Avoid Hardcoding Passwords: Never store sensitive credentials directly in code. While challenging in embedded VBA, consider alternative authentication methods.
• Use Windows Authentication: If possible, configure SQL Server and Dynamics GP to use Windows Authentication (Integrated Security=SSPI). This allows users to connect to SQL using their Windows login, eliminating the need to store SQL credentials. Permissions are then managed at the domain user or group level in SQL Server.
• Create Dedicated, Low-Privilege SQL Logins: If Windows Authentication is not feasible, create a specific SQL login only for use with this script. Grant this login the absolute minimum permissions required (e.g., SELECT access only to the GL00105 table in the specific company database). This limits the damage if the credentials are leaked.
• Encrypt or Obfuscate (Limited Efficacy in VBA): While VBA projects can be password protected, this offers minimal security against determined attackers. Code obfuscation is complex and often impractical for simple scripts.

Given the limitations of VBA in Dynamics GP for securely storing credentials, using Windows Authentication or a dedicated, restricted SQL login is paramount.

Error Handling¶

The provided script example lacks any form of error handling. If the database server is unreachable, the DSN is misconfigured, the SQL login fails, the table or column name is incorrect, or any other database error occurs, the script will likely halt with a runtime error. This can be disruptive to the user experience.

Basic Error Handling in VBA:¶

The simplest form of error handling in VBA is using the On Error statement.

Private Sub Description_AfterGotFocus()
    Dim objRec As Object
    Dim objConn As Object
    Dim cmdString As String

    ' Basic Error Handling - Resume execution on the next line if an error occurs
    On Error Resume Next

    Set objRec = CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")

    objConn.ConnectionString = "Provider=MSDASQL;DSN=GreatPlains;Initial Catalog=TWO;User Id=sa;Password=password"

    ' Check for errors after setting connection string (e.g., if CreateObject failed)
    If Err.Number <> 0 Then
        MsgBox "Error creating ADODB objects: " & Err.Description, vbCritical
        Exit Sub ' Stop execution if objects couldn't be created
    End If

    objConn.Open

    ' Check for errors after opening connection
    If Err.Number <> 0 Then
        MsgBox "Error opening database connection: " & Err.Description, vbCritical
        On Error GoTo 0 ' Turn off basic error handling before showing message
        Exit Sub
    End If

    cmdString = "Select ACTINDX from GL00105 where (ACTNUMST='" + Account + "')"

    Set objRec = objConn.Execute(cmdString)

    ' Check for errors after executing query
    If Err.Number <> 0 Then
        MsgBox "Error executing SQL query: " & Err.Description & vbCrLf & "Query: " & cmdString, vbCritical
        On Error GoTo 0
        ' Optionally handle the error, maybe leave UserDefined1 blank
        AccountMaintenance.UserDefined1 = "Error" ' Indicate failure in the field
    Else
        ' Process results only if query was successful
        If objRec.EOF = True Then
            AccountMaintenance.UserDefined1 = ""
        Else
            AccountMaintenance.UserDefined1 = objRec!ACTINDX
        End If
    End If


    ' Resume normal error handling (turn off On Error Resume Next)
    On Error GoTo 0

    ' Ensure connection is closed even if errors occurred earlier (best done in a separate cleanup routine or using GoTo)
    ' For simplicity in this example, we'll add a check here, but a GoTo cleanup is more robust.
    If Not objConn Is Nothing Then
        If objConn.State = 1 Then ' State = 1 means connection is open
            objConn.Close
        End If
    End If

    Set objRec = Nothing
    Set objConn = Nothing

End Sub

This enhanced version adds basic checks for errors (If Err.Number <> 0 Then) after critical operations like creating objects, opening the connection, and executing the query. It uses MsgBox to inform the user and Exit Sub to stop execution gracefully if a critical error occurs. A more robust approach would involve using On Error GoTo ErrorHandler and having a dedicated cleanup section at the end of the procedure.

Performance Considerations¶

Executing a database query every time a user tabs into a field (AfterGotFocus event) can potentially impact performance, especially if the SQL server is slow or the network latency is high. For simple lookups like this, the impact might be minimal. However, for more complex queries or if this pattern is repeated on many fields, users might experience delays as they navigate through the window.

If performance becomes an issue, consider alternative strategies:

• Trigger on a different event: Perhaps retrieve the data only when the account number field loses focus (Account_AfterLostFocus) or when a specific button is clicked.
• Cache data: If multiple fields require data from the same lookup, retrieve it once and store it in a form-level variable.
• Dexterity development: For performance-critical or complex integrations, developing directly in Dexterity (Dynamics GP’s native development environment) or using alternative integration methods like web services might be more appropriate.

For the simple lookup described, the AfterGotFocus event is functionally correct but keep performance in mind for more ambitious VBA-to-SQL integrations.

Alternative Methods (Historical Note)¶

The original text mentions the RetrieveGlobals_80.dll file as an alternative method for Great Plains 8.0. This refers to a set of external libraries (often Dexterity-based or C++ based) that Microsoft or partners provided to expose certain Dynamics GP internal functions or data retrieval methods to external applications or VBA. These DLLs were version-specific and often required careful handling regarding registration and variable declaration.

While this was a valid method in older versions, relying on such specific, potentially outdated DLLs is generally not recommended for new development unless absolutely necessary for compatibility with legacy systems. The ADODB approach is a standard Microsoft technology and is more broadly applicable across different versions of Windows and SQL Server.

Summary¶

Connecting Microsoft Dynamics GP to its underlying SQL Server database using VBA and ADODB is a powerful technique for extending the application’s functionality through client-side scripting. By understanding the ADODB object model (Connection, Recordset) and the structure of the connection string and SQL queries, developers can retrieve and utilize Dynamics GP data directly within the application’s windows.

The provided example demonstrates a practical application: looking up the account index (ACTINDX) from the GL00105 table based on the account number entered in the Account Maintenance window and displaying it in a user-defined field. Implementing this involves adding the window and relevant fields to the VBA project and inserting the ADODB script into the appropriate field event (Description_AfterGotFocus).

However, it is crucial to acknowledge and address the significant security risks associated with hardcoding SQL credentials and to implement robust error handling to provide a stable user experience. While the example uses older versions and simple techniques, the core principles of ADODB connectivity remain relevant for accessing SQL data from VBA in various contexts.

Share Your Experience¶

Have you used VBA to connect Dynamics GP to SQL? What challenges did you face, and how did you overcome them? Do you have alternative methods or best practices to share regarding security or performance in such integrations? Let us know in the comments below!