Can Word VBA query a remote database?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ZLA - 19 Jan 2006 20:19 GMT

Can VBA be used to query a remote (over an internet connection) database from
a Word Document?

If so, what does the client machine need in order for this to occur? [for
example, MDAC] Is it likely they will have that installed on their computer
if they only have Word installed? What if they have Word and Excel installed?

Does it matter if the remote database is MS SQL or MS Access? Is it be
possible to connect to an Access database stored on a remote web server?

Thanks in advance.

Reply to this Message

Cindy M -WordMVP- - 20 Jan 2006 17:37 GMT

Hi =?Utf-8?B?WkxB?=,

this probably isn't the best group to get an indepth, knowledgeable answer to
your question. Basically, Word VBA can use any technology you can use from VB6
to connect to a database. So the best thing would be to ask in a group that
specializes in remote data connection for VB6.

Once you've figured that out, you go over Tools/References to set references to
the same object libraries you use in VB6 (there it's Project/References). And
from that point on, you can more or less copy/paste the code, or even import
entire *.bas and/or *.cls files.

> Can VBA be used to query a remote (over an internet connection) database from
> a Word Document?
[quoted text clipped - 5 lines]
> Does it matter if the remote database is MS SQL or MS Access? Is it be
> possible to connect to an Access database stored on a remote web server?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Reply to this Message

Mike Smith - 24 Jan 2006 13:22 GMT

> Can VBA be used to query a remote (over an internet connection) database
> from
[quoted text clipped - 10 lines]
>
> Thanks in advance.

Here's an example:

sDBFile = "\\PathToDB\docID.mdb"
' Open the DB
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & sDBFile
SQL = "SELECT COUNT(*) as total_Count FROM Documents WHERE FileName='" +
ActiveDocument.Name + "' AND Title='" +
ActiveDocument.BuiltInDocumentProperties("Title") + "';"
rsDocuments.Open SQL, dbMain, adOpenDynamic, adLockOptimistic

Reply to this Message