Arthur Fuller
fuller.artful at gmail.com
Wed Feb 27 12:18:27 CST 2013
Tina, You and I seem both to be missing something here. I'm relieved to know that I am not alone in this. Unless Brad has mis-stated the problem, I don't get it. The home-built Inquiry System ought to be able to do just about anything with the linked-source-data; and if massages are required on said source-data, that's what ETL is for. (Install a free copy of SQL Server Express, design some ETL, and you're done.) Brad, I fail to see the problem here. Please expound. Thanks, On Tue, Feb 26, 2013 at 4:34 PM, Tina Norris Fields < tinanfields at torchlake.com> wrote: > 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@**databaseadvisors.com<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@**databaseadvisors.com<accessd-bounces at databaseadvisors.com> >> [mailto:accessd-bounces@**databaseadvisors.com<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 >> >> >> >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> > Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com> > -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Niels Bohr