Word VBA newbie...

User is running an application that uses MS SQL server. The program can either be using a test database on the back-end or a production database. While in the program they start Word. Word is the way they get data into certain parts of the program since they are using Word to create a report that will be stored in the database.

I have written a .dotm that is in the users Word StartUp folder. I query the database using macros in the macro enabled template.

The issue is that someone could open Word using either the test database or the production database on multiple servers. Since my application queries the database to get certain information I need to know whether they are using the test database or the production database and which server they are located on. I can tell by the name of the database whether they are in the test database or the production database. The test database and production databases could be on the same server or a different server.



Is there a way to use VBA and find out the name of any open SQL databases and the server they are located on? That way I could open the correct database to get the information I need.


Thank you...

IMHO, you're going about this the wrong way. If the SQL is supposed to work with a particular database, the code should used a fully-qualified database name; not simply work with whatever 'production' database the user has open.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Is it possible to find the "open" databases and the server on which they reside with VBA? If so how?

To do that, you would have to search all servers for databases, then test whether anyone has them open. This is irrelevant to the question of which database the user running an SQL is using.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

There is a only a small number of databases I would have to search. How can I use VBA and tell if the database is open?

See, for example: http://support.microsoft.com/kb/209189

However, given that many databases allow multi-user access, simply know that 'someone' has such a database open isn't of much use, since that doesn't prevent someone else opening the same database; all that gets locked in such instances are the records in use.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]