MS Word documents have a Visual Basic component that allows processes to be executed when specified events occur.
One of these events is
Document_New(), that can be used like this to pop up a "UserForm" that is also part of the Word document:
Private Sub Document_New() Dim myForm As UserForm1 Set myForm = New UserForm1 myForm.Show End Sub
The UserForm looks just like a VB6 or MS Access form and various controls can be added to collect input from the user. In this case a "Submit" button would be place that has an
OnClick() event that accesses the database and returns a recordset used in the document/template.
The code behind can run SQL queries on a SQL server usinging either a DAO or ADODB object model.
The goal is to run some SQL query or stored procedure that returns the data needed for the Word Document. This can all be done with code and there are a million examples and ways to do this.
However... The connection string for the ODBC driver or the ADODB connection leaves the servername/username/password visible to whomever has access to the Word document or template (i.e. user opens document/template in Word and looks at the VB code and now can connect and do anything to the database tables).
How can I create this functionality but not allow the connection string properties to be visible to someone using the document/template?
If it was a VB6 application, it could be compiled. But this is just a Word document or template and not compileable.
I don't believe that the "Encrypted" functionality applies here. That just encrypts data across the network. Connection string properties are still visible to the prying eye.