Using Microsoft Access to Create Reports for Dynamics GP

If you are a Microsoft Dynamics GP user, you are most likely using Microsoft Office. One of the components of the Office Professional Edition is Microsoft Access, which you can use to create your own databases or connect to existing databases, including those in GP.

Microsoft Access 1.0 was released in 1992. It was very popular as a way to create simple databases tracking items like your wine or records (remember those?), but it also could connect to other databases such as Btrieve. Access quickly became a popular tool for working with Btrieve-based applications such as Great Plains for DOS. Your faithful author has been using Access since those days.

Additionally, several business applications have been written using Access. Some of our clients have used these Access-based applications and have acquired a lot of knowledge creating queries and reports based on Access data.

This brings up the question:  Can I use Access with GP? Yes! This article will give you some insight on how to use Access securely to create reports for GP.

Note:  You must already have knowledge of how to use Access. This article isn’t going to teach you how to use the product. (Feel free to contact our Support Center, however, if you’d like to discuss ways of learning!)

Security

Before you can start writing reports, you must be able to access the GP database. The first step is to have a database administrator set up an SQL login for your network login. GP user passwords are encrypted, so you can’t use your GP user with Access or any outside application, save FRx.

When your administrator sets up your SQL login, he can use your Windows domain login and assign it to one or more company databases. To grant you access to the necessary tables, you can use the database roles created by Dynamics, which start with “rpt_.” An administrator always can create additional roles to fit your needs. Please read the security planning document available from Microsoft for more information. The rpt roles all have SELECT access to the appropriate tables.

DO NOT assign users to the DYNGRP role.

We’re going to look at a user named callen who will be assigned the role of rpt_accounts payable coordinator. Here is a screenshot of the security in SQL Server Management Studio.

ODBC

You will also need an ODBC connection using Integrated Security. You can set up a single ODBC connection for all databases or make separate ODBC connections for each database. Setting up the ODBC connection is very similar to setting it up for GP, with the exception of the default database and security options, as shown here.

Linking to Tables

Now that you have an ODBC connection and have been assigned security to the database, you can start linking the tables you want to use. By linking to the tables, your tables will be refreshable. You can import the tables instead, but the data would not be refreshable, meaning you’d have to import it each time you want new data.

Click the External Data tab and click the ODBC Database icon on the toolbar. Select the ODBC DSN created above and you should see the tables you have the rights to see. In our case, the user is only able to see a few tables that are payables-related.

Note:  You are seeing the physical table names, e.g., PM00200, instead of the easier-to-understand display names (PM Vendor MSTR). Using the SDK or Resource Descriptions in GP, you can determine the tables you need to link for your particular report.

Creating a Query

While this training does not cover how to use Access, be aware of one important piece of information. The security mentioned above only grants select access to the tables, meaning you can view data but cannot add, remove or change it. Shown here is a listing from PM00200, the vendor master table.

When typing into a cell, you can press the tab key to leave the cell and it appears your changes have been made. For example, notice “TEST” appears in the VENDNAME column on the first line. However, if you press the down arrow to go to another record or line, notice the message below.

This tells you that you can’t update, i.e., change, the value in the VENDNAME column. Press the escape key on your keyboard to put the value of the VENDNAME field back the way it was.

For more information or for assistance with Access and Dynamics GP, contact our Dynamics GP support center at gpsupport@bkd.com.

Leave a Reply

Your email address will not be published. Required fields are marked *