Let me start off by saying that my problem is somewhat complex, so I am not expecting an excruciatingly long or detailed answer. I am really looking for some general guidance and a gentle shove in the right direction...
OK. Here goes. I am responsible for delivering all manner of reports for my department. My data sources consist of various RDBMS's such as MSSQL, DB2, Access and others. I tie all of these data sources together by using link tables, and building my own queries in MS Access.
These Access queries become the data sources (via ODBC) from which I refresh various data tables and pivot tables inside my Excel workbooks.
So far so good...
The problem however is with security. I have database access to the data I am queering, but my customers (those people in my department consuming the reports) do not. For this reason, I have a series of scheduled tasks that refresh a series of reports on a daily or weekly basis. These reports (in the form of Excel files) are saved to a network drive, or are sent as attachments in emails.
This works fine as well. However, I have now been asked to make it so these reports can be refreshed in real-time. This would take the form of an Excel file sitting on the user's desktop that they could refresh on a whim, any time they pleased.
Since the end users do not have access to the data sources, and since I do not want to embed my credentials in the Excel file, or in an Access file that they would have file-level access to, I must devise another way.
So far, I have come up with the following (incomplete) ideas.
1) Install MSSQL, and use OPENQUERY to replicate functionality of Link Tables. Then, create queries that grab necessary data.
PROS: This will isolate my credentials from the users, and should allow real-time pass-through access to the data.
CONS: I don't have a license of MSSQL. I have to worry about CAL licensing, if I were to obtain one.
2) Find an alternative RDBMS that allows for both a pass-through query functionality and port level access (to eliminate file-level access like MS Access uses) Does MySQL or PostgreSQL allow for the link table functionality as found in Access?
PROS: If this works, it would be ferfect.
CONS: I don't know if these RDBMSs have this functionality.
3) Develop a web services solution. Capture the output of my Access queries via web services, and have the data source of my Excel pivot and data tables be the web service. I've seen a few (not very helpful) examples of this, and most seem to be based on C# or VB.NET. I'm fairly good with VBA, but have never dabbled with VB.NET.
PROS: Once built, the web service would be fairly easy to integrate into Excel
CONS: I'd be starting on square one with building web services (but a new challenge is always good)
Of course, there may be other solutions that I have completely missed which might be much simpler than those mentioned above(???)
If anyone has any thoughts, or has worked a project like this, please let me know.
Thanks!
OK. Here goes. I am responsible for delivering all manner of reports for my department. My data sources consist of various RDBMS's such as MSSQL, DB2, Access and others. I tie all of these data sources together by using link tables, and building my own queries in MS Access.
These Access queries become the data sources (via ODBC) from which I refresh various data tables and pivot tables inside my Excel workbooks.
So far so good...
The problem however is with security. I have database access to the data I am queering, but my customers (those people in my department consuming the reports) do not. For this reason, I have a series of scheduled tasks that refresh a series of reports on a daily or weekly basis. These reports (in the form of Excel files) are saved to a network drive, or are sent as attachments in emails.
This works fine as well. However, I have now been asked to make it so these reports can be refreshed in real-time. This would take the form of an Excel file sitting on the user's desktop that they could refresh on a whim, any time they pleased.
Since the end users do not have access to the data sources, and since I do not want to embed my credentials in the Excel file, or in an Access file that they would have file-level access to, I must devise another way.
So far, I have come up with the following (incomplete) ideas.
1) Install MSSQL, and use OPENQUERY to replicate functionality of Link Tables. Then, create queries that grab necessary data.
PROS: This will isolate my credentials from the users, and should allow real-time pass-through access to the data.
CONS: I don't have a license of MSSQL. I have to worry about CAL licensing, if I were to obtain one.
2) Find an alternative RDBMS that allows for both a pass-through query functionality and port level access (to eliminate file-level access like MS Access uses) Does MySQL or PostgreSQL allow for the link table functionality as found in Access?
PROS: If this works, it would be ferfect.
CONS: I don't know if these RDBMSs have this functionality.
3) Develop a web services solution. Capture the output of my Access queries via web services, and have the data source of my Excel pivot and data tables be the web service. I've seen a few (not very helpful) examples of this, and most seem to be based on C# or VB.NET. I'm fairly good with VBA, but have never dabbled with VB.NET.
PROS: Once built, the web service would be fairly easy to integrate into Excel
CONS: I'd be starting on square one with building web services (but a new challenge is always good)
Of course, there may be other solutions that I have completely missed which might be much simpler than those mentioned above(???)
If anyone has any thoughts, or has worked a project like this, please let me know.
Thanks!
