Excel Update with SQL connection

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
Issue:
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.

Thanks
BKennedy2008Asked:
Who is Participating?
 
Jaes OverleyConsultantCommented:
BKennedy2008:

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,

Jaes
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.