Visual Basic 5 - Peachtree Utility

Todd Boyle - tboyle@rosehill.net

Visual Basic 5 - "GL" Peachtree Utility
Todd Boyle - tboyle@rosehill.net

Requirements

Peachtree Complete Accounting for Windows Ver. 3.5 thru 7.0 running under Windows 95/98/NT4, Visual Basic version 5/6 (the code would also run under MS Access 97 with minor modifications)

Overview

This is a read-only utility which can accompany Peachtree or Quickbooks companies.  It provides a full screen table browsing capability based on the VB5 DBgrid.  This will look very similar to Microsoft Access, except that the program has several modest improvements over Access:

The program requires you to export 11 data files from Peachtree by using the File/Export menu. It then imports each of the separate transaction journals from the Peachtree export files into a Microsoft Access database (MDB file) and combines them (denormalizes them) into a single transaction table.  In other words, it creates a flat file view of Peachtree data.

The program also maintains Vendor Lists and Customer Lists in its database. There are functions for Bi-directional synchronization of changes in Vendors and Customers in both directions between the MS Access environment, and Peachtree. In other words, if you have a business application residing in the Access environment where users Add,Edit or Delete customers or vendors, this program can create CSV files for importing them into Peachtree as well as merging the changes made in the Accounting department, in Peachtree back out to the Access database.

The transaction browsing and other features of the program are generally usable as an add-on to any general ledger. MS Access has many advantages as a repository of accounting data, or a bridge between a particular client's G/L and other software.

Following are typical sources of transactions imported into small business general ledgers like Peachtree (or onward destinations for date exported from the ledgers):

  1. the business system of the company (billing, point of sales, production, etc.)
  2. a better reporting or querying tool,
  3. a consolidation or corporate reporting tool such as Hyperion or Comshare's FDC Commander, or
  4. another instance of the same GL package, serving as a consolidated ledger, ledgers in different currencies, archival prior year ledger, etc.

This Visual Basic 5.0 application serves as a basic example, configured to run against Peachtree. I've also configured it as a standalone General Ledger, or running alongside Quickbooks, SunAccounts, MAS90 level 3, and AccPac 6.2/DOS. You are welcome to use it for free.

The program is basically a flatfile General Ledger in the 32-bit MS Access JET database. It is a full-screen spreadsheet type "browser" which really makes it easy and fast to surf and review the transactions. The program is straight Visual Basic code attached to command buttons, for various filters, indexes, etc. The only imports provided are a bunch of SQL for Peachtree Complete Accounting, importing the standard Peachtree export ASCII files for General, Sales, Purchasing, Cash Recpts Journal, etc. and a single, barebones import function for Quickbooks (based on the Transaction report printed to a CSV file)

The data import works by "Attaching" the eleven or more Peachtree ASCII files as database tables, and then "Opening" them and running queries to import all the transactions from the Peachtree journals into my flatfile transaction database.  Read the example SQL text included in the zipfile.

The program solves the thorny problem of getting data from Peachtre's multiple transaction journals. Once in a database, you can query, report, or export onwards to bigger systems. The program displays each of its numerous SQL statements that import data, build multi-column reporting tables with different months in each column, etc in a text box. These period-comparison reports with various filters and sorting are not available in the low-end general ledgers. For example, with Crystal Reports template provided in this program, you can report income and expenses by Customer, Employee, Vendor, or Job Code for each month of the year in multicolumn layout, by month.

I created the SQL Queries for reporting, by running the MS Access query grid, to design each query I wanted. Then I switched to the SQL view and copied the SQL command to the clipboard, and pasted it into my Visual Basic program, maybe with a little bit of editing. You can do this too. It's easy.

By displaying all of its SQL commands in text boxes or other displays,the program is a good learning tool, and makes the program very easy to modify. I built the SQL display windows to make it easier to adapt for different source ledgers. Basically you copy SQL out of my program to MS Access, modify its behavior, and paste it back into my program.

With this sort of Visual Basic tool, a large company could run Peachtree as the General Ledger in each of their subsidiaries in various countries, and perform multicompany, multicurrency consolidations in MS Access in the home office. With a VB program you have the possibility of accumulating the ledgers into a consolidated database where you can drill down, to see the ultimate detail from every country.


How to import data from a Peachtree company to MS Access:

1. Create a directory C:\Access\BCP and Unzip all of the files from the zipfile BCPGL.ZIP into the directory. Copy the schema.ini to the C:\Access\ directory

2. Find your Berkeley Custom Pools sample company directory and replace the report file RptData.dat with the one I have provided in the zipfile BCPGL.ZIP. (You'll need this because it contains the saved Export templates with the names of the columns you need to export)

3. Enter Peachtree and open Berkeley Custom Pools.

4. Run the Data Export function from the File menu in Peachtree, to export every Export defiinition having uppercase names starting with the _ underbar character. There are eleven such export procedures, provided to you in the file REPORT.DAT described above. Note that these file export templates are hardcoded to export .CSV files into C:\access\BCP.
Sorry. This export job is a real nuisance and takes several minutes on typical company with lots of transactions.

5. Quit Peachtree and review the are 11 comma delimited CSV files in your C:\Access\BCP directory, along with the Visual Basic files, etc.

6. Double click on the file BCPGL.VBP. This should start up Visual Basic for you. click on the RUN button and run the program.

7. In the BCPGL program, choose FILE - IMPORT PEACHTREE. Follow the directions on screen, it's a bit weird but works fine. You can see all the SQL in the display window, as the importing is taking place or after it completes. Basically you tell it what date range to import and it blows away that range and replaces it with whatever it finds for that date range.

The program also has code to make copies of all transactions which it deletes, for safety if you use this in a production environment. I have commented out that program code since it isn't necessary for me.

9. To prepare financial statements, the "Financial Statements" menu must first be used to add up the reporting totals. Then the individual reports can be picked off the menu; these are crystal reports definitions.



LEDGER EXPORT FIELDS NEEDED TO SUPPORT "BCPGL" VISUAL BASIC MODULE:

The VB5 program imports accounting transactions from Peachtree Complete Accounting for Windows Ver.3.5-7 into an MS Access (Jet 3.5) database.

The operator exports the following Peachtree-format "CSV" files manually or programmatically each time the data needs to be imported to MS Access. Peachtree requires the user to identify the columns (i.e. data fields) desired before this can happen. It is about a 20 minute job to set up the export by choosing the desired columns. The user can then save the column layouts under any name desired; thus, the setup is only performed once.

The columns you need to export are shown in the .CSV files provided in my zipfile. For example, of the available General Journal columns, we must export the following:

Date
Reference
Number of Distributions
G/L Account
Amount
Job ID
Description

How to customize the BCPGL Visual Basic / Accounting Utility for your own companies:
------------------------------------------------
this assumes you're already running BCPGl successfully in the C:\Access\BCP subdirectory:
-------------------------------------------------

I have been running 5 to 10 companies actively at the same time, by putting multiple implementations in separate subdirectories. This lets you modify table structures, include additional features, queries, reports, etc. for each particular company.

Obviously the database is complicated ENOUGH having 20 -30 tables in the BCPGL.mdb without adding multiple companies and special queries for each one!

Steps to implement another company:

1. Create another subdirectory under C:\Access and name it as any 3-character company code resembling your company's name. For example I will arbitrarily use "ABC" throughout this doc. Copy all files from your "BCP" subdirectory to the c:\access\ABC\ directory, except the MDB databases.

Choose a good unique combination so that as you improve your modules, you will be able to globally replace it in the programs with a wordprocessor. For example, regardless of your company name, don't choose "DIM" or "dat" or other strings that appear extensively throughout your code base.

2. Drop to DOS or NT command prompt, and Rename BCP*.* ABC*.*

3. Use a good, binary file editor to perform a Global search and replace for every instance of "BCP" in every file in your new C:\ACCESS\ABC directory. Every BCP must be replaced in each text file (.frm, .vbp, .bas, etc) as well as binary files (Crystal reports RPT, Visual basic FRX, etc.)
This takes approx. 1 second with UltraEdit 32 (using the Global search in Files menu.) Sorry. I've found that coding OOP hasn't amortized too well, against 1 and 2-seconds of time saving.
Get UltraEdit 32 at Http://www.idmcomp.com. Ian, you're a genius.
4. Now that you have finished modifying all the files, it is safe to go to the BCP directory and copy the MDB files into the ABC directory
5. My program uses one DLL. Right click and drag the file 'RECCOUNT.DLL' and drop it on your file REGSVR32.EXE to register the DLL in your registry.

6. Copy the file "RptData.dat" containing all the ASCII export definitions for the 11 files we need, from your existing "BCP" directory to your "Peachw\ABC" directory. Following are the necessary definitions:

Peachtree Exports this file: Name of the Export Definition:
GENERAL.CSV _GENJ
SALES.CSV _SJ
RECEIPTS.CSV _CRJ
PURCHASE.CSV _PJ
PAYMENTS.CSV _PMTJ
PAYROLL.CSV _PAYR
CHART.CSV _CHART
CUSTOMER.CSV _CUST
EMPLOYEE.CSV _EMP
JOB.CSV _JOB
VENDOR.CSV _VENDOR


Copying "RptData.dat" is fast, but obviously, will blow away any existing custom report definitions and export definitions. If you want to retain your "RptData.dat" you can create the export definitions needed for the VB module by reading the above C:\access\BCP\*.CSV files with a text editor. (Each file contains the necessary column names on the first row of the file.)

8. Go into Peachtree and open your ABC company. Menu File-Export and see the eleven Export Definitions begninng with "_" such as "_CRJ" etc. You will do the same thing for every file: Click the Export button, change the subdirectory in the "Options" button from "BCP" to "ABC", Save your change, and then run the export. Tedious, but only a 10 minute job.

BI-DIRECTIONAL SYNCHRONIZATION of CUSTOMER LIST and VENDOR LIST

The user may create a new Customer within Peachtree at any time (for example, to issue an invoice). The user may also create customers within the VB program either by importing them from external lists or manually creating them. This synchronization function merges the VB Customer list and the Peachtree Customer list.

The main thing the user needs to remember is:

* NEVER create the same customer TWICE, in each system.
* You can create it in one place or the other, not both.

This synchronization function assumes that a current copy of the Peachtree customer list exists on disk in the CUSTOMER.CSV file. You must have just finished exporting the customer list by using the Peachtree File Export function, which creates the required CUSTOMER.CSV ascii file.

Please understand the two steps followd by the syncronization function:

STEP 1 -- Copying customers from Peachtree (PT) into Visual Basic (VB):

The synchronization function identifies each customer in PT by its Peachtree [Customer ID] code (usually an alphabetic mnemonic string like FREDMEYER with no spaces). The function looks in the VB Customer list, in the [PeachID] field, to see if the same customer code already exists in VB. If not, the newly discovered Peachtree customer is inserted into VB.

If a Customer record is found in VB where the [PeachID] code is equal to the above Peachtree code, the synchronization function does nothing. The function cannot guess which version of the customer is better, so it does nothing.

In other words, if you use Peachtree to update an address for an existing customer, the new address will be ignored unless he also goes into VB and deletes that customer to make it look like the Updated Customer in Peachtree is a new customer. The main thing the user needs to remember is:

* NEVER change the customer address or information in Peachtree unless
you either make the same change in VB manually, or delete that customer
from VB to force the synchronization to copy the entire customer to VB *

* WHENEVER a customer address changes, enter the data into VB and run the
synchronization function to push the change into Peachtree.

* TO KILL a customer record, the user must kill it both in Peachtree and VB;
if only one copy is killed, the survivor will be replicated to the other
database and you're right back where you started


STEP 2 -- Copying customers from Visual Basic (VB) into Peachtree (PT):

Now that the VB Database contains all customers from Peachtree, the function performs a complete export of the whole VB Customer database into an ASCII file for re-import into Peachtree. DO NOT NEGLECT TO IMPORT THE VB OUTPUT FILE BACK INTO PEACHTREE.

Existing *fields* of existing Peachtree customers are overwritten by the VB version of that field, every time this synchronization function is performed. The result of this process is that any new customers or changes to customer data made in VB, are automatically pushed into Peachtree. The user must remember that:

* THE MASTER COPY of the customer database is in VB not Peachtree.

Note that New Customers in VB are created with a blank spaces for their mnemonic [PeachID] (for example, if "Fred Meyer" is added to the VB Customer database, it would have a VB customer number but no PeachTree ID.

This synchronization function creates a Peachtree [Customer ID] for each new customer discovered at this time, by suggesting an Uppercase copy of the company's name with all spaces and quotes removed, and asking the user to confirm or modify if necessary. The creation of mnemonic Peachtree Customer IDs is most effective when done by the human user. The program will synthesize a new upper-case key, based on the first 16 characters of the VB CompanyName field, and then require User to agree or change it.

The creation of unique Peachtree Customer IDs for new customers discovered in the VB database is a somewhat risky process and this is why you shouldn't create customers in both PT and VB. Follow my advice and don't manually create them in both software. The synchronization program won't fail, but make sure you remember the correct Peachtree Customer ID or you'll end up with two records for the same customer and in no time you'll end up with invoices, collections, etc. on both accounts, a real mess and completely avoidable.

NOTE ABOUT COMMAS AND QUOTES

It has always been problematic to transfer data between programs in comma- delimited files. This has been true since the beginning of DOS. Commas, Quotes and Doublequotes are a real problem. DO NOT put commas and quotes into your data entries into Peachtree, if you want reliable data sharing via CSV files.

The BCPGL program has successfully imported the CSV files from Peachtree even if you create Customers, Vendors, Descriptions on transactions etc containing commas. (One measure to enable this is that the Description of General Journals, etc. is always the last field in my Export Definition.)

Peachtree however, cannot import anything with a comma or quote between the quotes of the comma-delimited field. Bizarrely, it can't really import its own exported customer or vendor lists if names contain commas.

Thus, altho commas in customers and vendors are supported in my VB program, when exporting to Peachtree (synchronizing), the commas and quotes will be stripped out.
On my website there is a utility for performing this task on any Ascii file (download CLEANCSV.zip, its small and easy)

-------------------

I offer no warranties that this is bug-free, production software.

I also apologise for the lack of documentation at this time. I don't mind answering questions, though. Plenty of people have helped me at times.

If you have any questions feel free to email me.

Todd Boyle - tboyle@rosehill.net 4/98

Download the freeware Visual Basic/Peachtree utility (260K zipfile)