Public Constants question

[cjmitton]

On opening a template at start up of Word 2010 (its in the 'startup' folder), I want to run a SQL Statement to get some information from my MS SQL DB then declare the results as Public Constants so I can then use them in different macros and forms I've set up.Running the SQL code / assigning the results to variables I can do, I'm assuming I can run it all on start up when the template opens (using AutoNew is I remember correctly). I'm not sure how to then assign the results to Public Constants?I've tried to google but I'm getting no where any thoughts please.

[SamT]

You can't assign a varying value to a Constant. A Constant is just that: Constant, it cannot change it's value.

A possible, (but complex and difficult,) workaround is to programmatically create a new module when the doc opens and delete it when the doc closes, before save.

[cjmitton]

Thanks for that, I guess I will have to run the SQL statement each time I open the form or macro to get the variable I need from the DB, I was trying to avoid doing each time and to do it once when I open up word.Unless there is another way?

[westconn1]

you could assign the result from sql to a customdocumentproperty then just read it back from there, when opening document, and assign to variable

[Frosty]

Typically, if the results would be the same during the entire session of Word, what you would do is have a macro in AutoExec that gets your info from your database, and downloads that information to a local resource (a text file)... then you can have code read the text file.This is also a pretty good structure when you want to support an offline capability (i.e., the SQL server isn't available, but you still want to be able to run the macros which need info which originated from the SQL server).But maybe you should define why you want to avoid running your SQL query all the time -- SQL is designed to have many transactions at the same time, so you may not have any kind of performance "hit" in running your macros (and thus not need to dump the data to a local file at all, unless you need the previously mentioned "offline" scenario to work).

[fumei]

I'm assuming I can run it all on start up when the template opens
I am not totally following. Global templates in Startup do not "open". They are loaded though, and any variables in them are available. So an AutoNew in Normal can certainly assign a value to them.

So have your SQL run from a procedure in Normal, and assign a value to a public variable. This should be available for the entire session. Unless I am missing what you are asking for.

[cjmitton]

Thanks for your thoughts on this.Fumei: Thanks, I think what I was initially thinking about doing was wrong (i.e. the constant) I will try your method first and see how it goes.Frosty & Westconn1: Thanks, Good thoughts about the whole off line side of things & customdocumentproperty parts. I may look in to a combination of both once I have the initial part working of getting in the information I need. Also Frosty my little DB runs on a server with a bigger DB that runs a business front line DB, its managed by an external company and I'm not meant to put any other DB's on or there tech department get annoyed and say they won't do anything until its removed! (complete gits) so trying to keep the calls to a minimum so it stays under the radar! Don't have the licences for a second SQL server or a budget to get one!

[Frosty]

That all makes sense, especially using an existing SQL server. Just as an aside... you can use an access database and call data from it with SQL queries. You won't have all the SQL kind of stuff (views, among many other things), but if it's just a simple DB, you don't necessarily need to worry about second SQL servers... just a file server with an Access DB on it.