Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using a local table to create a Pass-Thru Query

Discussion in 'Information Technology' started by Cam808, Nov 17, 2005.

  1. Cam808

    Cam808
    Expand Collapse
    Guest

    How do you get the information out of a local table to incorperate it into a
    pass-thru query. Here is my example (simplified)

    One local table (A) with one value called DWE

    A table on Oracle (B) with many fields one of which is DWE

    the SQL needs to look like

    select * from DB.B where DWE in ('','','','')

    the ('','','','') should have the DWE values in the A table. I see that a
    join would work so if that is a possiblity I would be willing to use that as
    well. The only problem with the join that I have created is that it pulls
    all of table B over the network and process the request locally which ends up
    taking about 20 to 30 minutes. If don right it would take 5 to 10 seconds.

    Thanks,

    Cam
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 06:06:11 -0800, Cam808
    <Cam808@discussions.microsoft.com> wrote:

    >How do you get the information out of a local table to incorperate it into a
    >pass-thru query. Here is my example (simplified)
    >
    >One local table (A) with one value called DWE
    >
    >A table on Oracle (B) with many fields one of which is DWE
    >
    >the SQL needs to look like
    >
    >select * from DB.B where DWE in ('','','','')
    >
    >the ('','','','') should have the DWE values in the A table. I see that a
    >join would work so if that is a possiblity I would be willing to use that as
    >well. The only problem with the join that I have created is that it pulls
    >all of table B over the network and process the request locally which ends up
    >taking about 20 to 30 minutes. If don right it would take 5 to 10 seconds.
    >
    >Thanks,
    >
    >Cam


    You will probably need to build the SQL of the passthrough query in
    VBA code. The code at

    http://www.mvps.org/access/modules/mdl0004.htm

    should give you a start...

    John W. Vinson[MVP]
     
  4. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    Use a QueryDef object for the passthrough and perform the join against it,
    like so:

    Set qdf = CurrentDb.CreateQueryDef("OracleQuery")
    qdf.Connect = "oracle_connect_string"
    qdf.ReturnsRecords = True
    qdf.MaxRecords = 2147483647 '2^31 - 1 = ~2GB
    qdf.SQL = "SELECT * FROM DB.B"

    strSQL = "SELECT * FROM OracleQuery WHERE DWE IN (SELECT DWE FROM A)"

    'open recordset here etc.

    Replace "oracle_connect_string" with the connection string used by e.g. a
    linked table. I often set it like so:

    qdf.Connect = CurrentDb.TableDefs("some_linked_table_name").Connect

    This will improve the performance but I don't know about 5 - 10 seconds... I
    don't think that is realistic in this scenario!

    "Cam808" <Cam808@discussions.microsoft.com> wrote in message
    news:CF96A8B0-0EC3-41CF-BFD6-C988E1E7D92F@microsoft.com...
    > How do you get the information out of a local table to incorperate it into

    a
    > pass-thru query. Here is my example (simplified)
    >
    > One local table (A) with one value called DWE
    >
    > A table on Oracle (B) with many fields one of which is DWE
    >
    > the SQL needs to look like
    >
    > select * from DB.B where DWE in ('','','','')
    >
    > the ('','','','') should have the DWE values in the A table. I see that a
    > join would work so if that is a possiblity I would be willing to use that

    as
    > well. The only problem with the join that I have created is that it pulls
    > all of table B over the network and process the request locally which ends

    up
    > taking about 20 to 30 minutes. If don right it would take 5 to 10

    seconds.
    >
    > Thanks,
    >
    > Cam
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    But that's not a true passthrough, since it can't run entirely on the
    server.

    What Cam's looking for is something like:


    Set rs = CurrentDb.OpenRecordset("SELECT DWE FROM A")
    Do While rs.EOF = False
    strValues = strValues & rs!DWE & ", "
    rs.MoveNext
    End If

    If Len(strValues) > 0 Then
    ' Remove extraneous ", " from the end
    strValues = Left(strValues, Len(strValues) - 2)

    strSQL = "SELECT * FROM DB.B " & _
    "WHERE DWE IN (" & strValues & ")"

    Set qdf = CurrentDb.CreateQueryDef("OracleQuery")
    qdf.Connect = "oracle_connect_string"
    qdf.ReturnsRecords = True
    qdf.MaxRecords = 2147483647 '2^31 - 1 = ~2GB
    qdf.SQL = strSQL
    End If

    The above assumes that DWE is a numeric field. If it's text, you'll need to
    concatenate quotes around it:

    strValues = strValues & "'" & rs!DWE & "', "

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Ron Hinds" <__ron__dontspamme@wedontlikespam_garageiq.com> wrote in message
    news:u5QXtdu6FHA.1020@TK2MSFTNGP15.phx.gbl...
    > Use a QueryDef object for the passthrough and perform the join against it,
    > like so:
    >
    > Set qdf = CurrentDb.CreateQueryDef("OracleQuery")
    > qdf.Connect = "oracle_connect_string"
    > qdf.ReturnsRecords = True
    > qdf.MaxRecords = 2147483647 '2^31 - 1 = ~2GB
    > qdf.SQL = "SELECT * FROM DB.B"
    >
    > strSQL = "SELECT * FROM OracleQuery WHERE DWE IN (SELECT DWE FROM A)"
    >
    > 'open recordset here etc.
    >
    > Replace "oracle_connect_string" with the connection string used by e.g. a
    > linked table. I often set it like so:
    >
    > qdf.Connect = CurrentDb.TableDefs("some_linked_table_name").Connect
    >
    > This will improve the performance but I don't know about 5 - 10 seconds...

    I
    > don't think that is realistic in this scenario!
    >
    > "Cam808" <Cam808@discussions.microsoft.com> wrote in message
    > news:CF96A8B0-0EC3-41CF-BFD6-C988E1E7D92F@microsoft.com...
    > > How do you get the information out of a local table to incorperate it

    into
    > a
    > > pass-thru query. Here is my example (simplified)
    > >
    > > One local table (A) with one value called DWE
    > >
    > > A table on Oracle (B) with many fields one of which is DWE
    > >
    > > the SQL needs to look like
    > >
    > > select * from DB.B where DWE in ('','','','')
    > >
    > > the ('','','','') should have the DWE values in the A table. I see that

    a
    > > join would work so if that is a possiblity I would be willing to use

    that
    > as
    > > well. The only problem with the join that I have created is that it

    pulls
    > > all of table B over the network and process the request locally which

    ends
    > up
    > > taking about 20 to 30 minutes. If don right it would take 5 to 10

    > seconds.
    > >
    > > Thanks,
    > >
    > > Cam

    >
    >
     
  6. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    That's correct, but I've used both methods and in my testing (which may be
    specific only to my situation ;-) the other method performs better. However
    I would suggest the OP do their own testing to see what works best in their
    situation.

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23TkPnju6FHA.2092@TK2MSFTNGP12.phx.gbl...
    > But that's not a true passthrough, since it can't run entirely on the
    > server.
    >
    > What Cam's looking for is something like:
    >
    >
    > Set rs = CurrentDb.OpenRecordset("SELECT DWE FROM A")
    > Do While rs.EOF = False
    > strValues = strValues & rs!DWE & ", "
    > rs.MoveNext
    > End If
    >
    > If Len(strValues) > 0 Then
    > ' Remove extraneous ", " from the end
    > strValues = Left(strValues, Len(strValues) - 2)
    >
    > strSQL = "SELECT * FROM DB.B " & _
    > "WHERE DWE IN (" & strValues & ")"
    >
    > Set qdf = CurrentDb.CreateQueryDef("OracleQuery")
    > qdf.Connect = "oracle_connect_string"
    > qdf.ReturnsRecords = True
    > qdf.MaxRecords = 2147483647 '2^31 - 1 = ~2GB
    > qdf.SQL = strSQL
    > End If
    >
    > The above assumes that DWE is a numeric field. If it's text, you'll need

    to
    > concatenate quotes around it:
    >
    > strValues = strValues & "'" & rs!DWE & "', "
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Ron Hinds" <__ron__dontspamme@wedontlikespam_garageiq.com> wrote in

    message
    > news:u5QXtdu6FHA.1020@TK2MSFTNGP15.phx.gbl...
    > > Use a QueryDef object for the passthrough and perform the join against

    it,
    > > like so:
    > >
    > > Set qdf = CurrentDb.CreateQueryDef("OracleQuery")
    > > qdf.Connect = "oracle_connect_string"
    > > qdf.ReturnsRecords = True
    > > qdf.MaxRecords = 2147483647 '2^31 - 1 = ~2GB
    > > qdf.SQL = "SELECT * FROM DB.B"
    > >
    > > strSQL = "SELECT * FROM OracleQuery WHERE DWE IN (SELECT DWE FROM A)"
    > >
    > > 'open recordset here etc.
    > >
    > > Replace "oracle_connect_string" with the connection string used by e.g.

    a
    > > linked table. I often set it like so:
    > >
    > > qdf.Connect = CurrentDb.TableDefs("some_linked_table_name").Connect
    > >
    > > This will improve the performance but I don't know about 5 - 10

    seconds...
    > I
    > > don't think that is realistic in this scenario!
    > >
    > > "Cam808" <Cam808@discussions.microsoft.com> wrote in message
    > > news:CF96A8B0-0EC3-41CF-BFD6-C988E1E7D92F@microsoft.com...
    > > > How do you get the information out of a local table to incorperate it

    > into
    > > a
    > > > pass-thru query. Here is my example (simplified)
    > > >
    > > > One local table (A) with one value called DWE
    > > >
    > > > A table on Oracle (B) with many fields one of which is DWE
    > > >
    > > > the SQL needs to look like
    > > >
    > > > select * from DB.B where DWE in ('','','','')
    > > >
    > > > the ('','','','') should have the DWE values in the A table. I see

    that
    > a
    > > > join would work so if that is a possiblity I would be willing to use

    > that
    > > as
    > > > well. The only problem with the join that I have created is that it

    > pulls
    > > > all of table B over the network and process the request locally which

    > ends
    > > up
    > > > taking about 20 to 30 minutes. If don right it would take 5 to 10

    > > seconds.
    > > >
    > > > Thanks,
    > > >
    > > > Cam

    > >
    > >

    >
    >
     

Share This Page