Excel Update with SQL connection

Posted on 2014-07-11
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
    LVL 1

    Accepted Solution


    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,


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now