RE: [AccessDevelopers] Datawarehouse and linked local tables

Jim,

I wouldn't expect a pass-through to include the "dbo_". Are you sure this is a pass-through and not a standard Access query?

Also you shouldn't use "LIKE" if you aren't using a wildcard. 

I would expect the p-t SQL to look like:

 SELECT TRANSACTION_DETAIL_ALL.* 
 FROM TRANSACTION_DETAIL_ALL 
 where [FISCAL_YEAR] = 2013 

If you have any permission/rights on the SQL table, you might want to create an index on the FISCAL_YEAR field.

Duane Hookom MVP
MS Access

________________________________
> From: luvmymelody@yahoo.com
>
> SELECT dbo_TRANSACTION_DETAIL_ALL.*
> FROM dbo_TRANSACTION_DETAIL_ALL
> where [FISCAL_YEAR] LIKE 2013
>
> Inedexed fields in the ACCOUNTS table
> ACCOUNT
> TITLE
>
> Jim Wagner
> ________________________________
>
> ________________________________
> From: Duane Hookom <duanehookom@hotmail.com>
>
> Can you share the SQL of the pass-through as well as provide which
> fields are indexed?
>
> Duane Hookom MVP
> MS Access
>
> ________________________________
>> To:
>> From: luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com> 
>>
>>
>> Duane
>>
>> I indexed and created the pass through query but the pass through times
>> out on me
>>
>> Jim Wagner
>> ________________________________
>>
>> ________________________________
>> From: Duane Hookom
>>
>> Jim,
>> This makes sense to me. If you are querying a table or data only in the
>> warehouse, Access will optimize the request for data. If there is a
>> join being performed on the Access side with a local table, it is much
>> more complex since each record in the local table has to be matched
>> with records in the warehouse.
>>
>> In addition to indexing, I would consider using a pass-through query to
>> limit the records from the warehouse to a subset.
>>
>> Duane Hookom MVP
>> MS Access
>>
>>
>> ________________________________
>>> From:
> hal_mcgee@beaerospace.com<mailto:hal_mcgee@beaerospace.com><mailto:hal_mcgee@beaerospace.com<mailto:hal_mcgee@beaerospace.com>>
>>>
>>> Be sure and index the local table. I've had this issue before and
>>> indexing the local table helps a lot.
>>>
>>> Good luck
>>>
>>>
>>
> [mailto:AccessDevelopers@yahoogroups.com<mailto:AccessDevelopers@yahoogroups.com><mailto:AccessDevelopers@yahoogroups.com<mailto:AccessDevelopers@yahoogroups.com>>]
>> On Behalf Of
> luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com><mailto:luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com>>
>>>
>>> Hello all,
>>>
>>> We have a data warehouse table in a query with a local table named
>>> ACCOUNT joined together on the ACCOUNT field to act as a filter for the
>>> query.
>>>
>>> The data warehouse table is huge and the query is now not working. We
>>> took the local ACCOUNT table out of the query grid and added to the
>>> criteria line LIke PL* and Like JC2* and the query runs in less than 10
>>> seconds.
>>>
>>> This makes no sense because with the * we are asking for more accounts
>>> than what is in the table and it runs faster. The query has run for
>>> years so the ACCOUNT table has worked for a long time. Is there a
>>> limitation to a linked local table or something?
>>>
>>> Thank You
>>>
>>> Jim Wagner
>>

------------------------------------


Please zip all files prior to uploading to Files section.Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/AccessDevelopers/join
(Yahoo! ID required)

<*> To change settings via email:
AccessDevelopers-digest@yahoogroups.com
AccessDevelopers-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
AccessDevelopers-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/