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
Update database field when value in sheet changes (Read 4046 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Update database field when value in sheet changes
06.02.06 at 13:05:38
 
Mike from the USA asked the following question:
 
Question:
How do I to update a database field based on values in my spreadsheet? When I make a change in my spreadsheet I would like the database to be updated immediately.
 
Answer:
Use the SQL*XL SQLExecute function in Excel to execute an update statement to update the database.  
The technique works as follows.
 
Consider the following cell formula:
=sqlxl.xla!SQLExecute( "update emp set ename = 'Linker' where empno = 123" )
This would update the database to set the employee name (ename) to Linker where the employee number is 123.
 
To make this variable you use the concatenate function as follows:
.sqlxl.xla!SQLExecute( "update emp set ename = '", A1 , "' where empno = 123" )
 
Depending on the calculation setting (see Tools | Options in Excel) this update statement will run. When you have set Calculation to automatic it will fire straight after changing A1. If calculation  is set to manual you need to calculate (or press F9) to run the update statement. If you do not change A1, the command will not be run when you calculate. When you want to run the update command whenever you calculate the spreadsheet use the Volatile versiono fthe SQLExecute command:
.sqlxl.xla!SQLExecuteVolatile( "update emp set ename = '", A1 , "' where empno = 123" )
 
The cell value that is displayed after a successful run is the time that the update statement was run. This way you can check when the update statement actually ran.
Back to top
 
« Last Edit: 06.02.06 at 13:10:56 by Gerrit-Jan Linker »  

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