Re: Link table alternative

From: B Nieds (bnieds_at_wieler_and_wieler.com)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 16:34:03 -0500

Hi:

Simple query really:

Select tbl1.phsmgr, (Select Sum(tbl1.phscst) as TCost from tbl1 Group by
tbl1.phscde;) as Total from Tbl1;

Now I know I could use grouping in the above example but I wanted to show
the simplicity which would cause the error. The above query will when run
return the error "database engine could not lock table 'tbl1' because it is
already in use by another person or process". If I run the above query
against an imported table then I get the error that only one record can be
returned but at least it runs.

Normally tbl1 has a join to tbl2 which is joined to tbl3 which combine to
provide the information that I want. I can circumvnent the problem by
importing the data rather than linking but as I mentioned earlier the data
is only as current as the import.

Thanks
Bill
"MGFoster" <me@privacy.com> wrote in message
news:4qdkc.17579$e4.572@newsread2.news.pas.earthlink.net...
> Post your query's SQL statement. Maybe something there we can analyze.
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
>
> B Nieds wrote:
>
> > Hi:
> >
> > I am using Access to report on data maintained outside of Access. I have
> > created links to the ourside files and have then created Queries, Forms,
and
> > Reports using these links. All of that is working reasonably ok.
> >
> > The problem I encounter is when I need to get more complex. Access will
then
> > complain that the linked table is in use (or opened exclusively) by
another
> > user. I tested with a copy of the files locally on my computer with the
same
> > results. Example is a subquery use the same table as the main query.
> >
> > Is this a limitation of linked tables? Is there a workaround or
alternative
> > method for getting the data? I know I can import the data but seems to
> > defeat the purpose of linked tables.
>

Relevant Pages

  • Re: Combine Tables
    ... Tbl1 is single field: ... Tbl2 has 2 fields: ... that determines where, the dash is. ... THEN use the query and tbl2 to create another query that joins ...
    (comp.databases.ms-access)
  • Re: Link table alternative
    ... If your query is an actual query & not an example: ... SELECT phsmgr, phscde, SUMAs Total ... The subquery in the SELECT clause will return a record for each phscde ... in tbl1, ...
    (microsoft.public.access.queries)
  • #error and #name?
    ... errors on my report. ... the report is linked to a query, and contains text fields to display each ... I have also tried ControlSource = sumbut in both cases I get ... control source = Select countfrom tbl1 ...
    (microsoft.public.access.reports)
  • Re: Find and Replace against set of rules in 2nd table
    ... is a table of instructions for updating each Record of TBL1? ... > be a query so that I can run it as needed. ...
    (comp.databases.ms-access)
  • Re: Deleting Unmatched records
    ... I have created an unmatching query, using the wizard, on tbl2 with ... comparison to tbl1 to filter out which records in tbl1 no longer ... SQL View of query: ... PRIMARY KEY (Key1) ...
    (microsoft.public.access.queries)