Tina Norris Fields
tinanfields at torchlake.com
Tue Feb 26 15:34:11 CST 2013
Okay, I'm probably missing something here. Can you write new queries? If you can, couldn't you make a select query qselA that pulls all the fields of that table, then use qselA as the source for qselB that uses the pair of fields as a compound PK? Sorry if I'm being dense. T Tina Norris Fields tinanfields at torchlake.com 231-322-2787 On 2/24/2013 11:30 AM, Brad Marks wrote: > Jim, > > Thanks for your assistance. > > >>> While you cannot change anything existing, can you add a view to the SQL >>> database? > We are not able to add views to the SQL-Server database. We cannot make any updates to anything, except to update data via the purchased package front-end screens. > > > >>> Not sure I understand this part. When you link the table, do you see the >>> index with both fields and is that set as the PK field or no? > It appears that the table has a compound key (Part-ID and Routing-Sequence-Number). > The Access-based Inquiry system that we have built has the Part-ID, but not the Routing Sequence-Number available. When I tried to join this table in one of the existing queries, the data was returned, but it took a long time. I have tried several variations of the query and ran quite a few tests. Due to the slowness, I have started to consider alternative approaches. I don't have a lot of experience with obtaining data from a table with only a partial key. I would guess that a sweep of the entire table is being performed, therefore the slowness. > > Brad > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com on behalf of Jim Dettman > Sent: Sun 2/24/2013 10:03 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Inquiry System Built With Access 2007 ?Performance Concern - Partial Key > > Brad, > > <<We are not able to change any of the tables, keys, relationships, indexes, > etc. in any of the tables in the purchased system. We can only read the > data. >> > > While you cannot change anything existing, can you add a view to the SQL > database? > > <<Currently the Inquiry System does not have the second part of the key > available. Only the Part_ID is available, not the Routing_Sequence_Number. > (Not all Parts have routings, all we need to discern is whether a part has a > routing or not) >> > > Not sure I understand this part. When you link the table, do you see the > index with both fields and is that set as the PK field or no? > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks > Sent: Saturday, February 23, 2013 06:20 PM > To: Access Developers discussion and problem solving > Subject: [AccessD] Inquiry System Built With Access 2007 ? Performance > Concern - Partial Key > > All, > > We have an Inquiry System that obtains data from a purchased package's SQL > Server database via ODBC. > > The Inquiry System works nicely and response time is quite fast for all > screens. > > We are not able to change any of the tables, keys, relationships, indexes, > etc. in any of the tables in the purchased system. We can only read the > data. > > Recently, a request has been made where we will need to pull data that will > come from a table that has not been used by the Inquiry System before. > > Here is the catch. The new table that we now want to pull data from has a > two-part key (Part_ID, and Routing_Sequence_Number). > > Currently the Inquiry System does not have the second part of the key > available. Only the Part_ID is available, not the Routing_Sequence_Number. > (Not all Parts have routings, all we need to discern is whether a part has a > routing or not) > > When we try to obtain data from this table with only the Part_ID, the data > is returned, but response time is slow. This table has about 2,000 rows. > > I am considering two approaches and decided to see if anyone else has run > into something similar and has advice. > > Approach-1. Build an Access table from the SQL Server table, that has only > Part_ID for the key. > > Approach-2. Use the SQL Server data to build an array when the inquiry > system is first initiated then obtain the data from the array. > > I would guess that there may be other approaches that would work better. > > I appreciate your assistance. > > Thanks, > Brad > > >