ODBC Connection

[Ben]

Hi,
I have a database on which I am writing several reports. These reports are
being written as data is being entered so I have a concern on the accuracy of
the data which will be presented in my report. For example, I have a general
statistics page which lists the amount of data entered, the amount of links
between elements and so on.
At the moment I have to check the report and compare it to the database to
ensure the report is accurate at the time of print. I have experimented with
linking access to excel, then imbed into word but this process is a little
difficult for other users to do routinely. Is there some way I can link
directly to the word document and refresh from there? Are there any other
resources you know of which may help me? Cheers in advance. -Ben

 

[John Nurick]

Hi Ben,

Working from within Word, you can use the Database field. This runs a
query against a database and insert the results into the text. AFAIK if
the query returns a single value the field can go into running text,
while if there are multiple values (more than one field and/or more than
one record) the field result is a table. Here's a field that queries
Northwind and (if I got it right) returns the mean of the total amounts
of all orders:

{DATABASE \d "C:\\Program Files\\Office
2003\\OFFICE11\\SAMPLES\\Northwind.mdb" \s "SELECT AVG(OrderPrice) AS
AvgOrder FROM (SELECT SUM(UnitPrice * Quantity * (1-Discount)) AS
OrderPrice FROM [Order Details] GROUP BY OrderID);" \# $#,##0.00 }

See also http://word.mvps.org/faqs/interdev/GetDataFromDB.htm

If you prefer to work from Access, you can use formfields or bookmarks
in the Word document and write Access VBA code to poke data into them.

 

[Ben]

Thanks for your help John. The databse field works perfectly although I am
having some update issues. I have also posted this question in the word forum
with no luck. My problem is that when I update the table by pressing F9 I
lose all of my format changes. Is there any way that I can keep these in
place? I only really need to narrow the columns a little to get the whole
table in my document. Any ideas? Cheers.

Hi Ben,

Working from within Word, you can use the Database field. This runs a
query against a database and insert the results into the text. AFAIK if
the query returns a single value the field can go into running text,
while if there are multiple values (more than one field and/or more than
one record) the field result is a table. Here's a field that queries
Northwind and (if I got it right) returns the mean of the total amounts
of all orders:

{DATABASE \d "C:\\Program Files\\Office
2003\\OFFICE11\\SAMPLES\\Northwind.mdb" \s "SELECT AVG(OrderPrice) AS
AvgOrder FROM (SELECT SUM(UnitPrice * Quantity * (1-Discount)) AS
OrderPrice FROM [Order Details] GROUP BY OrderID);" \# $#,##0.00 }

See also http://word.mvps.org/faqs/interdev/GetDataFromDB.htm

If you prefer to work from Access, you can use formfields or bookmarks
in the Word document and write Access VBA code to poke data into them.

Hi,
I have a database on which I am writing several reports. These reports are
being written as data is being entered so I have a concern on the accuracy of
the data which will be presented in my report. For example, I have a general
statistics page which lists the amount of data entered, the amount of links
between elements and so on.
At the moment I have to check the report and compare it to the database to
ensure the report is accurate at the time of print. I have experimented with
linking access to excel, then imbed into word but this process is a little
difficult for other users to do routinely. Is there some way I can link
directly to the word document and refresh from there? Are there any other
resources you know of which may help me? Cheers in advance. -Ben

 

[John Nurick]

Have you tried using the \* MERGEFORMAT switch in the field? That often
helps, though I don't know whether it will work with a table.

Thanks for your help John. The databse field works perfectly although I am
having some update issues. I have also posted this question in the word forum
with no luck. My problem is that when I update the table by pressing F9 I
lose all of my format changes. Is there any way that I can keep these in
place? I only really need to narrow the columns a little to get the whole
table in my document. Any ideas? Cheers.

Hi Ben,

Working from within Word, you can use the Database field. This runs a
query against a database and insert the results into the text. AFAIK if
the query returns a single value the field can go into running text,
while if there are multiple values (more than one field and/or more than
one record) the field result is a table. Here's a field that queries
Northwind and (if I got it right) returns the mean of the total amounts
of all orders:

{DATABASE \d "C:\\Program Files\\Office
2003\\OFFICE11\\SAMPLES\\Northwind.mdb" \s "SELECT AVG(OrderPrice) AS
AvgOrder FROM (SELECT SUM(UnitPrice * Quantity * (1-Discount)) AS
OrderPrice FROM [Order Details] GROUP BY OrderID);" \# $#,##0.00 }

See also http://word.mvps.org/faqs/interdev/GetDataFromDB.htm

If you prefer to work from Access, you can use formfields or bookmarks
in the Word document and write Access VBA code to poke data into them.

Hi,
I have a database on which I am writing several reports. These reports are
being written as data is being entered so I have a concern on the accuracy of
the data which will be presented in my report. For example, I have a general
statistics page which lists the amount of data entered, the amount of links
between elements and so on.
At the moment I have to check the report and compare it to the database to
ensure the report is accurate at the time of print. I have experimented with
linking access to excel, then imbed into word but this process is a little
difficult for other users to do routinely. Is there some way I can link
directly to the word document and refresh from there? Are there any other
resources you know of which may help me? Cheers in advance. -Ben