Linked Excel table has 2,000 blank rows

[dutty2001]

I have a table in Excel that I'm trying to link to Access. When I do
this not this, not only does the small amount of information from my
excel sheet carry over, but so does over 2,000 blank rows. This is
going to get in the way of sorting. I would just delete them, but
since it is linked it won't let me, for some reason.

My goal is to have a form that I can use to enter data that will show
up in my Access table, and in turn show up in the excel sheet as well.
I tried one suggestion to make a Query to filter out all those blank
lines and that worked great. But when I made a form from the Query
instead of my original table, the new records I type in don't show up
in the original table, and thus won't go into my excel sheet. This is
very frustrating and if someone has any ideas it would be great.

In addition, I'm disappointed to find out I can set a key field in
access if my table is linked. Is there a way around this? Or so I
just have to do without this option. Keep in mind it must stay linked,
not imported or exported.

Thanks so much!

Daniel

 

[Nikos Yannacopoulos]

Daniel,

This sort of behaviour tends to occur with linked Excel worksheets when the
cell content of rows has been cleared in them, but not the rows themselves.
Open the worksheet in Excel, select all rows under your data down to the
very end and delete rows form the right click shortcut menu (not just clear
contents or delete button). Save. This will get rid of the blank records in
the linked table in Access.

A linked table (regardless of its origin) is a read-only object for Access
as far as its design goes, so no, you cannot set a key.

HTH,
Nikos