Access Query results not visible in Word

[Martin Folley]

Sometimes, when creating a query in Access and using it as a source for a
mail merge, the records are not appearing (but the field names are).

I say sometimes because the same database can have substantially the same
query, both of which work in Access, only one of which works in Word.

The point at which it fails is when linking the Word doc to the Access mdb
file, I choose a query and can then see the field names in the next box but
no fields. Choose a similar query from the same mdb file and both the fields
and the data are visible. Why should one work and not the other? and why
should they both work in Access but one fail within Word?

The Access database is being used by 15 students for the same exercise. They
copied the database from same place into their areas. 10 have no problems, 5
are having problems.

Any suggestions as to where I should look? All students have the same
permissions, all stations are identical. Moving stations does not help.


Access 2003, Word 2003, no filters are used in the Word merge).


Many thanks

Martin.

 

[Peter Jamieson]

Two possibilities are:
a. if workgroup security is in use, the user has access to one query
(or its underlying data) but not another.
b. the queries that fail have wildcard characters, e.g. in a LIKE
clauee. There are two sets of wildcard characters: Jet (*,?) and ANSI
SQL (%, _). Access typically uses the Jet ones, unless you modify the
database so that it recognises the ANSI ones. However, when you query
the underlying Jet database via ODBC or OLE DB (which do not actually
get their data via Access), the ANSI ones are recognised.

If that is the problem, you can
a. change the database so that it uses the ANSI wildcards. Hoever,
that can be scary if the database is used by a lot of different bits of code
b. If you check Word Tools->Options->General->Confirm conversions at
open , go through the process of connecting to Access again, and select
the DDE option, the Jet wildcards should work. But DDE connections have
their own problems
c.you can consider making copies of the queries that use ANSI rather
than Jet wiidcards.

Word will fail to connect to some other types of query -e.g. UNION
queries, queries that contain some other built-in VBA functions such as
replace, and many of the financial functions, parameter queries, and
queries that reference user-defined VBA. However, in at least some of
those cases the OLE DB connection method would not even list the query
when you try to connect.

Peter Jamieson

http://tips.pjmsn.me.uk