Click the three-dot symbol on theODBC Connect Strproperty and you can then select a
DSN to use. Click the Machine Data Source tab and scroll down to your DSN.
You will then be asked if you want the password included in the connection string, which
raises a security issue. If you do not include it, the user will be prompted for the password
every time the query is run, which could be annoying. If you include it in the connection
string, anyone can see it.
A solution is to include it in the connection string, but to lock down the database so query
structures cannot be accessed. See Chapter 24 on how to do this.
A further problem is that you cannot build your query using the Access GUI. You must
write your query in SQL and use the same notation that the database platform is expecting.
This is because your query will run directly on the database server.
If you are connecting to Oracle or SQL Server, you need to use a percent sign (%)
instead of a star (*) for wild card characters and use single quotes instead of double quotes
to denote strings. Also, you cannot use IIF. Instead, you must use the notation CASE
WHEN.....THEN.....ELSE.....END.
Chapter 19: Working with External Databases 247
Figure 19-6 Setting up a pass-through query