Word Autotexts in a database

[mileski]

Hi everybody,


I have one big question for you. So, the idea, until now in my Company we were using a small Word-program, actually VBA scripts, and we have a big number of Autotexts put into .DOT files. There are around 15000 autotexts. Well, now I want to put all those autotexts into a database, preferably Access, from where, with the help of VBA, the users will pull these autotexts and use them in their documents. You have to know also, that these autotexts are formated separately, so writing plain text into the database won't help. I've tried this example and it works:

I save the autotext as RTF (Rich Text Format), get the stream and put it into the database. This is how I create the database just one time, and then give it to the users.

The users pull this autotext, the VBA macro first takes the string from the database into the Clipboard, and pastes it in a document.

This works flawlessly and fast, when it concerns a normal autotext. The problem arises when I use a complicated autotext, which is made from many other autotexts. Then, i go through the main autotext, see which autotexts it has, pull them from the database, and put them at the place of the child autotext.

Now here comes the delay.


What I would like from you, is to tell me, whether my idea is ok, whether I implement it correctly, or is there anything, that I can make better?

I would also appreciate other ideas, how to accomplish the above.

[mileski]

Hi,

I didn't expect that, no one will answer my post for such a long time. I hope that I cleared my problem in the above post well.

I hope I'll get some idea from other perspective to my problem from somebody on the forum.


:banghead: <== I'm really doing this.

[mdmackillop]

The problem arises when I use a complicated autotext, which is made from many other autotexts. Then, i go through the main autotext, see which autotexts it has, pull them from the database, and put them at the place of the child autotext.

Can you clarify this for me. I don't quite see how this works. Is it possible to post a simple sample?
Regards
MD

[TonyJollans]

I didn't answer because I didn't think I could offer much in the way of help, but as everyone else seems to feel the same way, I'll do my best.

I'm with Malcolm in not really understanding what you're doing or why you're doing it, but it sounds like you have a database performance problem that isn't really anything directly to do with Word.

15000 is a lot of AutoTexts. It isn't reasonable to expect a user to be working with that many, no matter how they are organized. Or is it just 15000 in total but far fewer in use at any one time? Could you give a bit more detail and tell us what problems you have experienced using them as AutoTexts that have made you go for this rather unusual solution?

[mileski]

Can you clarify this for me. I don't quite see how this works. Is it possible to post a simple sample?
Regards
MD
First, let me thank you two, for taking the time to answer my post :hi:

Now, about the explanation. I will try to write you a real example. Like this:

You write in Word: e3<F3>

(F3 is the Function button pressed F3)

Now, the program knows from the F3, that you want to access the Autotext with the name "e3". The program pulls the autotext "e3" from the database, puts it in RTF on the clipboard (this must be done, to be able to keep the formating of the autotext), and then pastes it from the clipboard onto the place where the user wrote "e3".

And then, instead of e3 in the document, you see something like this:

{AUTOTEXT e15}{AUTOTEXT e17}{AUTOTEXT e20}

So, actually, the "e3" AUTOTEXT, is a combination of three new autotexts e15, e17, e20.

Now, the program selects this new range, and looks for Autotexts inside. When it encounters "e15", it does the same procedure like explained earlier. Pulls from database, to clipboard, and paste in the document.

So, at the end the user has a real text (not Autotexts) consisted from the texts in e15, e17, e20.

This is the general idea.

[mileski]

I didn't answer because I didn't think I could offer much in the way of help, but as everyone else seems to feel the same way, I'll do my best.

I'm with Malcolm in not really understanding what you're doing or why you're doing it, but it sounds like you have a database performance problem that isn't really anything directly to do with Word.

15000 is a lot of AutoTexts. It isn't reasonable to expect a user to be working with that many, no matter how they are organized. Or is it just 15000 in total but far fewer in use at any one time? Could you give a bit more detail and tell us what problems you have experienced using them as AutoTexts that have made you go for this rather unusual solution?

Hi Tony,

Well, yes, 15000 are a lot of Autotexts. And so much they really are :)

Now, i will tell you how this was done in the past.

We had in the startup folder, like 30 .DOT files, and each had like 200-300 Autotexts. And this worked flawlessly.
Now, as the time past, the autotexts reached the number of 15000.
Now, this was divided in 50 .DOT Files, and each had 300 autotexts in it.

Here comes the problem of maintaining these Autotexts.

I have made a complete program, so that the users can update to the newest version, with a update possibility.
I came on the idea to use a database for the autotexts, and not .dot files, because I thought that this will lead to the following:

1. Database. <= All Autotexts divided in tables, which are with relationships

2. The program would work faster, because we are not putting 50 .dot files in the startup folder of Word, instead we use the database.

3. It is easier to manipulate the Autotexts from one central location, where someone will change some autotexts, and makes a patch for the program, puts it on the server, and the Update automatically sees that there is a newer version, and updates to the newest version of the program, and also has the newest changed Autotexts.


Now, I don't really know, whether the idea of using a database to hold all the Autotexts is better then having these .dot files in the startup folder. That's my problem. That's why I posted here, so we can talk about this whole thing, and maybe someone will give me a better idea, or tell me some way of optimizing this whole thing with database.


I hope that I've explained myself better this time.


Feel free to ask me, if you still see some part unclear.


p.s.. About the users using all these Autotexts. Well, when they make a new document, they use maybe like 500-600 Autotexts in one, but they must have ALL the autotexts available at any time.

[TonyJollans]

Hi Robert,

Thankyou for that. I need to think about it and don't have time to reply fully at the moment. Two things spring to mind - (1) how did the users used to manage the nested ATs - presumably the db process is adding functionality for them, apart from anything else and (2) could you not manage to build the whole insert at the database end rather than inserting one level at a time in the document and then going back and fore?

[mileski]

Hi Robert,

Thankyou for that. I need to think about it and don't have time to reply fully at the moment. Two things spring to mind - (1) how did the users used to manage the nested ATs - presumably the db process is adding functionality for them, apart from anything else
They didn't. It was a messed up thing when I started to work with. One person was changing them, and then they found ways to give the new .Dot Files to the people using them.

Actually, the normal users are not allowed to change these autotexts, they just use them. They are changed just from one place, and with the Update Program now everybody gets them in time and secure.


and (2) could you not manage to build the whole insert at the database end rather than inserting one level at a time in the document and then going back and fore?
Can you explain this a bit? I don't quite understand what do you mean by that.

[fumei]

If I understand Tony questions correctly...

Currrent situation:

1. e3 goes to DB, returns e15 & e17 & e20
2. e15 goes to DB, returns text
3. e17 goes to DB, returns text
4. e20 goes to DB, returns text

This is FOUR interactions with the DB.

What I think Tony is asking:

1. e3 goes to DB, DB returns full text of e15 & e17 & e20

In other words, the logic is in the DB. The DB determines that e3 means e15 & e17 & e20. Rather than all that traffic going back and forth.

[fumei]

BTW: how are you getting {AUTOTEXT e15}{AUTOTEXT e17}{AUTOTEXT e20} into the document?

[mileski]

BTW: how are you getting {AUTOTEXT e15}{AUTOTEXT e17}{AUTOTEXT e20} into the document?

How do you mean how I get them?

They are in the Database under e15, e17, e20. And e3 is from the format above.

So, when you press e3, you get texts from e15, e17, e20. And if you press Alt+F9, you see the Autotexts only.


Btw, thanks for the sugestion.

It is right that back and forth is very problematic, but then I have the problem of editing the Autotexts. Because, if someone edits e15 for example, and e3 is not made of Autotexts but from the real texts behind e15, e17, e20, then e3 will be with the old text. The one changing it, should then rebuild the whole database for every change, because of the consistency. :(