Get Connection string for Pass through queries
I have just learned about the pass through queries in
I have a
SQL SERVER backend and
if I’m right, for a query access loads all records before to do the where clause… so what would be the point of having a
SQL SERVER backend?
That’s why I want to try using pass through queries as much as possible but is there a way I can get the connection string from my linked tables for my pass through queries?
CurrentDb.TableDefs("One of my table name").Connect in the
ODBC Connect Str property but I got the error saying it’s an invalid connection string.
It would be nice because I know I will have to change the connection soon so I wouldn’t have to edit the connection string at many places.
One Solution collect form web for “Get Connection string for Pass through queries”
I’m not sure what you meant here: “for a query access loads all records before to do the where clause“
WHERE clause can be applied at the server, ODBC will translate it to the server’s language, and only the matching rows will be sent back to Access:
WHERE date_field >= #2011-01-01# AND date_field < #2012-01-01#
WHERE clause would limit the rows sent to Access to only those whose
date_field values are from 2011.
However, if a
WHERE clause includes functions which must be evaluated by Access, ODBC must retrieve all candidate rows and hand them over to the Access db engine so it can perform the evaluation.
WHERE Format(date_field, 'yyyy') = '2011'
But for your actual question … connection string for pass through queries … consider the following code example. I have an ODBC link named
dbo_foo whose source table in SQL Server is
[dbo].[foo]. So I can grab the
.Connect property from
dbo_foo and use it for the
.Connect property of a pass through query based on the same server table.
Public Sub CreatePassThruQuery() Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strConnect As String Set db = CurrentDb strConnect = db.TableDefs("dbo_foo").Connect Set qdf = db.CreateQueryDef("qryDbo_Foo") qdf.Connect = strConnect qdf.SQL = "SELECT * FROM [dbo].[foo];" qdf.Close Set qdf = Nothing Set db = Nothing End Sub
Still when you change the
.Connect property of the table, you will also need to do it for the query. If you have many of them and/or change the connections frequently, it may be worth the effort to create a VBA procedure to update them. Alternatively, you might use a DSN for the
.Connect property of the table and matching query. Then revise the DSN as needed. A pitfall with that approach is that, if other people will be using your application, you would need to manage the DSNs on multiple machines.