[Nich]

Thank you for your in-depth answer, Peter. All queries are in the
front-end and run fine from Access. However, Word hangs when I try to attach
to the Access query as a data source. I will try to right the somewhat
complicated query (Left-Join and logic sorts) in SQL and see if it works.

"Peter Jamieson" wrote:

The query's obviously going to be SQL whether you write it "in Access" or
"in the SQL database". Which is "better" depends on a number of things, e.g.
a. feasibility
b. security
c. ease of maintenance
d. performance

(a). Feasibility: Typically you would write a query in Access or a view in
your SQL database. Unless it's particularly complex, personllay I'd just
write both and see what's required to connect. To connect to your SQL
database you will either need an OLEDB connection (you will probably need to
set up a .odc file that each computer using the view will need) or an older
ODBC connection, requiring either an ODBC DSN or .dsn file. Personally I
have had a lot of difficulty connecting to SQL Server 2005 databases from
Word using OLEDB (e.g. you can construct a .odc that works fine from Excel
but not from Word).
(b) Security. If you are using Windows Integrated Security, there should
not be problems in this area as long as users have the SQL database
permissions they need. If you are not, then you may end up having to insert
plain text logon/password information into your Word file or a .odc file.
I'd suggest that doing it via Access might be better in that case, although
even that might pose a security risk.
(c) ease of maintenance. You might be using your SQL database purely as a
data store and want to build all other types of code (including queries) in
Access. Or maybe you want to move as much "business logic" including
queries, reports etc. to your SQL database. Your choice, really.
(d) Performance. As a general rule, most things are lilkley to perform
better as SQL database queries/views, but for any given query the difference
may not be large enough to override other benefits of putting the query in
Access. Try it and see.

Peter Jamieson

"Nich" wrote in message
...
We recently moved our database from Access to SQL. The SQL database is
linked
to an Access front-end. Is it better to write the query for the merge in
SQL
or Access?