updating sql via excel.

Question

updating sql via excel.

snomadj

SSCarpal Tunnel

Points: 4491
More actions
May 21, 2018 at 3:31 am

#335900

3rd party provider has a requirement = user needs to update sql tables via excel. Possible it seems with some VBA code.
As the DBAdmin I need to advise the client to say allow this work to proceed or don't.
I don't have the right words or arguments to say it is ill advised but my spider senses are telling me it is.
Can anyone help?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sunilchand Mr or Mrs. 500 Points: 507 More actions · Answer 1

snomadj - Monday, May 21, 2018 3:31 AM
3rd party provider has a requirement = user needs to update sql tables via excel. Possible it seems with some VBA code.
As the DBAdmin I need to advise the client to say allow this work to proceed or don't.
I don't have the right words or arguments to say it is ill advised but my spider senses are telling me it is.
Can anyone help?

Never work on such scenario .however its possible through VBA code

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 2

sunilchand - Monday, May 21, 2018 6:29 AM
snomadj - Monday, May 21, 2018 3:31 AM
3rd party provider has a requirement = user needs to update sql tables via excel. Possible it seems with some VBA code.
As the DBAdmin I need to advise the client to say allow this work to proceed or don't.
I don't have the right words or arguments to say it is ill advised but my spider senses are telling me it is.
Can anyone help?
Never work on such scenario .however its possible through VBA code

Use of the "ACE" drivers would make for a fairly simple solution without having to maintain additional code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 3

Ok, thanks for the replies. I know it's possible, I guess I was wondering if it is adviseable.

sgmunson SSC Guru Points: 110639 More actions · Answer 4

snomadj - Tuesday, May 22, 2018 2:50 AM
Ok, thanks for the replies. I know it's possible, I guess I was wondering if it is adviseable.

That depends on the situation. Is the data they need to update, data that only they are responsible for? What would happen if they mis-edited something? What would happen if they deleted something? If anything they do to the data has any direct impact on your company, I'd insist on a more formal process, e.g. they submit a spreadsheet for processing, which they securely FTP to your FTP site, where a file watcher waits for it and when found, processes it as appropriate (VBScript or better yet, SSIS), with no external access needed beyond their login to the FTP site.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

snomadj - Tuesday, May 22, 2018 2:50 AM
Ok, thanks for the replies. I know it's possible, I guess I was wondering if it is adviseable.

In theory, it's no different than using some custom app to do the same thing but I have to agree with Steve Munson above on this one. As the saying goes... "Here there be dragons". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 6

All good points, thanks for helping me think it through.

sgmunson SSC Guru Points: 110639 More actions · Answer 7

Jeff Moden - Tuesday, May 22, 2018 4:11 PM
snomadj - Tuesday, May 22, 2018 2:50 AM
Ok, thanks for the replies. I know it's possible, I guess I was wondering if it is adviseable.
In theory, it's no different than using some custom app to do the same thing but I have to agree with Steve Munson above on this one. As the saying goes... "Here there be dragons". 😀

I saw a poem about that as it relates to business once... went something like this:
Here there be dragons, and each must be slayed; And for each one, there's a price to be paid. There's DBAs, BAs, SQL guys and .Net 'ers. And you'll pay them in gold, or you'll end up like debtors. And then you must fix all the tech debt t'was done. before you can say, that the battle was won. So... if the dragons were kind, and let you escape... Just know that your mind, t'was what they actually raped.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

DennisR SSC Journeyman Points: 97 More actions · Answer 8

Updating a DB with Excel is a viable option. I use Excel when there will be only 1 or 2 users, or I use Excel to create a prototype to be implemented in a web based application. For Excel I prefer to load the current data into protected columns, provide columns for updates, then send both the original and changed data to a stored procedure that is run with EXECUTE AS an id that has update authority. The user id should have read only authority and only the users that will be responsible for updating should be authorized to execute the stored procedure. The stored procedure verifies that the data in the database has not changed since it was displayed in Excel (hours could have elapsed between display and update) and can edit the new data to prevent SQL injection and update the destination row along with the timestamp and userid. When designing tables for user update I always require a timestamp and userid since I know someone will ask the question who changed this data and when. Using VBA and the DB connections - I have done this with SQL Server, Oracle, and others - will allow simple or complex SQL queries to be executed which can be dynamically assembled in the Excel VBA code before sending the request to SQL Server to return results to Excel.