Home

ACE Project Systems

Tools for better Project control, making your life easier

Read, Edit and Write data in
SQL Server using Excel

SQL Server is a fantastic datbase for storing data. Power BI and other tools are great at reporting on data stored in SQL Server.

However you can't update, add or delete data in SQL Server. This is where our Excel VBA comes in. With a basic set of code and a hidden spreadsheet to link column headings to their SQL field name and you can see large tables of data in Excel, edit it, copy rows and flag rows for deletion tehn update SQL Server with a click of a button.

The data is displayed in Excel using Excel's Table feature. This gives you built in, familiar, easy, sorting and filtering options so no training needed.

Reasons for using Excel as a front end for your Database data

  • Excel is familiar and provides no resistance when its time to roll out your solution.
  • Excel is a fast and easy solution to roll out and update.
  • Views in SQL Server define what fields are shown and the column titles.

It's more of a question of "What do I want to do?" rather than "What can I do?"

FAQs on Excel as a Front End to SQL Server

  • Are macros written from scratch?
    No. We have a library of functional objects (such as all communications with SQL Server) so each new system typically starts with 80% of its code copied from tried and tested systems that have been working for many years.
  • Do we Own the code?
    No, but you have a lifetimg license to use it.
  • What if I need to add more data items?
    That's quick and easy. Usually adding a column or changing its title is just a matter of editing the View used for copying the data to Excel. For more complex changes, VBA development is fast.
  • Will any version of SQL Server work?
    Version 2012 or later and even the free SQL Server Express will work.
  • How fast is the refresh and update funcitons?
    The refresh is faster than web browsers showing the same amount of data. Why? because web pages usually have to download the data and all formatting code then interpret it to display the result. Our Excel code just has to download data, Excel has already done the formatting interpretationand work.

Contact us to discuss how we can help you.