On Using Pass-through Queries in MS Access - Saving Connection information with the query
(Page 4 of 4 )
It is possible to store the connection information as follows: open the passthru query in the design view, then right click on the title of the query to open the next window. As you can see the ODBC connection string is not complete. Now click in the empty area to display an ellipsis button. When you click this button you will get the Select Data Source window. Choose the dsn file from there and click on the OK button. In the window that follows, provide the authentication information and click on the OK button. Now this connection string item gets filled, and if you save the query, the connection information will be saved with the query.
But before that you will get a message that asks whether you want the connections string saved with the query.
When you accept this, the following connectivity information will be saved with the query.
ODBC;Description = Using SQLClient for a Passthrough query;
DRIVER=SQL Native Client; SERVER=HODENTEK; UID=sa; PWD=XXXXXXX;
DATABASE=Northwind; LANGUAGE=us_english;
The password will be saved in clear text (but this has been replaced by the xxxxxxx in this document). Now you can run the query without needing to provide connectivity information. However, for security reasons connectivity information should not be saved with the query.
For the next passthrough query in the same database, you may copy and paste the connection string, but change the SQL query statement in the design pane to achieve the desired result as shown for this query, Top10.
Select top 10 *
from employees
where birthDate >'1-1-1960'
When yo run this query you should see the following result.
Summary
In connecting to a SQL server on another machine you will need authentication as well as permissions on the object. Even with these correctly entered, you may get errors if the TCP ports to the server are blocked for some security reason such as a firewall. Again, for security reasons, it is not advisable to store the authentication information with the query. A passthru query may be a better option than linked servers as it reduces an intermediate layer.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
