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
SQLXL.DeleteRecordset??? (Read 9115 times)
grahamf
YaBB Newbies
*


I Love SQL*XL

Posts: 13
SQLXL.DeleteRecordset???
04.04.07 at 16:27:38
 
I'm looking for an option to delete matching records before an insert. Either via an option on the InsertRecordset, or by an exactly equivalent Deleterecordset option e.g.
 
SQLXL.DeletetRecordset table:="archive", Columns:="M_COUNTRY,M_YEAR,M_MONTH,MEASURE_ID, DATA_VALUE", datarange:=uploadarea, PromptOnError:=False, SortToStatus:=False, CommitFrequency:=0, Orientation:=1, Silent:=True, Feedback:=False
 
(or similar)
I believe your Update option needs me to have dumped the data first. However I don't want to do that - I just want to ensure an "overwrite" capability
 
Any suggestions?
Thx
Graham
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL.DeleteRecordset???
Reply #1 - 16.04.07 at 20:43:38
 
This is technically a difficult thing to do. If you want to do this I suggest you use a procedure call in which you can code the precise logic you require. You could loop the rows of the spreadsheet executing the procedure for each row passing in the values in the cells.
 
The reason why this is difficult is that you need to find the rows to delete. This is easy to program it fool proof enough to make it into the program. What if the user forgets to include a row and accidentally deletes a whole lot of rows more than he intended to do. I would have to build dynamic delete statements with a where clause. I don´t really like that strategy.
Back to top
 
 

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


I Love SQL*XL

Posts: 13
Re: SQLXL.DeleteRecordset???
Reply #2 - 17.04.07 at 09:42:32
 
Quote from Gerrit-Jan Linker on 16.04.07 at 20:43:38:
This is technically a difficult thing to do. ...The reason why this is difficult is that you need to find the rows to delete. This is easy to program it fool proof enough to make it into the program. What if the user forgets to include a row and accidentally deletes a whole lot of rows more than he intended to do. I would have to build dynamic delete statements with a where clause. I don´t really like that strategy.

 
How about including an API call and the backend functinality, but exclude a user interface. In that way, it could only be hand coded by someone who knew what they were doing.. and would limit the damage possible by end users?
 
OK compromise?
Graham
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL.DeleteRecordset???
Reply #3 - 19.04.07 at 07:15:15
 
It may be a good idea to build this in but with the following safeguards:
- no commit frequency so the user can always rollback
- feedback cell to show the number of rows deleted (if available from the driver)
Back to top
 
 

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


I Love SQL*XL

Posts: 13
Re: SQLXL.DeleteRecordset???
Reply #4 - 19.04.07 at 10:08:36
 
.. your suggestions sound good (cautious chap aren't you Wink. I assume your delete record counter would take the place of  the "successful" indicator from the Insert version?
Thx
Graham
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL.DeleteRecordset???
Reply #5 - 19.04.07 at 11:03:17
 
Yes, very cautious. Users assume software like SQL*XL will help them. Taking a cautious approach is the only way to go in my opinion.
 
When I have time next I will look into this further. I will schedule this after the implementation of the 2007 ribbon interface. That is into the last phase of testing and implementation now.
Back to top
 
 

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