Combine Several Tables from SQL Server Into One Access Table

For the last 7 years we have been using a piece of software that's
based on Access 2000. About two years ago I created several MS Word
documents (merge files) that get data from this Access database and
today these documents are a crucial part of our business, they are
used 90% percent of the time and basically we can't live without them.
The Access database has a major design flaw, it basically has one
table that contains most of the data. A well designed database would
have the same data in several tables. But this flaw turned out to be a
good thing when creating the MS Word documents, since all the data
needed for the documents are in one table. Everything works
beautifully.
Our software vendor released a new version of the software which uses
a well designed SQL Database, and they split that table into several
tables, as it should have been to begin with. The problem now is that
the documents I created will not work with the new software, therefore
we cannot upgrade a make use of all the other good things they
implemented.
I thought about creating an Access database and link it to the SQL
database, and try to gather all the data needed into one table or
query so I can point the merge documents to it.
I know that's a long story, but I think that's the only way to be
clear. I really need help with this, and I'm pretty sure there are
other better ideas I can get here.
Thank you.
Silvio.

www.silvioribeiro.com

Re: Combine Several Tables from SQL Server Into One Access Table

On Apr 3, 12:56=A0am, silvi...@gmail.com wrote:
> For the last 7 years we have been using a piece of software that's
> based on Access 2000. About two years ago I created several MS Word
> documents (merge files) that get data from this Access database and
> today these documents are a crucial part of our business, they are
> used 90% percent of the time and basically we can't live without them.
> The Access database has a major design flaw, it basically has one
> table that contains most of the data. A well designed database would
> have the same data in several tables. But this flaw turned out to be a
> good thing when creating the MS Word documents, since all the data
> needed for the documents are in one table. Everything works
> beautifully.
> Our software vendor released a new version of the software which uses
> a well designed SQL Database, and they split that table into several
> tables, as it should have been to begin with. The problem now is that
> the documents I created will not work with the new software, therefore
> we cannot upgrade a make use of all the other good things they
> implemented.
> I thought about creating an Access database and link it to the SQL
> database, and try to gather all the data needed into one table or
> query so I can point the merge documents to it.
> I know that's a long story, but I think that's the only way to be
> clear. I really need help with this, and I'm pretty sure there are
> other better ideas I can get here.
> Thank you.
> Silvio.
>
> www.silvioribeiro.com

Probably you could create an SQL View that would include the columns
needed for your merge documents. A View can be used exactly as a
table, can contain many columns from many tables, and even be indexed.
You can link to the View from the Access Database; I haven't written
any merges for many years but I expect the Access intermediate step is
entirely redundant and that you can access an SQL View directly from
Word.

Re: Combine Several Tables from SQL Server Into One Access Table

silvionr@gmail.com wrote:

> I thought about creating an Access database and link it to the SQL
> database, and try to gather all the data needed into one table or
> query so I can point the merge documents to it.

You're on the right track. Sounds like all you need to do is link the SQL
tables and create a query in your Access database (or link to a view in the
SQL Server database) that combines the data from the source tables. Then
use the query in your Word merge, or copy the data from the query into a
local table and use it with your merge.

And being nosy, I'm curious how Word docs are used so often and why they're
so crucial to your business.

Re: Combine Several Tables from SQL Server Into One Access Table

On Apr 3, 1:29 am, "DFS" wrote:
> silvi...@gmail.com wrote:
> > I thought about creating an Access database and link it to the SQL
> > database, and try to gather all the data needed into one table or
> > query so I can point the merge documents to it.
>
> You're on the right track. Sounds like all you need to do is link the SQL
> tables and create a query in your Access database (or link to a view in the
> SQL Server database) that combines the data from the source tables. Then
> use the query in your Word merge, or copy the data from the query into a
> local table and use it with your merge.
>
> And being nosy, I'm curious how Word docs are used so often and why they're
> so crucial to your business.


Thanks for your response. I just need to figure out how to do it now,
but It's good to know that I had the right idea in mind.


I can see how someone can be curious about Word documents being a
"crucial" part of a business. I work for a funeral home, and all the
documents that are generated for a funeral, including government
forms, handout pamphlets with funeral information, register books,
invoices, etc, etc, are all done using MS Word docs, which get data
from the access database. We have an average of about 20 funerals a
week, so you can have an idea.

Silvio

Re: Combine Several Tables from SQL Server Into One Access Table

On Apr 3, 1:00 am, lyle wrote:
> On Apr 3, 12:56 am, silvi...@gmail.com wrote:
>
>
>
> > For the last 7 years we have been using a piece of software that's
> > based on Access 2000. About two years ago I created several MS Word
> > documents (merge files) that get data from this Access database and
> > today these documents are a crucial part of our business, they are
> > used 90% percent of the time and basically we can't live without them.
> > The Access database has a major design flaw, it basically has one
> > table that contains most of the data. A well designed database would
> > have the same data in several tables. But this flaw turned out to be a
> > good thing when creating the MS Word documents, since all the data
> > needed for the documents are in one table. Everything works
> > beautifully.
> > Our software vendor released a new version of the software which uses
> > a well designed SQL Database, and they split that table into several
> > tables, as it should have been to begin with. The problem now is that
> > the documents I created will not work with the new software, therefore
> > we cannot upgrade a make use of all the other good things they
> > implemented.
> > I thought about creating an Access database and link it to the SQL
> > database, and try to gather all the data needed into one table or
> > query so I can point the merge documents to it.
> > I know that's a long story, but I think that's the only way to be
> > clear. I really need help with this, and I'm pretty sure there are
> > other better ideas I can get here.
> > Thank you.
> > Silvio.
>
> >www.silvioribeiro.com
>
> Probably you could create an SQL View that would include the columns
> needed for your merge documents. A View can be used exactly as a
> table, can contain many columns from many tables, and even be indexed.
> You can link to the View from the Access Database; I haven't written
> any merges for many years but I expect the Access intermediate step is
> entirely redundant and that you can access an SQL View directly from
> Word.

You're right, I don't really have to use Access as a bridge, I just
didn't want to mess with the SQL dabatase

Thank you