[AccessD] Linked Table Limits?

Jim Lawrence accessd at shaw.ca
Thu Feb 8 19:10:42 CST 2007


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




More information about the AccessD mailing list