Great Plains DOS Exporting Data via ODBC connection or Pervasive SQL Control Center

There are numerous GPA installations frozen for data inquiry only, this is also helpful if you may expect future audit.  From time to time we hear requests to open GPA database for ODBC connection, typically when your IT programmer has the project to create several reports out of the old accounting database.  Of course, internet is the place where you are looking for free advice or instructions.  We’ll try to give you both:

1. DDF files.  Btrieve opens its tables via so called Data Definition Files (DDF).  If you still have your old GPA installation media, diskettes, please read Btrieve manuals, available on documentation folder on your Great Plains Accounting CD.  There you should be able to find instructions on DDF files creation for your version of the Accounting application.  There is of course some inconvenience, as you will have to mount floppy drive, and run DDF generation from one of the installation floppies

2. Pervasive SQL 2000 Server and Client installation.  We have not tested GP for DOS on 64 bit platform, we know that it is compatible with 32 bit Windows XP, Vista or Windows 7.  We also tested ODBC connection for earlier versions of Pervasive SQL 2000 on 32 bit operating system.  Most of the computers sold in the warehouses are on 64 bit Windows 7.  We recommend you to get something really tiny, netbook on Intel Atom with Windows XP 32 bit, for example to do the job.  Go ahead and install Pervasive SQL 2000 Server and Workstation (Pervasive Control Center with SQL query tool).  Then, be sure, that you copied DDF files into GPData folder (or if you have multiple companies, in the root of the targeted company).  Open Pervasive Control Center and create new database with your GPA company name and specify the folder, when transferred to ODBC DDF creation, check DDF files available checkbox.  If you did everything right, new DB will be created and you are now able to see its tables listed in the right panel

3. ODBC connection from Crystal Reports, MS Access, Excel.  Please, note that there are older modules of GPA where it was deploying one Btrieve file per table (GL, Accounts Receivable, Accounts Payable) and newer modules, where several tables might be hosted in single Btrieve file (Order Entry module, for example).  One table-file Btrieve is open for being connected via ODBC, including Crystal, MS Access.  For multitable hosting files there is the chance that ODBC driver will give you error message in connection and you have to do the job directly in Pervasive SQL Control Center Query Screen.  Here the technology is the following.  Try first exporting table into the text file and import it into Excel, or Access, but chances are high, that you will get into column shifting mess.  If this approach doesn’t work, try creating SQL query, where you are selecting only required columns (please, be aware that date columns might be screwed in your DB, especially when you see error message in ODBC connection “Invalid Date”).  Through Pervasive SQL Control Center the goal is to export your table into the text file (with the good columns only) and then import this text into MS SQL, Access, MySQL, Oracle, or another database platform for reporting purposes

4. If you think that this is too complex…  Well, we are here to help you out.  Feel free to call us 1-866-304-3265 or email help@efaru.com