Linking word (VBA) to an embedded database in .dot?

[Keith R]

I've done a bit of Excel VBA, and have one word VBA project I'm thinking
about updating. One of the features it lacks is persistence of some data
elements, because I didn't want to write the code to parse through the
document each time it was opened to try to recapture values from the
document contents.

I've done practically zero database work- I understand relational databases
conceptually, but have never really done anything with databases, so I don't
know any of the terminology (I'd struggle just to build something in
access).

Also, as I distribute my (free) template, I want to make it as easy as
possible. It used to install itself until WinXP, then I couldn't get it to
work anymore so I make people find their own template directory and put the
file there. Can I embed the database (or the data) in a hidden way in word,
so that I don't have to deal with multiple files (template plus database)for
users? Also, each document created from the template would need it's own
database to track their information, as the users might create several child
documents, so keeping it all internal to the document is preferred.


So here's my question: I have a _lot_ (hundreds) of pieces of data that it
would be nice to persist, which would affect things like userform options.
If I knew what I was doing (I'm willing to try to learn), what are my
options with Word?

 

[Jezebel]

From the sounds of it, you don't want a database. What you have is a
collection of name/value pairs. Even with several hundred pairs, you don't
need the overhead of a database simply to read and write the collection. A
couple of methods to consider:

1. DocumentVariables. You pack your information into strings, which you save
as DocumentVariables attached to the document or template. The Join() and
Split() functions make it easy to pack and unpack delimited strings. This
meets your 'no separate file' criterion.

2. Use an INI file. The PrivateProfileString() function reads/writes data to
an INI file. These aren't really so hard to manage, since Word expects to
have supporting files anyway (ie the templates and add-ins collections). You
can park the INI file in one of Word's standard folders (like the Start-up
folder) or use the Environ() function to get the local AppData folder.

 

[Peter Hewett]

Hi Keith R

Just to add a little to what Jezebel said. A document variable will hold up to 65,280
characters. You can also use the Scripting runtime libraries Dictionary object to manage
Parameter/Value pairs. It makes storing, retrieving and setting Parameter/Value pairs
very simple.

HTH + Cheers - Peter

 

[Steve Lang]

Hi Keith,

For what it's worth... I though I would suggest another possible option in
addition to Jezebel's excellent suggestions. If you want the data "hidden",
you can write it to the registry as well (Though with several hundred
entries this may get messy). This can also afford you to maintain separate
data sets for multiple users on a single computer using a single document.

If you do use the document variable route as Jezebel suggested (which I use
a lot), the data is part of the document. Real simple and transferable.



HTH and have a great day!

Steve

 

[Keith R]

Thanks to all (Jezebel, Peter, Steve) for your suggestions. I am likely to
use the document variable route, just in case the user(s) want to move a
particular document between computers - sounds like that would be the best
way to keep it portable.

Are there any online tutorials or examples of using this method? I looked at
the VBA help file and the closest I found was
CustomDocumentProperties(index)... is that the same thing? I'm using
OfficeXP on WinXP, as are my current users, although if possible it would be
nice to maintain backward compatibility to Word97 for other potential users
who haven't been upgraded.

Thanks!!
Keith R

 

[Steve Lang]

Hi Keith

Docvariables are used like:
'to set
ActiveDocument.Variables("MyVar1").Value = "My Variable Value 1"

'to retrieve
strVarValue = ActiveDocument.Variables("MyVar1").Value

HTH and have a great day!

Steve