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):
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)