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?
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.
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.