Access / Database / Visual Basic / SQL Server

Microsoft claims that Access provides a powerful set of tools that are sophisticated enough for professional developers, yet easy to learn for new users. As an advanced Access database programmer, I have found that this is true, yet many people have begun projects and while quite pleased with the speed of forms creation, often get tangled up in the area of queries that are beyond one table in scope. Access, when used within its limits is quite capable of creating powerful database solutions that organize and share information among up to 10 or 12 simultaneous users. If you outgrow that, there is an easy path (i.e. the Access Upsizing Wizard) to upgrade your data to a SQL Server database while preserving your front-end investment (forms, queries, reports).

Microsoft Access is a great choice of database. It is widely used and is the most popular database package you can purchase in the world. There are competitors to this package but the ease of use and its wide userbase makes it a winner. Additionally, there are numerous resources on the internet for Access, including tips, training, and tutorials.

Access has VBA (Visual Basic for Applications) as its underlying programming code. However, there is much you can do without using VBA. It was designed so the beginner can make good use of it but at the same time it has enough power the serious developer.

As part of Microsoft Office, the Access database is found in most offices throughout the country. I find that it has become the standard by which other database packages are judged. This is hardly surprising considering the dominance of Microsoft in the office software arena.

There have been many instances where, in my role as an advanced Access database programmer for clients in NH and MA, I have brought my skills to bear on existing projects. In all cases, this involves programming in Visual Basic as "code-behind" to get the job done.

Microsoft Access Development

My exclusive database development platform since 1994 includes all versions: starting with Access 2.0, Access 95, Access 97, Access 2000, Access 2002, Access 2003, Access 2007, and the current version--Access 2010

Specifically, I use Access to...

• Link to business data in a variety of formats (Access, SQL Server, Oracle, an others.)
• Program for multiuser access to enterprise data
• Add ease of navigation via menus
• Link to external documents (photos, documents, designs, user guides, MSDS, etc
• Optimization for speed of queries and reports
• Drill-downs and analysis of your data

As an Access database programmer, I am a regular member and presenter at the Visual Basic .NET, and Boston DotNet Developer's groups that meet in Nashua, NH and at Microsoft's Waltham offices. I read (analytically) several current journals, visit technical websites, and take advantage of the wide variety of developer support for Microsoft Access .

BIG NEWS: Access on the Web is a reality! This is the technology breakthrough we have been waiting for. It arrived with the combination of Access 2010 plus Sharepoint 2010. We now have a seamless method of getting Access to work on the Web, VBA and all, no strings attached--just the way I always wanted it!!
View this video for in-depth description of this technology then call to discuss with John (603-926-6611).



Abstracts of my major projects follow.

Rhode Island Hospital/Mt Sinai Hospital (Bruce Sands, MD) Providence, RI
Access Developer

Before we began this project, code-named OSCCAR, I first had to get HIPAA certified. This is a ten-year medical research project under a grant to study around 500 patients that suffer from inflammatory bowel disease (Crohn's Disease or ulcerative colitis). We imported all existing data for the first three years and incorporated it into an Access 2010 dual file format. Patients must undergo phone and in-person followups of their treament until 2018 and all details are tracked to satisfy grant requirements for the Crohn's & Colitis Foundation of America. Followup tracking and many other reports along ethnic, race, age and cooperation lines were developed.

Costa Fruit and Produce Boston, MA
Access Programmer

Costa is the largest supplier of products to stores and restaurants in the Greater Boston area. I wrote a complex Costing programn in Access 2010 to replace an aging Wang application. It is very much like a Bill-of-materials task incorporating products at 3 to 4 levels of assembly, labor, waste, packaging, and even down to the label.

Jaro Corp. Ipswich, MA
Access Programmer and Consultant

Jaro Corp, in the industrial sector does large scale coating and recoating of printed circuit boards. Their existing procedures for tracking quotes, orders, and jobs were being maintained in Excel, MS Works, and Access, We created a new Access 20xx application to handle these tasks. It is now in production and we are currently developing related reports.

FPI Inc.(Jonathan Pond, LLC) Newton, MA
Access Programmer; Crystal Reports

Jonathan is a nationally known financial planner with close affiliations with AARP. For many years, his firm has produced a very substantial and complete set of financial reports and advice based on extensive questionnaires sent in by conference attendees as well as respondants to public TV presentations. Done in VB6 and Borland, the report generation became very slow compared to current technology, but more seriously was breaking down. They lost support from the prior programmer and I was called. On my advice, the firm purchased new hardware and the current version of Microsoft Office. I set them up with a LogMeIn connection so I could transfer editions of my work to their site frequently. This became a content management project with dependance on complex combinations of responses concerning age, family, investments, retirement plans, risk preference and many other items. In some cases one of 50 possible responses were determined to go into a particula report section.

Costa Fruit and Produce Boston, MA
Access Programmer
Joe Walsh

Created a new Access application to handle complex costing for the biggest distributo in Boston. At three levels of assembly of fruit and produce items, costs must be rolled up and are affected by quite a variety of selectable factors along the way. We are now developing a complete reporting system.

State Street Bank Boston, MA
Access Programmer/Developer

I offer continuing support for Melissa's HR project in Access 2003. Over the past several years, there there has arisen the need to change the way that the study of job functions are compensated. These calculations vary geographically as well as by country and salary band. Heavy mathematical internals prevail.

Private Business Jets Hingham, MA
Access Database Programmer/Developer
June 2007 - Now

This company arranges charter jet trips for high profile individuals and groups from corporation executives to rock stars. Their distinction is that they are able to draw on their ten years of experience in order to quote the requestor immediately on hearing the trip's specifications. Then, they solve by calling carriers of planes/private pilots they have used before, locating planes setting idle away from their base or with empty legs, and as a last resort hooking up a single leg via commercial jets.

Upon completion of this application and placing it into production, the client reported that it cut typical trip solve time from 4 hours to twenty minutes and added a higher degree of certainty to each job's profitability.

Maximus Hartford, CT, (also NY)
Access Database Programmer/Consultant
Dec 2006 - Now

Maximus is a substantial IT consulting firm serving primarily the government sector. As part of their team working for the State of Connecticut's Department of Social Services (DSS), I created a program in Access 2003 to replace a large and intricate set of Excel spreadsheets that managed Temporary Assistance to Needy Families (TANF) as well as Child Care and Development Fund (CCDF).

There are 12 Agencies involved in this within the Connecticut DSS with names such as Dept. of Children and Families, Dept. of Corrections, Dept of Labor, Judicial, Office of State Treasurer, and so on. Each has many programs and subprograms and are served by many providers. The need was to unify all this data and allow systematic quarterly imports of data from many sources to replace all the manual spreadsheet manipulations that went on before.

An additional task was to create complex Access forms that allocate monies from various funding streams to these programs and then estimate what would be required for the following fiscal year. The program also distills all the actual distributions into two formal annual reports named ACF-196 (TANF) and ACF-696 (CCDF).

Disetronics Sterile Products Portsmouth, NH
Access Database Programmer
Mar 2007 - Now

Disetronic is a subsidiary of Hoffmann LaRoche. In a clean-room environment, they manufacture clinical devices, especially insulin pumps for diabetics. They had a pressing need to improve the tracking of numbered worklots to track the entire manufacturing cycle. We divided the4 work into two major tasks: Warehousing and Production.

As Einstein once said about hypotheses, "Make it as simple as possible, but no simpler."Before doing any coding, we spent the first week just designing the data schema needed to support both sides of their world. With this careful analysis, we were able to do the job with a minimal number of tables. The schema was reduced to a core set named tblLotMaster, tblWarehouse, tblItemMaster, tblSuppliers, tblTransactionLog, and just a few other lookup tables. .

Accurate Brazing, Inc, Goffstown, NH
Access Database Programmer and Consultant
Oct 2006 - Now

Bud had created a very extensive set of operational and financial programs in Access 95. Deployed on 15 PCs throughout the plant, the code was getting severely out of shape because of the variety of operating systems and hardware that it was being asked to run on. It was overdue for an upgrade. Using my offsite lab, I upgraded 4 front-ends plus 4 back-end .mdbs all the way to Access 2003 with no intermediate versions. As is typical, the data went through this process fine, but there were severe anomalies (reported as VBA corruptions) with the Forms and Reports modules. With care and singling out the bad actors for special treatment, the whole set of programs was put into production within three weeks from start.

PAX World Funds, Portsmouth, NH, (also Maine, NY)
Visual Basic/Access Database Programmer
Jun 2006 - Aug 2006 and
Jul 2003 - Dec 2003

This major mutual fund company hired me to finish the work started by a previous Access database programmer. The program tracks commissions owed to brokers all across the country on all kinds of mutual fund transactions. A quarterly import of data is required and I automated this to make life easy for the administrator (Michelle). I later took on Access projects in several other departments including a critical one, Social Screening that is the core of the firm's specialty,. PAX continues to use me for general database support.

ProTracker Software, Hampton, NH
Visual Basic.NET ASP.NET Programmer and Consultant
Contact: Warren Mackensen 603-926-8085
Jul 2004 - Oct 2004

This specialized software is marketed nationwide to Certified Financial Planning (CFP) firms. The owner, Warren has an excellent reputation, being a recognized "guru" in the industry and a frequent speaker at conventions. I worked on the internal database issues as well as a significant amount of work on the DotNet ECommerce web site the ProTracker uses to market the product. This included downloading data from a web service, working with common dotnet server controls, linking to the data on a remote SQL Server, and plenty of old-fashioned HTML coding.

UNITIL Corp, Hampton, NH
Visual Basic/Access Database Programmer and Analyst
May 2004 - Jun 2006

Unitil Corporation is a public utility holding company supplying gas and electric services. I took up where a previous programmer left off in a complex Access program This is designed to track the monetary benefits of users who voluntarily upgrade their residential or commercial energy equipment. This program handles frequent vendor data imports in a wide variety of formats, from ascii (.txt), Access (.mdb), Excel (.xls), and dbase IV (.dbf). The vendors as well as the users of the equipment are tracked and energy savings are calculated in this program.

Fisher Scientific World Headquarters, Hampton, NH
VB/Access Programmer and Consultant
Dec 2003 - Jun 2004

Fisher had a bunch of databases containing the results of multiple Quarterly Customer Satisfaction Surveys. They needed a programmer to unify this data so it could be properly queried and reported on and so information across quarters and across divisions would make sense. I have merged all data into one set of normalized tables and am proceeding to rewrite all queries and reports to address the new schema. We are doing this in such a way that an eventual merge of this "seed" data will easily transform into a full CRM system by means of migration into a Siebel ODS (Operational Data Store). Contact: Dierdre Sommerkamp 603-929-2517

Strafford County Dover, NH
Access and XML Consultant
Oct 2003 - Feb 2004

A new mandate in the State of New Hampshire requires all agencies (>300) to begin reporting all pension payroll deductions and related information for all state employees via a file in XML format. Strafford County has 310 employees to report on and the resulting XML rendition is over 4000 lines long. Roger located me from a web search (oracle programmer nh) and I did the job using Visual Basic.
It turns out that we were the only ones to get our file validated within the deadline. All other agencies used the fallback approach of fixed width ascii or CSV or they begged for more time.

Tyco International Exeter, NH, Portland, Maine
Access Database Programmer (Access 2002)
May 2003 - Jun 2003

A local programming house named Compass Systems and Programming created an Employee Performance program in Access that is deployed at 100 worldwide regions. There were two very different versions, one for upper management and one for everyone else. I merged the two code bodies and reproduced all the pre-existing functionality.

Just for fun, I changed some employees names and ids and published a subset of the data to our password protected .NET website in an editable datagrid. I did this to demonstrate our skills with DotNet and to show the value of centralized, up-to-date, and secure data. Currently, like so many others, Tyco exchanges data between regions via EMail attachment of Excel spreadsheets.

Acme Brick Company, Houston, TX
.NET Framework 1.1 Consultant, VB.NET, ADO.NET
Feb 2004 - May 2004

A large brick plant near Houston, TX located us by searching for our skillset on the Web. We created a quality control program for their production engineer using DotNet technologies that monitored a high resolution video camera. This camera was continually focused on a production line of precured and precut bricks called a "slug". Periodically, the line operator clicks a button to take a picture of the line going by at 1 foot per second. Our Visual Basic DotNet solution grabs a frame from the continuous video stream and displays it on a color monitor side by side with a picture of a known high quality standard for that particular style of brick. Depending on the visual comparison, the operator can take appropriate action such as calling for a change in mixture or other operating parameters. If quality is good, he simply does nothing. We log the picture taking events in order to track the operator taking pictures on a prescribed frequency. Hidden from the operator, there is a secured maintenance module so the engineer can periodically check the log, add new standards, tune the camera settings, and perform other data management features.

Genesys Software Corp, Methuen, MA
Visual Basic, Crystal Reports, SQL Server 2000
Jan 2003 to Feb 2003

Program: People Come First -- a Web-based Human Resources program.
As part of developing enterprise reporting solutions, I developed reports using Crystal Reports Developer (Ver 8.5) against a complex MS SQL Server 2000 database.

GSC clients are Fortune 500 companies as well as other sites such as state and county governments who have a need for extensive reporting on a wide range of employee activities. The client installations I worked on were in the area of employee education. We built a central report repository, maintained tight user security, used report scheduling, and sought accurate and speedy report processing.

As part of a tight and highly skilled development team of 5, I helped provide a scalable web-based solution for managing the access and delivery of hundreds of Crystal reports across the enterprise. These client reports had to be carefully constructed and often rested on SQL joins of up to 20 tables. A very important aspect was speed optimization. The query (view) underlying a particular report was written to take advantage of server-side processing. For example, directing that grouping take place on the server and that execution of the reports be based on data saved with the report, rather than suffering multi-user hits to the server for live data every time. This data was refreshed to the report during off-peak hours.

Another optimization was the use of stored procedures as data sources. Although these take time to set up properly, they can be incredibly powerful, especially when running reports off large databases.

In the interest of cross-skills enhancement on the development team, I conducted Crystal Reports classes (Basic and Intermediate.)

State of NH - Dept of Education, Concord, NH
Consultant / Coder ( Visual Basic / Access Programmer
Jan, 2000 to Jul 2001

The DOE needed a database programmer in NH to develop a brand new program to deal with a complex new tracking system mandated by the Federal Dept. of Labor. This legislation that created this national system was the Work Incentive Act (WIA) so that is what we named the State of NH response to it. My program tracks out of work participants and a myriad of details about them while they are involved in Job Training and other educational programs to improve their employability. Many business rules and extensive exporting and reporting at the state and federal level. Periodically transmitted the NH data in formatted records of over 3000 bytes to Dept. of Labor main database which collects data for all the states.

...and many other database programming projects going back into the mid 1990s.
Contact: Bonnie St. Jean 603-271-3805

back to top