Linker IT Software
Google
Web www.oraxcel.com
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
Sending changes in SQL db and in the Excel sheet (Read 2095 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Sending changes in SQL db and in the Excel sheet
16.06.08 at 23:02:15
 
Sending changes in SQL server and in the Excel sheet
 
Quote:
I have a Excel Spreadsheet and a SQL Database. I want a connection between the spreadsheet and SQL database. When the data changes on the SQL database it will update in the excel spreadsheet and if a change is made on the excel spreadsheet it will update the SQL Database.
Is there a better manner to do as I need?

 
Answer:
It doesn't do this fully automatic. There is not any magic feature that pings the database to see whether values have changed nor is there a function that magically updates the database after you made a change on the sheet. I believe you don't actually want that either. Suppose you make a typing mistake. In the process we implemented there is a review step that displays the old and new value so you can decide whether you want to make the update in the database. Let me explain:
 
To query data from the database into the spreadsheet there are 2 options. The easiest one to setup is to type the SQL in the SQL dialog and dump the results in the sheet. The SQL is saved in a small comment in the top left cell. Selecting this cell and opening the SQL dialog again, SQL*XL will load in the SQL automatically. It is therefore easy to rerun the query at a later time to refresh it. There is even a button on the toolbar that looks for SQL in notes to refresh them.
The second option is to use the =SQLQueryValue worksheet function You can create the function from the SQL*XL menu in the insert function list. Fetching one value is easy. You can also use it as an array formula which in Excel is unfortunately a little trickier to setup. I have left a lot of documentation so you should be able to do this also. You may also read the Excel help about entering en using Array functions. These worksheet functions can be recalculated (requeried) using the calculation menu which you find in the insert function menu of SQL*XL.
 
To send data back into the database we have a multiple rows update feature. When you fetch data with option 1 described above (using the SQL dialog) you can change the data in the spreadsheet of the last fetched results. Then you can use the multiple rows update feature to find the changes you applied and to send these to the database. It will aks you for confirmance if you want (recommended).
 
So the quick way to do it is to do a query once to receive a copy of the database data. When you want to ensure you look at the latest version of the data, refresh the query. Apply your changes to the spreadsheet and send the updates back to the database.
 
Another way to look at this process is to leave the master copy in the database and treat the copy in Excel as secondary. To my mind this is how you should try approach it, use the database for safe permanent storage and Excel for the ad-hoc changing of the data.
 
Back to top
 
 

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