Linked excel spreadsheet with LOTS of errors

[Guest]

I have linked an excel spreadsheet to an access database by using the method File>Get External Data>Link tables in my access program. However, the new linked database has added extra records in the middle and beginning of the database, with some fields having extraneous numbers and some with #Num! I know that it's not possible to delete records from a linked table. However, I would like to at least delete the numbers and #Num! from those extra records. When I attempt to do that, I get this error message:

Field cannot be updated. (Error 3164)
Possible causes:

You tried to update a field in a record or table that is currently locked by another user. Wait for the other user to finish working with the record or table, and then try the operation again.

In Microsoft® Access, you tried to change the value of a control whose Locked property is set to Yes.


Is there any way around this? The database is a visual mess and I have worked and re-worked my original excel spreadsheet to make it as simple as possible for the conversion process. There are no added graphics or formatting and it is set up as a simple list. Why all the extra rows and numbers in the new linked table?

I appreciate any helpful suggestions. Thanks.

 

[John Nurick]

Hi JD,

Is it possible that there are hidden rows in the Excel sheet?

 

[John Nurick]

Could it be that I'm selecting the whole spreadsheet to link
to and not just the range of the tables?

Yes. If you do that, Access tries to link to the whole sheet. Ifthe
sheet contains anything beyond one table starting in cell A1, you need
to specify the range occupied by the table. Usually the neatest way to
do that is to define a named range (Insert|Name|Define) in Excel and use
the name when linking the table.

Also, each table in Excel must be in a contiguous range of cells. Field
names (if they appear) must be in the top row of that range.

 

[Jamie Collins]

If you do that, Access tries to link to the whole sheet.

Note that linked tables are a feature of Jet.

If the sheet contains anything beyond one table starting
in cell A1, you need to specify the range occupied by
the table.

The (one) table could start in any cell in column A (e.g. A65000) and
the Jet view of the Excel table would be the same as if it started in
cell A1. It could also start in another column and the only ill effect
would be blank columns to the left.

Jamie.

--

 

[John Nurick]

Note that linked tables are a feature of Jet.

When talking about doing things with Access wizards and Access VBA
commands I say "Access".

The (one) table could start in any cell in column A (e.g. A65000) and
the Jet view of the Excel table would be the same as if it started in
cell A1. It could also start in another column and the only ill effect
would be blank columns to the left.

As far as I know this is only true if the UsedRange of the worksheet
coincides with the range occupied by the table. This is not reliably the
case in typical users' worksheets, so I feel that "A1 or range" is
practical advice.

 

[Jamie Collins]

As far as I know this is only true if the UsedRange of the worksheet
coincides with the range occupied by the table. This is not reliably the
case in typical users' worksheets, so I feel that "A1 or range" is
practical advice.

That presents a bit of a dilemma: should I seek to correct information
that is technically incorrect if it was given in the spirit of being
practical advice? It would be difficult for me to judge what you think
is practical, so can I respectfully request that in such situations
you qualify with, 'Practically speaking, ...'?

Thanks,
Jamie.

--

 

[John Nurick]

That presents a bit of a dilemma: should I seek to correct information
that is technically incorrect if it was given in the spirit of being
practical advice? It would be difficult for me to judge what you think
is practical, so can I respectfully request that in such situations
you qualify with, 'Practically speaking, ...'?

Sounds reasonable - provided you start using a converse qualification,
e.g. "Provided everything else is just right..."!

 

[Jamie Collins]

John Nurick wrote ...

Sounds reasonable - provided you start using a converse qualification,
e.g. "Provided everything else is just right..."!

No, that's unreasonable. Such a qualification is implicit, isn't it?
One has to make assumptions e.g. the OP has given all the relevant
details, they have a fairly standard install and machine set up,
there's power to the unit etc.

More appropriate would be, 'Technically speaking, ...'

Jamie.

--