Linked Table Problem

[Rob Parker]

I have an application which includes tables linked to an Excel spreadsheet.
If the spreadsheet is closed, these tables (and queries and forms based on
them) work fine; however, if the spreadsheet happens to be open, the
queries/forms either take several minutes to return the data (less than 100
records) or hang indefinitely.

I haven't used links to Excel sheets previously, but I need to in this
situation. I have two questions:
1. Is this standard behaviour with tables linked to Excel sheets (it's
occurring with both XP and 2003 versions of Access and Excel)? BTW, the
linked tables are not up-dateable - is this also standard behaviour?
2. Is there any way in which I can check whether the Excel file is open
before opening a form bound to a query which uses the linked tables? This
would allow me to trap the "hang" before it occurs, and present a message to
the user.

The linked tables were created via File - Get External Data - Linked Tables
....

Rob

 

[Guest]

Hi Rob,

1. Is this standard behaviour with tables linked to Excel sheets (it's
occurring with both XP and 2003 versions of Access and Excel)?

No. I just tried to duplicate what you described, using Access 2003 linked
to a defined range in Excel. It works just fine for me. I can have the
spreadsheet open to the worksheet that includes the range, and then
doubleclick on my linked table, and it pops right open without any delay. The
range for my test included a single column with 109 rows (ie. a listing of
ANSI reserved words shown in http://support.microsoft.com/?id=287417). This
suggests the possibility that you may have something wrong in your Excel ISAM
(msexcl40.dll) file. You aren't using any special characters or reserved
words by any chance, are you?

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Try re-registering this file, using the instructions shown here:
http://support.microsoft.com/kb/209805/

You might also try, as an experiment, renaming this file and then initiating
a repair of Office from the Help menu.

Do you have the latest service pack installed for the JET database engine?
http://support.microsoft.com/kb/239114/

BTW, the linked tables are not up-dateable - is this also standard behaviour?

Yes. Once you install the latest service packs for Office XP or Office 2003,
this does indeed become standard behavior. Microsoft lost a lawsuit which
forced this change.

I don't know the answer to your second question. Certainly it is possible to
detect whether Excel is open, but to a particular file, I don't know how at
the moment. However, I'm not experiencing the underlying problem that you are
that would cause me to go looking for this as a solution.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Rob Parker]

Hi Tom,

Thanks for the reply. My responses in-line in text below. Further comments
or suggestions will be gratefully received.

Rob

Hi Rob,


No. I just tried to duplicate what you described, using Access 2003 linked
to a defined range in Excel. It works just fine for me. I can have the
spreadsheet open to the worksheet that includes the range, and then
doubleclick on my linked table, and it pops right open without any delay.

I just tried that, and my linked tables open fine if the spreadsheet is
open. However, as I said in my original post, it's actually queries/forms
using the linked tables which are causing the problem - although I must
admit that I didn't realise that I didn't have a problem with the linked
tables per se when I first posted.

There are four select queries which pull the data fields I want from the
linked tables (they each have the same structure), and add a field
(DocGroup) to describe which sheet the data is from. Each of these has SQL
as in the following example:

SELECT "Plans" AS DocGroup, xlsPlans.ID, xlsPlans.Title, xlsPlans.Author,
xlsPlans.[Next Review Date]
FROM xlsPlans
WHERE (((xlsPlans.ID) Is Not Null));

These queries also work fine when the spreadsheet is open.

I combine the four select queries with a Union query, which is then used in
another query as the recordsource for a form. It's this Union query which
is hanging. It is as follows:

SELECT * FROM qryDocPlans
UNION
SELECT * FROM qryDocProcedures
UNION
SELECT * FROM qryDocChecklists
UNION
SELECT * FROM qryDocWorkInstructions;

Nothing exactly like rocket science in any of this, but that seems to be
where my problem lies. Any thoughts on this?

The range for my test included a single column with 109 rows (ie. a listing of
ANSI reserved words shown in http://support.microsoft.com/?id=287417).
This
suggests the possibility that you may have something wrong in your Excel
ISAM
(msexcl40.dll) file. You aren't using any special characters or reserved
words by any chance, are you?

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

No special characters or reserved words. Even in the fields I'm not using
in my select queries.

Try re-registering this file, using the instructions shown here:
http://support.microsoft.com/kb/209805/

You might also try, as an experiment, renaming this file and then
initiating
a repair of Office from the Help menu.

In light of my more recent observations (detailed above), I'll hold off
doing this for a while. It also seems unlikely as the cause, since I'm
getting the same behaviour on two separate systems.

Do you have the latest service pack installed for the JET database engine?
http://support.microsoft.com/kb/239114/

Yes (well, at least on my home system where I'm checking this at present).
I expect that my work system is also fully updated, but can't check, since I
won't be there again until late next week.

Yes. Once you install the latest service packs for Office XP or Office
2003,
this does indeed become standard behavior. Microsoft lost a lawsuit which
forced this change.

Thanks for that info - I'll file that away for later reference - or
forgetting ;-)

I don't know the answer to your second question. Certainly it is possible
to
detect whether Excel is open, but to a particular file, I don't know how
at
the moment. However, I'm not experiencing the underlying problem that you
are
that would cause me to go looking for this as a solution.

Yes, I can detect whether Excel is open, but (as you say) not to a
particular file. However, it seems that the problem is elsewhere - in
particular, with my union query - so I won't pursue this avenue at present.

 

[Guest]

Hi Rob,

Try rewriting your Union query to select the fields by name, instead of
using the SELECT * syntax. For a Union query to work correctly, the field
names and data types must be the same, and the same number of fields must be
included. You can alias the fieldnames if they are not the same. I suspect
that JET is somehow getting confused with the SELECT * syntax. In other
words, I am suggesting that you substitute the actual SQL statements for each
SELECT statement that you are currently showing. For example:

SELECT This, That, TheOther FROM TableName
UNION
SELECT This, That, TheOther FROM AnotherTableName
UNION
SELECT Field1 AS This, Field2 AS That, Field3 AS TheOther FROM Table3
ORDER BY THAT, THIS, TheOther;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,

Thanks for the reply. My responses in-line in text below. Further comments
or suggestions will be gratefully received.

Rob

Hi Rob,


No. I just tried to duplicate what you described, using Access 2003 linked
to a defined range in Excel. It works just fine for me. I can have the
spreadsheet open to the worksheet that includes the range, and then
doubleclick on my linked table, and it pops right open without any delay.

I just tried that, and my linked tables open fine if the spreadsheet is
open. However, as I said in my original post, it's actually queries/forms
using the linked tables which are causing the problem - although I must
admit that I didn't realise that I didn't have a problem with the linked
tables per se when I first posted.

There are four select queries which pull the data fields I want from the
linked tables (they each have the same structure), and add a field
(DocGroup) to describe which sheet the data is from. Each of these has SQL
as in the following example:

SELECT "Plans" AS DocGroup, xlsPlans.ID, xlsPlans.Title, xlsPlans.Author,
xlsPlans.[Next Review Date]
FROM xlsPlans
WHERE (((xlsPlans.ID) Is Not Null));

These queries also work fine when the spreadsheet is open.

I combine the four select queries with a Union query, which is then used in
another query as the recordsource for a form. It's this Union query which
is hanging. It is as follows:

SELECT * FROM qryDocPlans
UNION
SELECT * FROM qryDocProcedures
UNION
SELECT * FROM qryDocChecklists
UNION
SELECT * FROM qryDocWorkInstructions;

Nothing exactly like rocket science in any of this, but that seems to be
where my problem lies. Any thoughts on this?

The range for my test included a single column with 109 rows (ie. a listing of
ANSI reserved words shown in http://support.microsoft.com/?id=287417).
This
suggests the possibility that you may have something wrong in your Excel
ISAM
(msexcl40.dll) file. You aren't using any special characters or reserved
words by any chance, are you?

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

No special characters or reserved words. Even in the fields I'm not using
in my select queries.

Try re-registering this file, using the instructions shown here:
http://support.microsoft.com/kb/209805/

You might also try, as an experiment, renaming this file and then
initiating
a repair of Office from the Help menu.

In light of my more recent observations (detailed above), I'll hold off
doing this for a while. It also seems unlikely as the cause, since I'm
getting the same behaviour on two separate systems.

Do you have the latest service pack installed for the JET database engine?
http://support.microsoft.com/kb/239114/

Yes (well, at least on my home system where I'm checking this at present).
I expect that my work system is also fully updated, but can't check, since I
won't be there again until late next week.

Yes. Once you install the latest service packs for Office XP or Office
2003,
this does indeed become standard behavior. Microsoft lost a lawsuit which
forced this change.

Thanks for that info - I'll file that away for later reference - or
forgetting ;-)

I don't know the answer to your second question. Certainly it is possible
to
detect whether Excel is open, but to a particular file, I don't know how
at
the moment. However, I'm not experiencing the underlying problem that you
are
that would cause me to go looking for this as a solution.

Yes, I can detect whether Excel is open, but (as you say) not to a
particular file. However, it seems that the problem is elsewhere - in
particular, with my union query - so I won't pursue this avenue at present.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Rob Parker]

Hi Tom,

Tried that; unfortunately, it didn't fix the problem.

However, I'm pleased to report that the problem IS fixed. And it seems to
not have been an Access issue at all! I noticed that the spreadsheet file
(containing 8 sheets, with at most about 40 rows, and mostly about 8
columns) was considerably larger than I would have expected (over 2100kb).
So I copied the data from the four sheets I'm interested in into new sheets
in a new workbook (and applied similar formatting to the existing sheets),
and saved that as a new file - only 31kb. Relinked to this file, and
everything now works OK.

Thanks for the suggestions, which prompted me to look for something other
than what I expected the problem to be initially.

FWIW, I've seen this unexplained "bloat" of an Excel file once before. I
haven't explored it though - maybe when I get bored and have some free time,
that could be a little project for me.

Rob

Hi Rob,

Try rewriting your Union query to select the fields by name, instead of
using the SELECT * syntax. For a Union query to work correctly, the field
names and data types must be the same, and the same number of fields must
be
included. You can alias the fieldnames if they are not the same. I suspect
that JET is somehow getting confused with the SELECT * syntax. In other
words, I am suggesting that you substitute the actual SQL statements for
each
SELECT statement that you are currently showing. For example:

SELECT This, That, TheOther FROM TableName
UNION
SELECT This, That, TheOther FROM AnotherTableName
UNION
SELECT Field1 AS This, Field2 AS That, Field3 AS TheOther FROM Table3
ORDER BY THAT, THIS, TheOther;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,

Thanks for the reply. My responses in-line in text below. Further
comments
or suggestions will be gratefully received.

Rob

Hi Rob,

1. Is this standard behaviour with tables linked to Excel sheets
(it's
occurring with both XP and 2003 versions of Access and Excel)?

No. I just tried to duplicate what you described, using Access 2003
linked
to a defined range in Excel. It works just fine for me. I can have the
spreadsheet open to the worksheet that includes the range, and then
doubleclick on my linked table, and it pops right open without any
delay.

I just tried that, and my linked tables open fine if the spreadsheet is
open. However, as I said in my original post, it's actually
queries/forms
using the linked tables which are causing the problem - although I must
admit that I didn't realise that I didn't have a problem with the linked
tables per se when I first posted.

There are four select queries which pull the data fields I want from the
linked tables (they each have the same structure), and add a field
(DocGroup) to describe which sheet the data is from. Each of these has
SQL
as in the following example:

SELECT "Plans" AS DocGroup, xlsPlans.ID, xlsPlans.Title, xlsPlans.Author,
xlsPlans.[Next Review Date]
FROM xlsPlans
WHERE (((xlsPlans.ID) Is Not Null));

These queries also work fine when the spreadsheet is open.

I combine the four select queries with a Union query, which is then used
in
another query as the recordsource for a form. It's this Union query
which
is hanging. It is as follows:

SELECT * FROM qryDocPlans
UNION
SELECT * FROM qryDocProcedures
UNION
SELECT * FROM qryDocChecklists
UNION
SELECT * FROM qryDocWorkInstructions;

Nothing exactly like rocket science in any of this, but that seems to be
where my problem lies. Any thoughts on this?

The range for my test included a single column with 109 rows (ie. a listing of
ANSI reserved words shown in http://support.microsoft.com/?id=287417).
This
suggests the possibility that you may have something wrong in your
Excel
ISAM
(msexcl40.dll) file. You aren't using any special characters or
reserved
words by any chance, are you?

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

No special characters or reserved words. Even in the fields I'm not
using
in my select queries.

Try re-registering this file, using the instructions shown here:
http://support.microsoft.com/kb/209805/

You might also try, as an experiment, renaming this file and then
initiating
a repair of Office from the Help menu.

In light of my more recent observations (detailed above), I'll hold off
doing this for a while. It also seems unlikely as the cause, since I'm
getting the same behaviour on two separate systems.

Do you have the latest service pack installed for the JET database
engine?
http://support.microsoft.com/kb/239114/

Yes (well, at least on my home system where I'm checking this at
present).
I expect that my work system is also fully updated, but can't check,
since I
won't be there again until late next week.

BTW, the linked tables are not up-dateable - is this also standard
behaviour?

Yes. Once you install the latest service packs for Office XP or Office
2003,
this does indeed become standard behavior. Microsoft lost a lawsuit
which
forced this change.

Thanks for that info - I'll file that away for later reference - or
forgetting ;-)

I don't know the answer to your second question. Certainly it is
possible
to
detect whether Excel is open, but to a particular file, I don't know
how
at
the moment. However, I'm not experiencing the underlying problem that
you
are
that would cause me to go looking for this as a solution.

Yes, I can detect whether Excel is open, but (as you say) not to a
particular file. However, it seems that the problem is elsewhere - in
particular, with my union query - so I won't pursue this avenue at
present.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have an application which includes tables linked to an Excel
spreadsheet.
If the spreadsheet is closed, these tables (and queries and forms
based
on
them) work fine; however, if the spreadsheet happens to be open, the
queries/forms either take several minutes to return the data (less
than
100
records) or hang indefinitely.

I haven't used links to Excel sheets previously, but I need to in this
situation. I have two questions:
1. Is this standard behaviour with tables linked to Excel sheets
(it's
occurring with both XP and 2003 versions of Access and Excel)? BTW,
the
linked tables are not up-dateable - is this also standard behaviour?
2. Is there any way in which I can check whether the Excel file is
open
before opening a form bound to a query which uses the linked tables?
This
would allow me to trap the "hang" before it occurs, and present a
message
to
the user.

The linked tables were created via File - Get External Data - Linked
Tables
....

Rob

 

[Guest]

Hi Rob,

Glad to hear that all is well now. If you find the answer to the Excel bloat
problem, do post back. I've seen it occasionally myself, but never spent the
time investigating it further.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________