Acesss Data in Word via VBA

[Zokess]

Using Word VBA....I need to access information in an existing MS Access DB,
(all numeric), perform a few functions on said data (Add/Multiply), then
stick the result into my Text Form Fields within my word document. I plan on
executing the VBA code on the open evdent of my document....I just don't know
how to pull the data from my access database. Any
Suggestgions/Help...PLEASE!!

 

[Cindy M -WordMVP-]

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

Using Word VBA....I need to access information in an existing MS Access DB,
(all numeric), perform a few functions on said data (Add/Multiply), then
stick the result into my Text Form Fields within my word document. I plan on
executing the VBA code on the open evdent of my document....I just don't know
how to pull the data from my access database.

You basically have two options: DAO - Access's native database engine's
interface; or ADO - a standard Microsoft data access interface. Have you ever
used either one?

You'll find information on using DAO in the Access VBA Help files; you can use
it in Word by activating a reference to Microsoft Data Access Object. ADO
(Tools/References/Microsoft ActiveX Data Objects) has it's own *.chm files.

In either case, you set up a connection to the database, create a recordset, and
use an SQL statement to load the data you want to work with into the recordset.
You can then "read" the recordset in order to manipulate and use the data.

You'll find a DAO example in WdAcc97.zip in the Mail Merge FAQ on my website. It
shows how to write the data to bookmarks instead of form fields, but that's a
minor difference.

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

 

[Zokess]

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

You basically have two options: DAO - Access's native database engine's
interface; or ADO - a standard Microsoft data access interface. Have you ever
used either one?

You'll find information on using DAO in the Access VBA Help files; you can use
it in Word by activating a reference to Microsoft Data Access Object. ADO
(Tools/References/Microsoft ActiveX Data Objects) has it's own *.chm files.

In either case, you set up a connection to the database, create a recordset, and
use an SQL statement to load the data you want to work with into the recordset.
You can then "read" the recordset in order to manipulate and use the data.

You'll find a DAO example in WdAcc97.zip in the Mail Merge FAQ on my website. It
shows how to write the data to bookmarks instead of form fields, but that's a
minor difference.

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


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

I have been exposed to ADO while taking some MS VB.NET programming classes,
but have not used it anywhere else. Question....if I pull the information
into a dataset...can I acccess specific data by column/row indicaors because
there are only a few specific items I need...like the cost of Items A/C/&
Z..not the entire DB.

Thanx for the info......any additional help is greatly appreciated

 

[Cindy M -WordMVP-]

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

I have been exposed to ADO while taking some MS VB.NET programming classes,
but have not used it anywhere else. Question....if I pull the information
into a dataset...can I acccess specific data by column/row indicaors because
there are only a few specific items I need...like the cost of Items A/C/&
Z..not the entire DB.

Yes, that should be no great problem. The columns are the Fields(index) or you
can use the field name.

If you need only certain rows, it's probably fastest to specify those in the
SQL-Query you use to open and instantiate the Recordset. (You can also specify
the fields you want, i.e. SELECT A, C Z FROM table WHERE A='criteria'

"Fiddling" with an Access query, then looking at the SQL-View can give you most
of what you need.

You'll find information for setting up an ADO connection to an Access database
using an OLE DB provider, for classic VB (=Word VBA) at
http://www.carlprothman.net/Default.aspx?tabid=81

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