CODEWITHSUNDEEP
sql-serverms-accessms-access-2010
Marc
Marc

Reputation: 16512

Get Connection string for Pass through queries

I have just learned about the pass through queries in MS-ACCESS.

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?

I tried 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.

Thank you.

Upvotes: 1

Views: 5133

Answers (1)

HansUp
HansUp

Reputation: 97101

I'm not sure what you meant here: "for a query access loads all records before to do the where clause"

If the 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#

That 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.

Upvotes: 2

Related Questions