Learn how to a build a cloud-first strategyRegister Now


Excel Update with SQL connection

Posted on 2014-07-11
Medium Priority
Last Modified: 2014-07-29
Here is a good one that I would need assistance with:

I have a  Roster with Badge Numbers and a Labor Class in a SQL DB.
I have an excel sheet (.Xlsm) that connects to the DB and pulls the labor Roster into a  Table.
Works fine
Sometimes the users have to edit the Excel Roster with a different Class for a specific Job.
If I need to add users to the Roster, I need to UPDATE the Table with the latest Roster Numbers ONLY.
For Example: The Table contains Badges 100 through 150. I added Badge 151.
When I execute a sub inside Excel, I need to add any new badges after 150 Only. If I just pull down the entire roster again, it will overwrite the changed Class values on the  table.

I would imagine I need to change  the query for an Update, and change the Connection to ReadOnly= False for the Excel sheet, but I am not sure.

Question by:BKennedy2008
1 Comment

Accepted Solution

Jaes Overley earned 2000 total points
ID: 40192401

Greetings! What I usually do is have two sheets, a Control sheet and a Data sheet. Both sheets have an ODBC link to the database that returns the data from Labor_Roster. The Control sheet can be hidden so the end user doesn't see it. I refresh the Control sheet prior to sending my UPDATE statement from Excel. I then use a VLOOKUP between the Control sheet and the Data sheet and only update the new rows. When allowing changes to current data, if data in a row changes, I update the current row in the table with an Active_Indicator column switching the old row to a 0 and then add the new and updated rows with a 1. This way you have a history of changes and can easily reverse mistakes. You can also check to make sure either with formulas or with VBA to make sure that any new rows have an ID greater than those within the Control sheet. I hope that helps.

Kindest Regards,


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question