Can't see all of the queries when merging to MS Word and can't get to reports

[Jerry Hull]

I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are
several hundred queries, for example, with no indication of where they
are used or if they are in fact necessary at all...

The database is for the local Hospice service, and their entire
operation is based on it - from patient records to donations.

The database was developed in Access 2K and has not been converted, so
it's being opened as an Access 2000 database in Access 2003. I tried
converting to 2003, and didn't see any change in the bothersome
behavior.

There are several letters in MS Word that use the database as the
source data for mail merges. Until the conversion, everything worked
fine, but with the conversion, it became necessary to specify new
locations for the source data for the merges. In MS Word 2003, when I
try to open a new datasource, I can specify the MS Access database,
but the format of the dialog box is significantly different than I am
used to. I am presented with a list of "Views" and tables, with the
Views apparently corresponding to the queries in the database.

The difficulty is that, unlike in Access 2K, I don't seem to be seeing
any query that contain parameters - at least I think that's why I'm
not seeing all of the queries. I do see all of the tables, so my
workaround has been to create make-table queries based on the old
parameter queries, and specify those tables as the datasources for the
merges. That works, but involves lots of work with making new
make-table queries, and new macros that call the new queries, and
there are lots more merge document that need to be dealt with.

Also - I can't seem to open any reports. If I try to get to design
view from the reports tab, nothing happens - no error message or
anything. If I try to open a report from a button on a form that uses
code, I get a message the "The Open Report Action was cancelled".

Help!!!!?????

[Duane Phillips]

Hard to help you without more specific info. Any answer to your post here
would be speculative.

Question #1: What database backend is being used (JET? MS SQL Server?
Oracle?)?
Question #2: If JET, are you using front-end/backend, or single file
scenario? (I *highly* recommend the former).
Question #3: Why would you use make-table queries to run reports? (these
will inflate your database dramatically, are *not* friendly or practical in
multi-user environments, and cause more maintenance hassles).
Question #4: The report design problem needs more detail. Are you able to
see the report designs in the MSA2K version?

Personally, I'd recommend getting more specialized on-hands help for your MS
Access dilemma.

HTH.
~ Duane Phillips.

"Jerry Hull" <st******@peak.org> wrote in message
news:58**************************@posting.google.c om...

I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are
several hundred queries, for example, with no indication of where they
are used or if they are in fact necessary at all...

The database is for the local Hospice service, and their entire
operation is based on it - from patient records to donations.

The database was developed in Access 2K and has not been converted, so
it's being opened as an Access 2000 database in Access 2003. I tried
converting to 2003, and didn't see any change in the bothersome
behavior.

There are several letters in MS Word that use the database as the
source data for mail merges. Until the conversion, everything worked
fine, but with the conversion, it became necessary to specify new
locations for the source data for the merges. In MS Word 2003, when I
try to open a new datasource, I can specify the MS Access database,
but the format of the dialog box is significantly different than I am
used to. I am presented with a list of "Views" and tables, with the
Views apparently corresponding to the queries in the database.

The difficulty is that, unlike in Access 2K, I don't seem to be seeing
any query that contain parameters - at least I think that's why I'm
not seeing all of the queries. I do see all of the tables, so my
workaround has been to create make-table queries based on the old
parameter queries, and specify those tables as the datasources for the
merges. That works, but involves lots of work with making new
make-table queries, and new macros that call the new queries, and
there are lots more merge document that need to be dealt with.

Also - I can't seem to open any reports. If I try to get to design
view from the reports tab, nothing happens - no error message or
anything. If I try to open a report from a button on a form that uses
code, I get a message the "The Open Report Action was cancelled".

Help!!!!?????

[david epsom dot com dot au]

There are three ways to get Mail Merge data from an Access database:

DDE (office 6)
ODBC (office 95)
OLEDB (office 2000).

All three methods are still supported, you just have to navigate
through Word to find them. From your description, you are using
OLEDB, and can only see queries which are valid views in the OLEDB
connection. I think that you will have better luck with an ODBC
connection, but I personally don't do it that way: I automate from
the Access side, and I always start mail merges with an export to
a spreadsheet, csv, or a word (rtf) table.

I'm not really familiar with Word (you would be better off in a
Word group), but perhaps you could look for the "Select Method"
checkbox in the "Open Data Source" dialog box.

I don't know what the problem is with the reports. Make a backup
copy of the database, then de-compile or import all objects into
a new database.

(david)

"Jerry Hull" <st******@peak.org> wrote in message
news:58**************************@posting.google.c om...

I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are
several hundred queries, for example, with no indication of where they
are used or if they are in fact necessary at all...

The database is for the local Hospice service, and their entire
operation is based on it - from patient records to donations.

The database was developed in Access 2K and has not been converted, so
it's being opened as an Access 2000 database in Access 2003. I tried
converting to 2003, and didn't see any change in the bothersome
behavior.

There are several letters in MS Word that use the database as the
source data for mail merges. Until the conversion, everything worked
fine, but with the conversion, it became necessary to specify new
locations for the source data for the merges. In MS Word 2003, when I
try to open a new datasource, I can specify the MS Access database,
but the format of the dialog box is significantly different than I am
used to. I am presented with a list of "Views" and tables, with the
Views apparently corresponding to the queries in the database.

The difficulty is that, unlike in Access 2K, I don't seem to be seeing
any query that contain parameters - at least I think that's why I'm
not seeing all of the queries. I do see all of the tables, so my
workaround has been to create make-table queries based on the old
parameter queries, and specify those tables as the datasources for the
merges. That works, but involves lots of work with making new
make-table queries, and new macros that call the new queries, and
there are lots more merge document that need to be dealt with.

Also - I can't seem to open any reports. If I try to get to design
view from the reports tab, nothing happens - no error message or
anything. If I try to open a report from a button on a form that uses
code, I get a message the "The Open Report Action was cancelled".

Help!!!!?????

[Jerry Hull]

Thanks for the suggestions from those who responded - Here's what I've
discovered:

Re: couldn't see parameter queries from Word 2003:
MS Word 2003 will show the appropriate dialog boxes to let you choose
parameter queries as the data source for mail merges if, in word, you:
Select Options from the Tools menu
Click the General Tab
Place a check mark next to "Confirm conversion at Open"

After this (which only needs to be done once), you will get a dialog
box that lists tabs at the top for tables and queries, and you'll be
able to select parmeter queries.

Re: couldn't open reports
The problem seemed to be with the default network printer that was
somehow confusing MSAccess. Following the instructions of the network
administrator, we removed the network connection from the printer and
reconnected it as a local printer. Now the reports print fine - but
we're still not sure what causes the problem with the network
connected printer.

Re:questions and comments from responders

Question #1: What database backend is being used (JET? MS SQL Server?
Oracle?)?
JET

Question #2: If JET, are you using front-end/backend, or single file
scenario? (I *highly* recommend the former).
Since I inherited the database, and it's a real mish-mash, I haven't
split the database yet. First there's got to be a redesign, new
junction tables, importing and massaging existing data, redirecting
forms and reports, etc.

Question #3: Why would you use make-table queries to run reports?
(these will inflate your database dramatically, are *not* friendly or
practical in multi-user environments, and cause more maintenance
hassles).
Agreed - I wouldn't use make-table queries for reports. I used these
as a panic-induced solution to let the folks get out the mail merges
they needed (not reports). Now I don't even need those. The previous
designer did use some make-table queries, and I'll be doing away with
those as soon as I can.

Question #4: The report design problem needs more detail. Are you
able to see the report designs in the MSA2K version?
Nope - but that was apparently a printer issue, and that's been dealt
with .

Personally, I'd recommend getting more specialized on-hands help for
your MS Access dilemma.
That may be a necessity in the future - but there's the problem of
cost (I'm volunteering my time) and availability (we don't have a lot
of Access developers here in the local area)

HTH.
~ Duane Phillips.
There are three ways to get Mail Merge data from an Access database:

DDE (office 6)
ODBC (office 95)
OLEDB (office 2000).

All three methods are still supported, you just have to navigate
through Word to find them. From your description, you are using
OLEDB, and can only see queries which are valid views in the OLEDB
connection. I think that you will have better luck with an ODBC
connection, but I personally don't do it that way: I automate from the
Access side, and I always start mail merges with an export to a
spreadsheet, csv, or a word (rtf) table.

We ended up with DDE connections, which seem to work.

I'm not really familiar with Word (you would be better off in a Word
group), but perhaps you could look for the "Select Method" checkbox in
the "Open Data Source" dialog box.

Sort of what we did with the Confirm conversion at Open

I don't know what the problem is with the reports. Make a backup copy
of the database, then de-compile or import all objects into a new
database.

Didn't do it - but then, it was the printer thing....

David Epsom
st******@peak.org (Jerry Hull) wrote in message news:<58**************************@posting.google. com>...

Original Post:

I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are
several hundred queries, for example, with no indication of where they
are used or if they are in fact necessary at all...

The database is for the local Hospice service, and their entire
operation is based on it - from patient records to donations.

The database was developed in Access 2K and has not been converted, so
it's being opened as an Access 2000 database in Access 2003. I tried
converting to 2003, and didn't see any change in the bothersome
behavior.

There are several letters in MS Word that use the database as the
source data for mail merges. Until the conversion, everything worked
fine, but with the conversion, it became necessary to specify new
locations for the source data for the merges. In MS Word 2003, when I
try to open a new datasource, I can specify the MS Access database,
but the format of the dialog box is significantly different than I am
used to. I am presented with a list of "Views" and tables, with the
Views apparently corresponding to the queries in the database.

The difficulty is that, unlike in Access 2K, I don't seem to be seeing
any query that contain parameters - at least I think that's why I'm
not seeing all of the queries. I do see all of the tables, so my
workaround has been to create make-table queries based on the old
parameter queries, and specify those tables as the datasources for the
merges. That works, but involves lots of work with making new
make-table queries, and new macros that call the new queries, and
there are lots more merge document that need to be dealt with.

Also - I can't seem to open any reports. If I try to get to design
view from the reports tab, nothing happens - no error message or
anything. If I try to open a report from a button on a form that uses
code, I get a message the "The Open Report Action was cancelled".

Help!!!!?????

[Duane Phillips]

> That may be a necessity in the future - but there's the problem

of cost (I'm volunteering my time) and availability (we don't have
a lot of Access developers here in the local area)
What's the general area?
Sounds like you are working with a non-profit org?
There are those here who do sometimes contribute some effort, depending on
the cause and needs. You won't likely get the moon, but you may find some
willing and able to help. Also, the developer does not necessarily need to
be a local (especially with JET databases)... just someone you can trust.
Quite a few developers here have remote clientelle.

~ Duane Phillips.

"Jerry Hull" <st******@peak.org> wrote in message
news:58**************************@posting.google.c om... Thanks for the suggestions from those who responded - Here's what I've
discovered:

Re: couldn't see parameter queries from Word 2003:
MS Word 2003 will show the appropriate dialog boxes to let you choose
parameter queries as the data source for mail merges if, in word, you:
Select Options from the Tools menu
Click the General Tab
Place a check mark next to "Confirm conversion at Open"

After this (which only needs to be done once), you will get a dialog
box that lists tabs at the top for tables and queries, and you'll be
able to select parmeter queries.

Re: couldn't open reports
The problem seemed to be with the default network printer that was
somehow confusing MSAccess. Following the instructions of the network
administrator, we removed the network connection from the printer and
reconnected it as a local printer. Now the reports print fine - but
we're still not sure what causes the problem with the network
connected printer.

Re:questions and comments from responders

Question #1: What database backend is being used (JET? MS SQL Server?
Oracle?)?
JET

Question #2: If JET, are you using front-end/backend, or single file
scenario? (I *highly* recommend the former).
Since I inherited the database, and it's a real mish-mash, I haven't
split the database yet. First there's got to be a redesign, new
junction tables, importing and massaging existing data, redirecting
forms and reports, etc.

Question #3: Why would you use make-table queries to run reports?
(these will inflate your database dramatically, are *not* friendly or
practical in multi-user environments, and cause more maintenance
hassles).
Agreed - I wouldn't use make-table queries for reports. I used these
as a panic-induced solution to let the folks get out the mail merges
they needed (not reports). Now I don't even need those. The previous
designer did use some make-table queries, and I'll be doing away with
those as soon as I can.

Question #4: The report design problem needs more detail. Are you
able to see the report designs in the MSA2K version?
Nope - but that was apparently a printer issue, and that's been dealt
with .

Personally, I'd recommend getting more specialized on-hands help for
your MS Access dilemma.
That may be a necessity in the future - but there's the problem of
cost (I'm volunteering my time) and availability (we don't have a lot
of Access developers here in the local area)

HTH.
~ Duane Phillips.
There are three ways to get Mail Merge data from an Access database:

DDE (office 6)
ODBC (office 95)
OLEDB (office 2000).

All three methods are still supported, you just have to navigate
through Word to find them. From your description, you are using
OLEDB, and can only see queries which are valid views in the OLEDB
connection. I think that you will have better luck with an ODBC
connection, but I personally don't do it that way: I automate from the
Access side, and I always start mail merges with an export to a
spreadsheet, csv, or a word (rtf) table.

We ended up with DDE connections, which seem to work.

I'm not really familiar with Word (you would be better off in a Word
group), but perhaps you could look for the "Select Method" checkbox in
the "Open Data Source" dialog box.

Sort of what we did with the Confirm conversion at Open

I don't know what the problem is with the reports. Make a backup copy
of the database, then de-compile or import all objects into a new
database.

Didn't do it - but then, it was the printer thing....

David Epsom
st******@peak.org (Jerry Hull) wrote in message
news:<58**************************@posting.google. com>...

Original Post:

I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are
several hundred queries, for example, with no indication of where they
are used or if they are in fact necessary at all...

The database is for the local Hospice service, and their entire
operation is based on it - from patient records to donations.

The database was developed in Access 2K and has not been converted, so
it's being opened as an Access 2000 database in Access 2003. I tried
converting to 2003, and didn't see any change in the bothersome
behavior.

There are several letters in MS Word that use the database as the
source data for mail merges. Until the conversion, everything worked
fine, but with the conversion, it became necessary to specify new
locations for the source data for the merges. In MS Word 2003, when I
try to open a new datasource, I can specify the MS Access database,
but the format of the dialog box is significantly different than I am
used to. I am presented with a list of "Views" and tables, with the
Views apparently corresponding to the queries in the database.

The difficulty is that, unlike in Access 2K, I don't seem to be seeing
any query that contain parameters - at least I think that's why I'm
not seeing all of the queries. I do see all of the tables, so my
workaround has been to create make-table queries based on the old
parameter queries, and specify those tables as the datasources for the
merges. That works, but involves lots of work with making new
make-table queries, and new macros that call the new queries, and
there are lots more merge document that need to be dealt with.

Also - I can't seem to open any reports. If I try to get to design
view from the reports tab, nothing happens - no error message or
anything. If I try to open a report from a button on a form that uses
code, I get a message the "The Open Report Action was cancelled".

Help!!!!?????

[Jerry Hull]

Thanks for the reply! The general area is Corvallis, Oregon. The
nearest source of qualified Access programmers is Portland, about 90
miles to the north.

And yes, it's a non-profit. Hospice of Benton county works to provide
care for those who have been medically certified to have less than 6
months to live. And I am working for nothing, as a volunteer.
They're delighted to have me (I'm sure you know how little you really
have to know to be considered an expert), and I'm learning. I do have
some experience and a basic grasp of design priciples, so I think I
can get a bit further before I need to call in the big guns - but it's
a real comfort knowing that this group exists. It's already been very
helpful - Thanks!!!

Jerry Hull
Starship Enterprises
st******@peak.org

"Duane Phillips" <as***@askme.askme> wrote in message news:<eA***************@news.uswest.net>...

That may be a necessity in the future - but there's the problem
of cost (I'm volunteering my time) and availability (we don't have
a lot of Access developers here in the local area)

What's the general area?
Sounds like you are working with a non-profit org?
There are those here who do sometimes contribute some effort, depending on
the cause and needs. You won't likely get the moon, but you may find some
willing and able to help. Also, the developer does not necessarily need to
be a local (especially with JET databases)... just someone you can trust.
Quite a few developers here have remote clientelle.

~ Duane Phillips.

"Jerry Hull" <st******@peak.org> wrote in message
news:58**************************@posting.google.c om...

Thanks for the suggestions from those who responded - Here's what I've
discovered:

Re: couldn't see parameter queries from Word 2003:
MS Word 2003 will show the appropriate dialog boxes to let you choose
parameter queries as the data source for mail merges if, in word, you:
Select Options from the Tools menu
Click the General Tab
Place a check mark next to "Confirm conversion at Open"

After this (which only needs to be done once), you will get a dialog
box that lists tabs at the top for tables and queries, and you'll be
able to select parmeter queries.

Re: couldn't open reports
The problem seemed to be with the default network printer that was
somehow confusing MSAccess. Following the instructions of the network
administrator, we removed the network connection from the printer and
reconnected it as a local printer. Now the reports print fine - but
we're still not sure what causes the problem with the network
connected printer.

Re:questions and comments from responders

Question #1: What database backend is being used (JET? MS SQL Server?
Oracle?)?
JET

Question #2: If JET, are you using front-end/backend, or single file
scenario? (I *highly* recommend the former).
Since I inherited the database, and it's a real mish-mash, I haven't
split the database yet. First there's got to be a redesign, new
junction tables, importing and massaging existing data, redirecting
forms and reports, etc.

Question #3: Why would you use make-table queries to run reports?
(these will inflate your database dramatically, are *not* friendly or
practical in multi-user environments, and cause more maintenance
hassles).
Agreed - I wouldn't use make-table queries for reports. I used these
as a panic-induced solution to let the folks get out the mail merges
they needed (not reports). Now I don't even need those. The previous
designer did use some make-table queries, and I'll be doing away with
those as soon as I can.

Question #4: The report design problem needs more detail. Are you
able to see the report designs in the MSA2K version?
Nope - but that was apparently a printer issue, and that's been dealt
with .

Personally, I'd recommend getting more specialized on-hands help for
your MS Access dilemma.
That may be a necessity in the future - but there's the problem of
cost (I'm volunteering my time) and availability (we don't have a lot
of Access developers here in the local area)

HTH.
~ Duane Phillips.
There are three ways to get Mail Merge data from an Access database:

DDE (office 6)
ODBC (office 95)
OLEDB (office 2000).

All three methods are still supported, you just have to navigate
through Word to find them. From your description, you are using
OLEDB, and can only see queries which are valid views in the OLEDB
connection. I think that you will have better luck with an ODBC
connection, but I personally don't do it that way: I automate from the
Access side, and I always start mail merges with an export to a
spreadsheet, csv, or a word (rtf) table.

We ended up with DDE connections, which seem to work.

I'm not really familiar with Word (you would be better off in a Word
group), but perhaps you could look for the "Select Method" checkbox in
the "Open Data Source" dialog box.

Sort of what we did with the Confirm conversion at Open

I don't know what the problem is with the reports. Make a backup copy
of the database, then de-compile or import all objects into a new
database.

Didn't do it - but then, it was the printer thing....

David Epsom
st******@peak.org (Jerry Hull) wrote in message
news:<58**************************@posting.google. com>...

Original Post:

I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are
several hundred queries, for example, with no indication of where they
are used or if they are in fact necessary at all...

The database is for the local Hospice service, and their entire
operation is based on it - from patient records to donations.

The database was developed in Access 2K and has not been converted, so
it's being opened as an Access 2000 database in Access 2003. I tried
converting to 2003, and didn't see any change in the bothersome
behavior.

There are several letters in MS Word that use the database as the
source data for mail merges. Until the conversion, everything worked
fine, but with the conversion, it became necessary to specify new
locations for the source data for the merges. In MS Word 2003, when I
try to open a new datasource, I can specify the MS Access database,
but the format of the dialog box is significantly different than I am
used to. I am presented with a list of "Views" and tables, with the
Views apparently corresponding to the queries in the database.

The difficulty is that, unlike in Access 2K, I don't seem to be seeing
any query that contain parameters - at least I think that's why I'm
not seeing all of the queries. I do see all of the tables, so my
workaround has been to create make-table queries based on the old
parameter queries, and specify those tables as the datasources for the
merges. That works, but involves lots of work with making new
make-table queries, and new macros that call the new queries, and
there are lots more merge document that need to be dealt with.

Also - I can't seem to open any reports. If I try to get to design
view from the reports tab, nothing happens - no error message or
anything. If I try to open a report from a button on a form that uses
code, I get a message the "The Open Report Action was cancelled".

Help!!!!?????