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
Simple table maintenance (Read 6829 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Simple table maintenance
13.03.06 at 11:12:53
 
With a few customers I have been discussing the topic of table maintenance. Table maintenance can be done in SQL*XL but we would like to see how we can do it easier.
 
With this topic I am inviting you to participate in the discussion. Please let me know what you think.
 
Current methods availalbe for table maintenance:

  • Use SQL dialog to query data. Update in Excel. Reconcile database using Multiple Rows Update dialog.
  • Use Multiple Rows Insert dialog to bulk insert data
  • Use single row insert/update

 
The new method of data maintenance we are exploring is through the insert/update and delete statements.
In SQL*XL 4.0.41 I introduced Excel formulae that create these SQL statements. Note that the statements are database type specific. So when connected they generate Oracle syntax, when connected to MySQL they generate MySQL syntax.
 
Using these Excel formula you can extend the ways tables can be maintained in Excel. I have attached a spreadsheet to show an example. A button can be programmed in a simpel way to run an insert, update or delete statement. Please look at the code in sheet1. Another method is through the gray box I put to the side of the query. This will need to be created by SQL*XL each time it requeries. This is not yet done in the example file. In the gray area you can put a tick mark for a row you want to mark for insert, update or delete.
 
Let us know what you think!
Back to top
« Last Edit: 27.03.06 at 10:02:38 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Simple table maintenance
Reply #1 - 27.03.06 at 10:04:11
 
Albert let me know that the spreadsheet contained links to sqlxl.xls. This should be sqlxl.xla of course. I have fixed the code and uploaded it again. I overwrote the original file so you can download it again from the original post.
Back to top
 
 

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