What are my options for manipulating (read/write) DNN SQL Server database data from an external remote user application (Excel VBA)?
• Some DNN module (free or otherwise)?
• A custom developed web service?
• LINQ, ADO, SQL Stored procedure, REST?
• Other?
I have a DNN web site with membership.
I have an external application that needs to have access to the SQL server database (read and write data). In my case, the external application is Microsoft Excel and I will use VBA code to initiate/control the interaction.
I want DNN or SQL Server to be able to control what data the user app has access to (based on the user's ID that is stored within the Excel file). I believe that DNN/SQL would have to match the user's ID (stored in Excel) with their DNN website membership to determine which data the user can access.
The SQL server database is currently internal; which I understand makes it difficult to directly access the database using ADO in Excel VBA.
Any thoughts or links would be appreciated.

I suggest looking at IWeb project, how to provide secure access to DNN data using web services via DNN API calls. see iweb.adefwebserver.com

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN

Rhys,
Yes, SQL Server inside the firewall. Excel out in the internet.
Yes, I can directly manipulate (read and write) the SQL data via ADO from Excel VBA, but I agree this is a bad idea. I think access needs to be controlled (by the server/DNN/SQL) so users can only access their data (e.g. if my Excel VBA code [which would contain SQL admin credentials] is hacked).

The DNN site allows registered/authorized users to download spreadsheets. After entering data in the Excel, the user would "submit/upload" or "sync" data to the server. Other data would also be downloaded from the server to Excel. The uploaded data would be put in other tables (or possibly another database) and would not manipulate DNN framework data. However, the user-submitted data would need to be associated with the DNN user, so we can keep track of which user submitted which data.
Separetly, we would also want to dislay a summary of data entered (appropriate for that user) in DNN, perhaps using some type of datagrid.
Thanks,
AH