Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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

    > >
    > >

    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page