SQL vs. Access

[Nich]

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?

 

[Peter Jamieson]

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]

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]

Something to bear in mind when deciding where to locate a query is "where
are the joins, sorts, and filters actually being performed". I can't say I
know the rules in depth, but typically if you are using /linked/ tables in
Access and you're not using a "pass-through" type query, then Access could
be reading all the data it "thinks" is necessary to do the necessary
joining/filtering, which may require a lot of data to pass between our SQL
database and Access, which could I assume lead to behaviour which either
looks like a hang (because the query takes a long time to complete) or is a
hang (because of some limitation in Access).

Peter Jamieson