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
Maintain database tables in Excel (Read 3991 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Maintain database tables in Excel
21.12.05 at 09:40:52
Venku from the USA asked the following question:
Our database has bunch of tables. We are trying to get the data into an Excel spreadsheet to modify and update the database in regular intervals. We are looking for Insert/Update/Delete operations in the table. At the same time we are looking for multi user update capabilities.
Yes this is all possible but you need to do some book keeping yourself. With macro recording you can automate quite a lot too.
First thing you mention is the retrieval of data into the spreadsheet. SQL*XL has multiple ways you can automate the execution of queries. There is the SQL Favorites list. You can use SQL files. You can also record a macro.
The inserting of new rows is possible too. You need to use the multiple rows insert feature for this. You need to point to the selection of rows that are now. You must keep the inserted rows separate from the existing rows to make this work efficiently. You could e.g. only allow your users to add new rows at the end of the spreadsheet. Again this process can be automated using VBA macro recording.
Updating changed data is easy. The update multiple rows facility in SQL*XL allows you to edit the last retrieved data. You can use this facility to post back any changed data.
Deleting rows is a bit more tricky. I have posted a message on the forums to see whether there is any interest to add a delete feature. There has not been much reply and the reply that was received was negative.  I can still build it in but for the moment you must do a little work yourself. The easiest is if you can force the end user to select the cells with the PK information. Then you can very easially loop through the selected cells, pick up the PK value and issue a delete command to remove the row.
Multi user capabilities must be present in the database. You are using Oracle so that is quite ok. Simpeler database sometimes do not have multi user capabilities. You do need to be a bit careful with drivers. I know that ODBC against Oracle just updates the new data in the following scenario:
t0: user1 updates row 1
t1: user2 updates the same row
t2: user 1 issues a commit
t3: user 2 issues a commit
Question: is user1 changes applied in the database of the changes of user2. With ODBC and Oracle I recall that the changes of user2 will override the changes made by user1. With the OO4O driver user2 will get errors that someone else changed the data. I have not tested this with OLE DB yet.
If you want your user (say user1 in the scenario above) to update the data where other users should not interfere you should consider using locking. You can run a select .... for update. That will lock the rows you select until you issue a commit or rollback. In this situation our scenario above would become:
t0: user1 selects data for update locking the rows
t1: user1 updates row 1
t2: user2 updates the same row but gets an error that the row is locked
t3: user 1 issues a commit
t4: user 2 can now update the data
Back to top

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