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?

  • Is there and alternative to LIKE statement in T-SQL?
  • Can't execute a COMPUTE statement
  • SQL rows to 2 columns
  • Update with Join
  • Returning multiple values from a stored procedure
  • Can someone help me simplify/speed up this Function?
  • 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.

    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

    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];"
        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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.