Unlock Dynamics GP Integration: Mastering New VBA Connection Objects

Table of Contents

This article delves into the pivotal enhancements introduced in Microsoft Dynamics GP 10.0, specifically focusing on the new connection objects integrated into Visual Basic for Applications (VBA) through the GPConn.dll file. These advancements represent a significant step forward in how developers and power users can interact programmatically with Dynamics GP data, offering more robust and secure methods for data access and manipulation. Understanding these new objects is crucial for anyone looking to customize or extend Dynamics GP functionalities using VBA.

Evolution of Dynamics GP VBA Connectivity¶

Before the advent of Dynamics GP 10.0, the landscape of VBA connectivity to Dynamics GP was largely dependent on specific DLL files like RetrieveGlobals.dll and RetrieveGlobals9.dll. These files served a critical purpose: they facilitated the retrieval of essential user context information, such as the username and password of the user currently logged into Microsoft Dynamics GP. This was an indispensable feature for creating dynamic connection strings within VBA code, eliminating the need for hardcoding sensitive credentials directly into applications.

While effective for their time, these older methods often involved external dependencies and specific compatibility requirements. As software ecosystems evolve, so do the demands for efficiency, security, and integration simplicity. The transition to Dynamics GP 10.0 marked a shift towards a more integrated and streamlined approach to VBA connectivity, aiming to enhance the developer experience and system performance. The new connection objects were designed to supersede these legacy files, providing a native and more cohesive framework for accessing the Dynamics GP environment.

Introducing the UserInfoGet Connection Object¶

Microsoft Dynamics GP 10.0 ushered in a new era for VBA development with the introduction of new connection objects, primarily encapsulated within the UserInfoGet object. This innovative object directly replaces the functionalities provided by the RetrieveGlobals.dll and RetrieveGlobals9.dll files, offering a modern and integrated solution for establishing active database connections based on the currently logged-in Dynamics GP user’s context. The UserInfoGet object simplifies the process of obtaining critical user and company-specific data, making it far more efficient and secure to interact with the Dynamics GP database.

The core strength of UserInfoGet lies in its ability to abstract away the complexities of credential management and database connection establishment. By leveraging this object, developers can securely retrieve vital information and forge database connections without exposing sensitive data within their code. This not only enhances the security posture of custom VBA solutions but also streamlines development by providing direct access to essential properties and methods.

Key Properties and Methods of UserInfoGet¶

The UserInfoGet connection object is endowed with several powerful properties and methods that are essential for crafting dynamic and context-aware VBA solutions in Dynamics GP. Each serves a distinct purpose, enabling developers to retrieve specific pieces of information or perform critical actions related to the current Dynamics GP session.

Here’s a breakdown of its primary components:

• CompanyName Property:

  • Purpose: This property allows developers to retrieve the full name of the company that the user is currently logged into within Microsoft Dynamics GP. This is particularly useful for logging, reporting, or customizing user interfaces based on the active company.
  • Usage Example: Imagine needing to display the current company’s name on a custom form. You could simply use MsgBox UserInfoGet.CompanyName to retrieve and show it. This avoids hardcoding company names and ensures the solution is dynamic across different company databases.

• CreateADOConnection Method:

  • Purpose: This is arguably the most critical method provided by UserInfoGet. It returns an active ADODB.Connection object that is pre-configured and authenticated using the credentials of the user currently logged into Dynamics GP. This eliminates the need to manually construct connection strings, specify servers, or handle authentication details.
  • Usage Example: When you need to execute SQL queries directly against the Dynamics GP database, this method provides the ready-to-use connection. It seamlessly integrates with the ActiveX Data Objects (ADO) library, which is a standard for database interaction in VBA.

• IntercompanyID Property:

  • Purpose: This property retrieves the unique intercompany ID associated with the company the user is currently logged into. In Dynamics GP, databases are often named based on their intercompany ID (e.g., TWO, FABRIKAM). This property is invaluable for dynamically setting the default database for your ADO connection, ensuring that your queries target the correct company’s data.
  • Usage Example: After obtaining an ADO connection via CreateADOConnection, you can set cn.DefaultDatabase = UserInfoGet.IntercompanyID to ensure all subsequent commands executed on that connection operate within the context of the current company’s database.

• UserDate Property:

  • Purpose: This property provides the current user’s date setting in Dynamics GP. Dynamics GP allows users to set a specific “user date,” which can differ from the system date and is often used for transaction entry or reporting periods.
  • Usage Example: If your VBA customization needs to perform operations or filter data based on the user-defined date within GP, you can retrieve it directly using UserInfoGet.UserDate instead of relying on the system date.

• UserID Property:

  • Purpose: This property returns the login ID (username) of the user currently logged into Microsoft Dynamics GP. This is the short, system-level identifier for the user.
  • Usage Example: Useful for auditing purposes, assigning ownership to data modifications, or personalizing forms and reports based on the logged-in user’s ID.

• UserName Property:

  • Purpose: This property provides the full, display name of the user currently logged into Microsoft Dynamics GP. This is typically a more descriptive name than the UserID.
  • Usage Example: Ideal for displaying a friendly greeting on a custom form (e.g., “Welcome, John Doe!”) or for populating audit trails with a more human-readable name.

These properties and methods together empower developers to create highly dynamic, secure, and user-context-aware solutions within Dynamics GP VBA, moving beyond static connection strings and hardcoded values.

Understanding the Underlying ADO Framework¶

Before diving into the sample code, it’s essential to grasp the basics of ActiveX Data Objects (ADO), which form the backbone of database interaction in the provided VBA example. ADO is a high-level, object-oriented interface to access data from various sources, including relational databases like Microsoft SQL Server (which underpins Dynamics GP).

The sample code utilizes three core ADO objects:

1. ADODB.Connection (cn): Represents an open connection to a data source. It is the conduit through which all communication with the database flows. In our context, UserInfoGet.CreateADOConnection provides this pre-configured connection.
2. ADODB.Command (cmd): Used to execute commands (like SQL queries, stored procedures, or table definitions) against the data source. It requires an active ADODB.Connection to operate.
3. ADODB.Recordset (rst): Represents a set of records from a database table or the results of a query. It allows you to navigate through the data, access individual fields, and sometimes modify records.

These three objects work in concert: Connection establishes the link, Command sends instructions, and Recordset holds the results.

Practical Implementation: Sample Code Breakdown¶

The following VBA code snippet beautifully demonstrates how the new UserInfoGet object integrates with ADO to perform a simple data retrieval task. Let’s dissect each part of this powerful example.

Dim cn As New ADODB.connection
Dim rst As New ADODB.recordset
Dim cmd As New ADODB.Command

Private Sub PushButtonM79_AfterUserChanged()
    cmd.CommandText = "Select * from RM00101"
    Set rst = cmd.Execute
    MsgBox (rst!custnmbr)
End Sub

Private Sub Window_AfterOpen()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
End Sub

Global Declarations¶

Dim cn As New ADODB.connection
Dim rst As New ADODB.recordset
Dim cmd As New ADODB.Command

These lines globally declare the ADO connection, recordset, and command objects. Declaring them at the module level (outside any specific sub-procedure) makes them accessible to all procedures within that module, ensuring they persist across different event handlers within a form or window. This is a common practice for objects that need to maintain state or be reused throughout the lifetime of a form.

Window_AfterOpen() Event Procedure¶

Private Sub Window_AfterOpen()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
End Sub

This procedure executes automatically whenever the Dynamics GP window containing this VBA code is opened. This is the ideal place to establish the initial database connection and set up the command object.

1. Set cn = UserInfoGet.CreateADOConnection: This is the cornerstone of the new integration. It calls the CreateADOConnection method of the UserInfoGet object. This method returns an ADODB.Connection object (cn) that is already authenticated and connected to the SQL Server instance where Dynamics GP resides, using the current GP user’s security context. This eliminates the need to specify server names, usernames, or passwords in your code, significantly enhancing security and simplifying deployment.
2. cn.DefaultDatabase = UserInfoGet.IntercompanyID: Once the connection cn is established, this line sets the default database for that connection. The UserInfoGet.IntercompanyID property dynamically provides the correct database name (e.g., “TWO”, “FABRIKAM”) corresponding to the company the user is currently logged into in Dynamics GP. This ensures that any subsequent SQL queries executed through this connection will target the correct company’s data.
3. cmd.ActiveConnection = cn: Finally, the cmd (ADODB.Command) object is linked to the newly established and configured connection cn. This crucial step prepares the cmd object to execute SQL commands against the correct database within the active Dynamics GP session.

PushButtonM79_AfterUserChanged() Event Procedure¶

Private Sub PushButtonM79_AfterUserChanged()
    cmd.CommandText = "Select * from RM00101"
    Set rst = cmd.Execute
    MsgBox (rst!custnmbr)
End Sub

This procedure would typically be triggered by an event, such as a user clicking a button named PushButtonM79 or after a user changes a field related to that button.

1. cmd.CommandText = "Select * from RM00101": Here, a SQL SELECT statement is assigned to the CommandText property of the cmd object. RM00101 is a well-known table in Dynamics GP, representing the Customer Master File. This query is designed to retrieve all columns from this table.
2. Set rst = cmd.Execute: This line executes the SQL query defined in cmd.CommandText against the database via the cmd object’s active connection. The results of the query are then populated into the rst (ADODB.Recordset) object. The rst object now holds all customer records from RM00101.
3. MsgBox (rst!custnmbr): After the recordset is populated, this line displays a message box containing the value of the custnmbr field (customer number) from the first record in the rst recordset. This demonstrates a simple retrieval and display of data from the Dynamics GP database using the established connection.

This example clearly illustrates the seamless and secure way to interact with Dynamics GP data using the new UserInfoGet object and standard ADO techniques.

Benefits and Advantages of the New Objects¶

The introduction of the UserInfoGet object and its associated methods and properties brings a multitude of benefits to developers and organizations utilizing Microsoft Dynamics GP:

• Enhanced Security: The most significant advantage is the elimination of hardcoded usernames and passwords. By leveraging the current GP user’s authenticated session, UserInfoGet dynamically establishes a secure connection, reducing the risk of exposing sensitive credentials in code or configuration files. This aligns with modern security best practices.
• Simplified Development: Developers no longer need to manage complex connection strings or devise mechanisms for retrieving credentials. The CreateADOConnection method provides a ready-to-use ADO connection, streamlining the coding process and reducing potential errors associated with manual connection setup.
• Contextual Awareness: The UserInfoGet object automatically understands the current user’s login and the company they are working in. This contextual awareness ensures that VBA customizations inherently operate within the correct business context, making solutions more robust and less prone to cross-company data issues.
• Seamless Integration: By residing within GPConn.dll, these objects are a native part of the Dynamics GP environment. This ensures better compatibility, stability, and performance compared to external or third-party solutions that might require more complex integration steps.
• Future-Proofing: As part of Dynamics GP 10.0’s architectural evolution, these new objects represent the intended pathway for programmatic interaction. Adopting them ensures that custom solutions are built on a more stable and supported foundation, reducing the likelihood of requiring major rewrites with future GP updates.

Setting Up Your VBA Environment for Dynamics GP¶

To effectively utilize these new connection objects, ensure your VBA project references the necessary libraries. In the VBA editor (Alt + F11 in Dynamics GP), navigate to Tools > References.... You will typically need to ensure the following references are enabled:

• Microsoft ActiveX Data Objects X.X Library: This is crucial for using ADODB.Connection, ADODB.Recordset, and ADODB.Command objects. The “X.X” will depend on the version installed on your system (e.g., 2.8, 6.1).
• GPConn: This library, containing the UserInfoGet object, should be automatically available if Dynamics GP is properly installed. If not, you might need to browse for GPConn.dll in your Dynamics GP application directory.

Ensuring these references are correctly set up is a prerequisite for your VBA code to compile and execute successfully, allowing you to tap into the power of the UserInfoGet object.

Best Practices for Dynamics GP VBA Development¶

When working with UserInfoGet and ADO in Dynamics GP VBA, consider these best practices to ensure robust and maintainable solutions:

• Error Handling: Always implement robust error handling using On Error GoTo statements. Database operations can fail for various reasons (network issues, permissions, malformed queries), and gracefully handling these errors is critical for user experience and application stability.
• Resource Management: After performing database operations, always explicitly close and set ADO objects (Connection, Recordset, Command) to Nothing. This releases system resources and prevents memory leaks, especially important in long-running or frequently used customizations.
  

  If Not rst Is Nothing Then If rst.State = adStateOpen Then rst.Close: Set rst = Nothing
  If Not cn Is Nothing Then If cn.State = adStateOpen Then cn.Close: Set cn = Nothing
  Set cmd = Nothing
  

• SQL Injection Prevention: While UserInfoGet handles connection security, be mindful of SQL injection when constructing dynamic SQL queries. Use parameterized queries if you are concatenating user input into your SQL statements.
• Keep It Modular: Break down complex logic into smaller, reusable functions or subroutines. This improves code readability, maintainability, and reusability.
• Test Thoroughly: Test your VBA customizations extensively in a non-production environment with various user roles and scenarios to ensure they function as expected and handle edge cases gracefully.
• Documentation: Document your code thoroughly. Explain the purpose of procedures, variables, and any complex logic. This will be invaluable for future maintenance and troubleshooting.

Conclusion¶

The UserInfoGet connection object in Microsoft Dynamics GP 10.0 represents a significant advancement in facilitating secure and efficient programmatic interaction with the system via VBA. By abstracting away the complexities of connection management and leveraging the existing Dynamics GP user context, it empowers developers to build more robust, secure, and dynamic customizations. Mastering these new connection objects is essential for anyone involved in extending the functionality of Dynamics GP, ensuring their solutions are built on a modern, secure, and maintainable foundation.

We encourage Dynamics GP developers and power users to explore these capabilities further within their own environments. Have you leveraged the UserInfoGet object in your Dynamics GP customizations? What unique challenges or successes have you encountered? Share your experiences and insights in the comments below!