Link table alternative

  • Thread starter Thread starter B Nieds
  • Start date Start date
B

B Nieds

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.

Thanks
Bill
 
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
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've found that it is better to import large text files rather than to
link to them. When linked Text Files, wierd errors appear that can't be
solved, except by importing the data to a temp table.

If your query is an actual query & not an example: the query can be
simplified as:

SELECT phsmgr, phscde, SUM(phscst) As Total
FROM tbl1
GROUP BY phsmgr, phscde

The subquery in the SELECT clause will return a record for each phscde
in tbl1, 'cuz you GROUPed BY phscde. That was the cause of the error
"only one record can be returned," which, confusingly, means that the
subquery should return ONLY one record, but is, in fact, returning more
than one record.

I included phscde in the SELECT clause, because to GROUP BY a column &
SUM() on each instance of that columns value w/o showing the column
value can be confusing. E.g.:

Include column value:

phscde Total
- --------- -----
1 25
2 30
3 1
4 5

When phscde is not shown the result is:

Total
- -----
25
30
1
5

Which can be meaningless when the associated column value (phscde) is
missing.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJF5joechKqOuFEgEQJdvgCaAkp/FG9M10MwXe82BR888/C4QHQAoKuC
tOWjfbgYaDt+UXHXSPERgNm5
=EFUe
-----END PGP SIGNATURE-----
 
Hi:

Yes you are correct on both counts. If I import the data into an Access
table then the problem disapears. That cause the problem on needing to first
import 4 - 12 tables each time the user wants to run a report or query. That
is why I was looking for some alternative or some idea on how to fix the
problem.

I had a chance to go back and look at the error and I did see why Access
said only one record was being returned. I like your explanation better
though since it does provide a better understanding.

Here is another example: I have two simple queries which summarise data for
use in a report. The report details the information in the main body of the
report then in the report footer the two queries are used as subreports (one
per query) to summarise the data.

Query 1 SELECT Phsdep, SUM(Phssls), SUM(phscst), SUM(Phstax) FROM tbl1
GROUP BY Phsdep;
This query gives summary totals by department.

Query 2 SELECT Phsmgr, SUM(Phssls), COUNT(phscde), AVG(Phssls) FROM tbl1
GROUP BY Phsmgr;
This query provides statistical information by manager.

Individually each query runs fine but when I add the two queries to the
report the second summary will fail because the error "database engine could
not loack tbl1..." occurs. It does not matter which is first and if I
convert one of the queries into a make table then the report runs fine.

If it would work then the link table would solve a lot of problems. Any more
ideas?

Thanks
Bill
 
Back
Top