Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    34

    Unanswered: Comparing local tables to server tables via passthrough?

    I have an automated process that I've been using that's driven off ID numbers in the system. What I currently do is copy down the database tables (all 1 gig+ of them) and then query them locally through a series of presetup queries/macros/VBA code. As you can imagine, this takes quite a bit of time to do....but it still significantly faster than querying off the server tables with a regular query. I know PassThrough queries bypass Jet and go directly to the server (in this case Oracle), but, you can't seem to query local tables in your Access database against the Oracle tables in the passthrough query builder. I was wondering if anyone knew of a way with VBA to basically do an innner join between the local table vs the Oracle table so that I can get the data instantaneously and not have to copy down all these tables each time?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi wysiwig6000

    Have a thing about what you are asking. At some point, somehow one of those tables has got to cross the network. So no - given your current set up I don't think you can improve things much.

    If you could permanently move your local table to Orcale then you would be flying. Failing that - if you could either upload the table to Oracle or use a passthrough that was a heteregeneous query (a query that uses a remote data source) then that might speed things up but I don't know the PL\SQL syntax (only the T-SQL). The Orcale forum might be able to help more.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2005
    Posts
    34

    I sorta figured out a way

    I sorta figured out a way, it's a little cheap and not as blazing fast as a regular passthrough..but hey, it beats using Access' linked table queries .

    -Create a passthrough query with the fields, and table that you are interested in querying against your local table
    -Go to Properties and use the elipses to generate the connection string, with password
    -Save the query
    -Create a new query in design view, select the local table that you want to use and then select the passthrough query you just created
    -Join the two together by whichever field you want to join them on
    -Select the fields that should be returned, enter in any criteria, etc... and run the query
    -You may also want to set the ODBC Timeout to 0 just in case

    It's pretty effective, but not exactly instantaneous like a normal passthrough query is. My guess is that because we're doing the query within a query method, it's somehow using Jet (which is sloooowwwing down everything). This method takes me about as much time as the old method of copying down the tables, but I don't have gigs and gigs of data on my machine anymore. Let me know if anyone knows of a way to somehow optimize this query to be as fast as possible.

  4. #4
    Join Date
    Nov 2012
    Posts
    2
    I had a situation where I needed to filter records on SQL Server based on data stored in a local Access table, but I did not want to use a combination of Jet and passthru.

    So I transposed the data stored in Access from rows to a comma delimited string and used this as a filter in the SQL's IN clause in the passthru query

    Here's an example

    Local Access Table [ltblFunds]
    Account_Number
    FAPF15010002
    FAPF15020002
    FAPF15030002
    FAPF15040002

    SQL SERVER Query
    SELECT Column1, Column2 FROM Table1
    WHERE Account_Number IN ('FAPF15010002', 'FAPF15020002', 'FAPF15030002', 'FAPF15040002')

Posting Permissions

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