Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Unanswered: Jet and overhead

    Can anyone tell me about Access and overhead? Specifically, if I have an Access db stored on the network which is a front-end to several SQL server databases, does running queries from my local machine result in full tables scans being transferred between the network access db, the SQL Server data sources, and ultimately my local machine?

    I've looked on the web but I'm getting somewhat contradictory answers on this.

    For instance, if I run the query, "Select * from Emp where LastName = 'Doe';" on a network Access db where Emp is a linked SQL Server table, does access transport and scan the entire Emp table before applying the criteria, or does it apply the criteria first and only transport those records where the criteria applies?

    Any webpages that can help me out?

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    frankly, linked tables are not the way to go with a SQL-serving backend.

    at the very very least, use a pass-thru query so the server does the work and your network is only hammered by the results you want.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2003
    Posts
    167
    I'd love to use a pass-through query to hit the SQL-server backend with the TSQL equivalent of my JET queries - but alot of my query criteria are fields from forms. How do I pass the values in these fields in the pass-through?

    For instance, say forms.form1.field1 is a date criteria. How do I get the query "Select * from SQLtable WHERE SQLtable.Date = forms.form1.field1;" to work? I keep getting errors right now. Maybe the square brackets are a no-no. I'll keep plugging away ...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    pass thru queries are NOT parsed by access (that's the whole point of pass thru after all).

    so you can't embed forms!this!that in the query SQL itself. you can prepare an SQL string embedding form values, but then you have to find somewhere to stick the SQL string.

    ...for example in a temporary passthru action query:
    dim wksp as dao.workspace
    dim dabs as dao.database
    dim strSQL as string
    strSQL = "UPDATE tblName SET tblName.NumField = " & Me.thisNumber
    strSQL = strSQL & " WHERE tblName.IDfield = " & Me.thatNumber & ";"
    set wksp = dbengine(0)
    set dabs = wksp.opendatabase("", False, False, gloStrConn)
    'gloStrConn is my connection string
    dabs.execute strSQL, dbSQLpassThrough

    ...for example in a saved access passthru, open the passthru querydef in code and hack the .SQL
    Dim strSQL As String
    Dim dabs As DAO.Database
    Dim qdef As DAO.querydef
    Set dabs = CurrentDb
    Set qdef = dabs.QueryDefs("yourPassThruQuery")
    strSQL = "SELECT blah FROM this "
    strSQL = strSQL & "WHERE myText = '" & forms!thisform!thisTextBox & "';"
    qdef.SQL = strSQL
    qdef.Close
    the difference is that the embedded references to forms etc are resolved when the SQL string is created (not when processing the pass thru - which never happens since they are not parsed)
    the hacked pass thru query now works fine as a .rowsource or .recordsource or food for a recordset or whatever you want to do with a query.
    i also cheat a bit with a little function that rips the WHERE clause off .SQL so i don't have to type too much crap, just replace the WHERE
    i.e. qdef.strSQL = stripWhere(qdef.SQL) & " WHERE blah = " & forms!etc etc etc

    ...it doesn't need to be a full SQL-query: could be a stored proc with parameters. same principle applies: you need somewhere to stick your resolved parameters for the sp.

    both examples are DAO, but ADO equivalents exist.

    stick with it and kill those linked tables - they are wrecking the performance of your app.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •