[AccessD] Linked Table Limits?

Keith Williamson Kwilliamson at RTKL.com
Thu Feb 8 13:33:52 CST 2007


Well....our labor detail table, alone, is 2.5 million records.

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:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Linked Table Limits?

Well since a number is normally compressed into fewer bytes than a
text field with the same value there would be some economy there with
a fewer number of bytes needing to be compared or indexed but if
everything is indexed the same I don't know if you would notice a huge
difference.  No doubt if you get into huge databases with millions of
records the compression that numerics bring to the party would make
things a lot faster.

GK

On 2/8/07, Keith Williamson <Kwilliamson at rtkl.com> wrote:
> 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
>


-- 
Gary Kjos
garykjos at gmail.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