Need to use view or stored procedure on SQL Server in OpenOffice Calc/Base
Posted on 2008-06-19
I have a substantial Excel Workbook that it almost entirely converted to Calc. One of the sheets in the workbook dynamically queries simple employee data from a table in one of our HR systems. In Excel, the sheet uses a QueryTable that is linked to an ASPX located on our intranet. Unless I'm mistaken, Calc doesn't have that functionality (you can only link to *.html...this is an option for a solution for this issue, but one I'm avoiding for several reasons.)
Instead I registerd an ADO connection to the Employees table in the SQL server, built the query I needed in Base, dropped it on the worksheet, and put a refresh macro in the createdocument event (I'm using templates over a network). This works well as far as obtaining current data is concerned, but it's not such a good idea for security reasons. Registering the ADO connection to the server gives any user that opens the spreadsheet access to ALL of the tables in this particular system just by hitting F4. This includes personal information that can't be shared.
I'll need access to other systems for other workbooks I'm converting, so rather than get into a tiresome routine of manipulating SQL server login access, I created a new DB (on a different SQL server) called "OpenOffice". The idea was that I would just create a view in this DB that gave me only the Employee Info that I need. Then I could register this DB in base, and distribute across the network without security concerns (The .odf is on a shared drive). The problem is that base doesn't see views, and I'm wondering now about stored procedures as well. Does anybody have any insight on this issue? I'm willing to explore other solutions as well.