Keith Williamson
Kwilliamson at RTKL.com
Fri Feb 9 08:16:24 CST 2007
Hmmm. Well...I have used a couple pass-through-queries. But not within this particular problem. I'll have to look at that. Thanks for the input. Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Thursday, February 08, 2007 8:11 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Linked Table Limits? Hi Keith: Unfortunately that situation limits your ability to resolves what sounds like internal system design limitations. This may be what is causing errors but of course you are limited in the ability to dig into some problems. One main problem may be the use of pass-through-queries. The use of these functions is probably the single greatest cause of slow data access and as well as adding another unstable layer to your application. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson Sent: Thursday, February 08, 2007 11:37 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Linked Table Limits? No. I can't do anything with the base application. My job is to create processes to extract the data (either through the canned reports, or by creating Access queries/forms/reports). I am using Access (instead of just Enterprise Manager, for example) because I am also storing some data, to make my reporting processes better. Thus, I link to the backend tables in the application AND use my own tables in Access, to create meaningful financial reports. Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Thursday, February 08, 2007 1:40 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Linked Table Limits? Hi Keith: They do. All databases back before the 90's use to run with text based keys. Is it your job to just run the application or is it your job to run it and repair it? Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson Sent: Thursday, February 08, 2007 9:41 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Linked Table Limits? I thought that both Access and Sql do searches much faster on numbers, rather than text. ?? This application is pretty slow. I assumed it was from all the text keys. ?? Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos Sent: Thursday, February 08, 2007 12:19 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Linked Table Limits? Well, I was taught that numbers that you don't expect to do mathematics on SHOULD be stored as text..... perhaps those guys went to my school? ;-) GK On 2/8/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > Sounds similar. This sql database is a major software > (Architectural/Service Industry....Labor-Based) called Vision. > Unfortunately, the designers didn't really seem to have a lot of > "vision" when designing it. All the Key fields seem to be text > fields......that's right...I said TEXT fields. Employee Number, Project > Number, etc. ......ALL TEXT. WHAT WERE THEY THINKING???? > > <:( > > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com > > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland > 21231-3305 > > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos > Sent: Thursday, February 08, 2007 11:18 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Linked Table Limits? > > We have an Oracle table in our data warehouse that if we just link to > and use the results of that link it works fine but if I put a criteria > on anything in that table it blows up and causes an Oracle Error > message. It's the Customer Location table and someone wanted all the > sales to California. I tried to select by the ship to state and it > blew up. I tried it again and it blew up again. And Again. I thought > it was something I was doing wrong. Then I did it pulling all records > into a temporary table including the state and then selecting only the > california sales from there and it worked fine. Go figure. > > GK > > On 2/8/07, Keith Williamson <Kwilliamson at rtkl.com> wrote: > > I suspect there must be a corrupted record in that particular table. > I > > know I have run queries before with just as many, if not more, joined > > tables (that are linked to sql database.) I was hoping someone had > some > > quick info about such a situation. But alas, I have to dump the issue > > on our MIS laps, to investigate the offending table. > > > > Thanks, > > > > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com > > > > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland > > 21231-3305 > > > > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby > > Sent: Thursday, February 08, 2007 10:29 AM > > To: 'Access Developers discussion and problem solving' > > Subject: Re: [AccessD] Linked Table Limits? > > > > Hmmm... I think something else is going on. AFAIK you can have every > > table > > in your database be out in SQL Server and thus you could have dozens > of > > SQL > > Server tables in a query. I have to admit that I do not have many > > "mixed" > > databases, and the ones I do have tend to use just one or two views > from > > SQL > > Server. > > > > If you have OTHER SQL Server tables, I would try to build queries > using > > those tables to see how they function. > > > > > > John W. Colby > > Colby Consulting > > www.ColbyConsulting.com > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith > > Williamson > > Sent: Thursday, February 08, 2007 10:16 AM > > To: Access Developers discussion and problem solving > > Subject: [AccessD] Linked Table Limits? > > > > Hi all, > > > > > > > > Does anyone know if there are limits to the number of linked tables > (to > > a > > SQL database) that can be included in an Access query? I have a query > > that > > joins (2) linked tables and (2) local Access tables. That runs very > > fast. > > However, if I try to add one more linked table to the query.....it > > simply > > crashes the database. I've even tried using a sql select statement in > > the > > query, to get the data from that last linked table......crashes the > > database. IF I copy the entire linked table to a local table, and > then > > select/join to that table......the query runs fast. When I try > > exporting > > the query, with the select statement to the linked table.....my > > resultant > > export (incomplete due to the crash) is corrupted. > > > > > > > > So, my question is....is there some sort of limit to the number of sql > > tables that I can join, in a query? There doesn't seem to be anything > > wrong > > with the sql table I am trying to join. Just very odd. > > > > > > > > Thanks, > > > > > > > > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com > > > > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland > > 21231-3305 > > > > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com > > <http://www.rtkl.com/> > > > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > -- > Gary Kjos > garykjos at gmail.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com