Why Great Plains and Access Integration Matters for Modern Small‑Business IT
Microsoft’s Great Plains (GP) continues to dominate the U.S. market for accounting and ERP, especially among midsized firms that need a flexible, on‑premise solution. In Europe the brand has shifted toward Navision, while the high‑end enterprise line, Dynamics Axapta, competes with Oracle, SAP, and IBM. Regardless of the region, the core problem that developers face is the same: how to keep GP in sync with other Microsoft tools that smaller businesses rely on - most notably Microsoft Access.
Access is the go‑to platform for lightweight data entry, reporting, and quick prototyping. It offers a familiar interface and ODBC connectivity that lets developers query any database without installing heavy middleware. But GP stores data in a complex relational model that evolves with each release, and the data flow logic is tightly coupled to the GP application engine. Direct manipulation of GP tables risks corrupting business rules or breaking future upgrades. For that reason the community has identified a handful of proven integration paths that preserve GP’s integrity while still letting Access work with the same data.
At its core, an integration strategy for GP and Access must answer three questions: How do you transfer master records (customers, vendors, employees) into GP? How do you push transactions (sales orders, invoices, payments) from Access into GP’s work tables? And how do you keep reporting and analytics in Access up to date without touching GP’s core tables? The answer is a layered approach that uses the built‑in Integration Manager for quick, low‑volume tasks, eConnect for ongoing, programmatic flows, direct SQL for fine‑grained control, and ETL tools for batch processing.
Developers who have been working with GP for a few years already understand that the data dictionary - DYNAMICS.DIC - holds the schema and business logic definitions. Even Small Business Manager, built on the same engine, follows that dictionary, meaning the same integration techniques apply across the board. The key is to respect the separation of work tables (which hold pending or historical data) and master tables (which hold current records). By keeping your integration logic on the work tables you allow GP to perform its validation and posting without any risk of data corruption.
Beyond the technical details, the real advantage of integrating GP with Access is the speed of delivery. Access developers can build front‑end applications in minutes, while GP updates happen behind the scenes through the same secure interfaces. The result is a hybrid solution that combines the agility of Access with the robustness of GP, letting small businesses scale without a full‑blown ERP implementation. The following sections dive into the specific tools and techniques that make this hybrid architecture possible.
Integration Manager: The End‑User Tool That Keeps Data Consistent
The GP Integration Manager is the most approachable entry point for developers who need to move data between GP and external sources. Built into GP, it presents a wizard‑style interface that lets you select master tables - such as RM00101 for customers or RM00102 for vendors - and map incoming files to those tables. The same mechanism works for work tables, like SOP30200 for sales history, allowing you to stage transactions before posting.
Because the manager runs within the GP Windows environment - even when you invoke it programmatically via VBA - it respects all of GP’s validation rules. Every field goes through the same business logic that a human operator would see on the GP screens. This means you’ll never have to worry about orphaned records or invalid data sneaking into the system.
Performance is the manager’s biggest limitation. For small volumes (hundreds of records) it is fast enough to handle daily imports. However, when you start dealing with thousands or tens of thousands of rows, the underlying GP engine becomes the bottleneck. The manager processes records one by one, and each record triggers a full validation cycle. This serial approach can take several minutes for a medium‑size file.
Developers can mitigate this overhead by breaking large imports into smaller batches or by leveraging the manager’s ability to call custom VBA routines. For example, you can write a VBA macro that reads a CSV file into a temporary Access table, then loops through the records, calling the Integration Manager’s ProcessFile method for each chunk. This keeps the transaction scope manageable and reduces the chance of timeouts.
Another advantage of Integration Manager is its ODBC friendliness. Because Access exposes ODBC drivers, you can write a simple SELECT query against the manager’s staging tables and pull the data into an Access form or report. The reverse is also possible: you can write to an ODBC‑connected Access table, then trigger the manager to import the new rows into GP. This two‑way flow is particularly useful for periodic batch jobs.
In short, Integration Manager is ideal for developers who need a quick, reliable method to keep GP’s master data and small transaction volumes in sync. Its built‑in validation ensures data integrity, while its VBA hooks give enough flexibility to build more complex workflows without writing extensive code. The trade‑off is speed, so for high‑volume scenarios you’ll want to look at eConnect or direct SQL techniques.
eConnect: The SDK That Powers Ongoing Integration Workflows
Microsoft’s eConnect is a software development kit that exposes GP’s business logic through a set of APIs. Developed in .NET, it requires Visual Studio as the development environment. The SDK provides a collection of typed classes that map directly to GP’s work tables. For example, the SOP30200 class represents the Sales History Header, while RM00101 maps to the Customer Master file.
Using eConnect, you can programmatically create new master records - customers, vendors, employees - or stage transactions that will later be posted by GP. The SDK also lets you query existing data, retrieve status updates, and trigger posting operations. Because eConnect sits on top of GP’s core logic, it guarantees that every record you push through it follows the same validation rules as if it had been entered via the GP UI.
Developers often use eConnect for e‑commerce integrations, where an online store submits orders that must appear in GP for invoicing and inventory updates. The SDK supports XML and SOAP messaging, so you can expose your own web services that accept order data in a standard format and translate it into eConnect calls. This approach keeps the external system agnostic to GP’s internal structures.
When working with eConnect, it’s essential to understand the difference between master records and work tables. You cannot post an existing transaction directly into GP using eConnect; you must first insert it into a work table, then call the PostWorkTable method to let GP process and archive the data. This pattern mirrors the GP UI, ensuring that all audit trails and journal entries are properly generated.
The SDK also offers event hooks and error handling mechanisms. Each eConnect call returns a response object that includes success status, error codes, and detailed messages. This level of granularity is invaluable for troubleshooting and logging, especially in production environments where a failed transaction can have cascading effects.
While eConnect provides a robust foundation for long‑term integration, it does require a deeper learning curve than Integration Manager. You need to be comfortable with .NET, object‑oriented design, and XML handling. However, the payoff is a scalable, maintainable solution that can handle thousands of records per day without compromising GP’s stability.
Direct SQL and Stored Procedures: The Most Granular Level of Control
For developers who need fine‑tuned control over GP’s data flow, writing raw SQL queries and stored procedures against GP’s tables is a powerful option. The first step is to familiarize yourself with the data dictionary. Launch GP, navigate to Tools > Resource Description > Tables, and locate the table of interest. For example, RM00101 holds customer master data, while SOP30200 stores sales history headers.
Each GP table contains a unique identity column, typically named DEX_ROW_ID. This column ensures that each row can be referenced reliably, even across database migrations or replication scenarios. When writing stored procedures, always include DEX_ROW_ID in your SELECT statements to avoid ambiguity.
Direct SQL is particularly useful for complex queries that involve joins across multiple work tables. For instance, you might write a stored procedure that aggregates sales data per customer, grouping by RM00101.CUST_NO and joining with SOP30200 on ORD_NO. Once the procedure is defined, you can expose it as a web service or call it directly from Access using ADO.
Because GP tables are not designed to be updated arbitrarily, the stored procedures should perform updates only on work tables. Never attempt to modify master tables directly through SQL unless you have a clear understanding of the impact on GP’s business logic. If you need to add a custom field, the recommended approach is to create an extension table that links to the master table via DEX_ROW_ID rather than altering the existing schema.
Many developers find it convenient to wrap complex SQL logic in XML parameters. For example, you can create a stored procedure that accepts an XML payload containing multiple customer records, then parses the XML to insert each record into RM00101. This pattern is handy when integrating with external systems that transmit data in XML format.
Deploying a web service as an intermediary between Access and GP is another common strategy. The service receives requests from Access, validates the data, then calls the appropriate stored procedure. Because the service runs on the same server as GP, you avoid network latency and maintain a secure, isolated environment for data transformation.
In summary, direct SQL and stored procedures provide the most granular level of integration control. They allow developers to craft sophisticated queries and batch operations, but they also demand a thorough understanding of GP’s data architecture and strict adherence to best practices to avoid corrupting the database.
ETL, Custom Screens, and the Final Integration Checklist
When batch processing or EDI integration is required, SQL Server Integration Services (SSIS) – previously known as Data Transformation Services (DTS) – is a natural fit. SSIS lets you extract data from a variety of sources, transform it through a visual workflow, and load it into GP staging tables. The same pipeline can be reused for EDI exports, where the transformed data is sent to a partner’s system via XML, flat files, or other protocols.
Linked Servers provide another layer of flexibility. By configuring a SQL Server linked server to connect to an Access database, you can run OPENROWSET queries that pull data directly from Access into SQL Server or GP. This is especially useful for cross‑platform reporting, where Crystal Reports can aggregate data from both sources in a single report.
For users who prefer a GUI approach, Dynamics Dexterity remains a viable tool, though Microsoft is gradually phasing it out. Dexterity allows developers to create custom screens that sit inside GP, enabling parameters to be set and integration processes to be started with a single click. The key is to build a new screen rather than modifying an existing one, so that future GP upgrades do not overwrite your changes.
No comments yet. Be the first to comment!