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

    Unanswered: Field variables to pass-through queries?

    I've added this to an existing question, but I still can't find a workaround for this. I need to run a pass-through query with criteria based data in a form's fields. How do I do this?

    For instance, if form1 has a date in field1, how do I run a pass-through query like "SELECT * from SQLServerTable WHERE date > [forms].[form1].[field1]?

    I realize that sql-server is expecing a different date format, but the field itself seems to be generating errors (because the server can't see the values in the fields).

    Thanks.

  2. #2
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    it's possible.
    I think you can use
    SELECT * from SQLServerTable WHERE date > Form_[form1].[field1]

  3. #3
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    I believe it should be:

    [Forms]![form1]![field1]

  4. #4
    Join Date
    Nov 2003
    Posts
    167
    I figured it out.

    Just to recap: with pass-through queries you are passing the sql directly to the server. Thus, not only do you have to use the flavor of sql of your server (Oracle, SQL Server, etc), but you also cannot use the contents of form fields because the server has no access to those forms.

    The solution I used was to change the definition of the query each time the form fields changed. The code looks like this:

    Private Sub Combo1_AfterUpdate()
    Dim strSQL as string
    Set db = Currentdb
    strSQL = "SELECT * from SQLserverTable WHERE criteria = '" & [Forms]![Form1].[Combo1] & "';"
    db.Querydefs("SamplePassThrough").SQL = strSQL
    End Sub

    Man, you wouldn't think such a short bit of code would hang me up so long. From what I understand, I can further speed up the code using ODBC direct and skipping the CurrentDB call - but no need to be greedy. I'm happy cutting this 2 minute 8 second query to 1 second (that's right, a single second to run a query that was taking over 2 minutes!).

Posting Permissions

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