These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 16, 2013

Database Table Editor for Excel

Executive Summary
            This project is a database table editor that is geared toward those who use databases heavily, and in particular those who frequently insert, update, or delete from tables. The business purpose grew from needs that I had as an Implementation Consultant for government Enterprise Resource Planning (ERP) systems. A large part of my job was to configure database tables that served as references for how the rest of the system operated. As more groups within the government agencies were brought into the system and the level of maintenance increased, we frequently copied existing configuration and made modifications as necessary to accommodate the new groups. The modifications were frequently done in Excel due to its flexibility in writing formulas to generate the changes that were needed.
            Our system database for several years has been SQL Server 2008, and we frequently copied and pasted directly from Excel into SQL Server. I began to run into limits with this method when dealing with several thousand inserts, where copying nearly 13,000 rows took approximately 15 minutes to accomplish. In order to deal with this I would write Excel formulas to generate insert scripts which could be run in SQL Server Management Studio in less than 20 seconds. While this was a huge increase in performance, the downside was the need for me to write a new formula for generating scripts for every table. Based on the bottlenecks described, this project is meant to simplify two processes:

  1. Copying data from Excel to the database.
  2. Generating insert scripts for running directly in a database GUI.

No comments:

Post a Comment

Blog Archive