You might want to send a SQL specific query in Microsoft Access directly to the underlying ODBC database server to improve performance. You may do this by using a pass-through query. Running such queries is the topic of this tutorial.
In earlier articles we discussed several options for working with a database backend with user interface objects created in MS Access. We considered this in the context of migrating an MS Access database to other database servers like SQL 2000, SQL 2005, and SQL Anywhere. If you want to send your SQL specific queries in Microsoft Access directly to the underlying ODBC database server, bypassing the Microsoft Jet 4.0 engine, you may do so by using a pass-through query. Removing interaction with one layer (the Jet Engine) between the client and server should improve performance. However, the syntax for the queries will be SQL specific and does not support the Jet syntax anymore.
This tutorial shows you how you may run a passthrough query on a database in the SQL 2005 server using the SQL Native Client.
Creating a Passthrough Query in Design
Create a new MS Access Database, PassThru.mdb
Highlight Queries in the left-hand objects pane, and click on New to create a query in design view.
Close the Show Table window, which may show up, by clicking on the close button. Right click on an empty area in the design and click on pass-through from the drop-down, SQL Specific -->Pass-Through as shown.
Close the Window labeled Query1:SQL Pass-Through Query.
Accept to save query. When asked for a name, provide a name; here it is passthru.
You will see the icon of the pass-through query, passthru, in the queries collection
Double click the icon to open the Select Data Source window as shown.
Click on New... to open the next window.
In the Create New Data Source window, scroll down and select SQL Native Client . We will connect to a Northwind database on the SQL 2005 server. Make sure the SQL Server is on. Click on Next to see the following window.
Provide a name to the source, MSClient, and click Next. You will see a summary of your actions thus far in the next window.
Click Finish. This opens up the next window.
Here you provide a description for your datasource as shown, and from the drop-down select HODENTEK (the name of the machine on which the SQL 2005 server is installed). Click Next. In the following window provide the SQL authentication information. If your system accepts Windows NT authentication you can just click Next.
Click Next after typing in the password and Login ID. In the next window, choose "Northwind" from the databases, since the default is "master;" accept the other defaults.
Click Next. In the next window you may change information regarding the log file. Here defaults are used.
Click Finish. This takes you to the final summary screen where you may test the connectivity. You should immediately see the test results.
Click OK twice, once for each open window. You will return to the Select data Source window where you can now see your file data source, MSClient.dsn.
When you click on the OK button, you will get a login screen where you must again type in the login info.
When you click on the OK button after entering the required information, you may get an empty window for the passthru query. This is because there is no query statement in the design view.
The connectivity was established as per the previous procedure. We must now create a SQL query. Open the query in design view, type in a SELECT query as shown here, and close the window.
You will see the Select Data Source window once again.
Select MSClient.dsn and click on the OK button. You will again get a SQL Server Login screen as shown.
When you enter password information and click on the OK button you will see the result of running the query as shown.
Since the connection information is not completely stored with the query, every time the query is run, you need to supply the connection information. This means accessing the Select Data Source window, choosing your dsn and providing login information.
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.
Microsoft Access 
ASP.NET Export to Excel and Word using VB.NET and C#
