Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Making database updates (Read 2131 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Making database updates
19.08.10 at 10:15:11
Making database updates
This is the first time I have tried to write back to a SQL db from Excel and I am a little cautious as the db I am writing to contains some sensitive info. Can I clarify with you the correct step I need to take?

I have set up the SQL*XL connection to a SQL Server, Runtime db, MSDASQL. Connects ok so I am assuming I have done this correctly?

Here's the bit I need a little help with. I have made an adjustment to the db on one cell, KR34 of the spreadsheet. When I refresh the original data returns. As I said earlier, I don't want to 'experiment' too much as fear of corrupting the db would get me shot!

Hope you can help

What I do in these situations where the data integrity is very important is to make a select statement that retrieves only the rows that need the change. The smaller the number of rows returned the less likely you change the wrong row.
After returning the data from the database I suggest you copy the returned cells and paste it elsewhere on the sheet for safekeeping.
Change values in the originally returned cells and start the multiple rows update screen. Select to review changes only first. This will run through the rows held in memory and the rows on your spreadsheet. If correct it will only detect the changes you applied. Then run the procedure again and do a review and change. This will do the same but also send the change you made back to the database. Commit the change.
Now query the data back from the database (or refresh the query) to see whether your changes were applied correctly. In the case you did it wrong it is easy to revert the change. You still have a copy of the original data on your spreadsheet as a safe copy.
If you are not soo familiar with this procedure, why do not experiment a little first. Most Oracle databases still have the SCOTT schema and the EMP table for example where you can experiment. You can also create your own testing table of course. In fact SQL*XL is very handy to do this. Just make some test data on the spreadsheet and run the multiple rows insert process. It will create the table for you as well.
I hope this helps
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1