Updating Access Linked Table from Excel

[rjmccafferty1]

This is probably a naive question, but none of my books answer the question.

If I link an Access table (2007) to an Excel workbook, there are two things I do not know for sure.

1. If changes are made to the Excel worksheet, does the linked Access table update immediately? (I have opened many things over the year that ask if I want to update the links, which makes me think maybe it is not automatic or immediate).

2. Excel has this "wonderful" tendency to believe that empty rows are meaningful. Example: If we put data in the first 100 rows of Excel and hit print, it will print 100 rows. If we then delete all the data from rows 51 - 100, leaving nothing in any rows except 1 through 50, and hit print again, we will usually still get 100 rows printed, the last 50 generating blank pages of printing. So my question is, in this same scenario, if I link a table to Access (with the linked table in Access, the original in Excel) will my Access table have 50 rows in it or 100?

 

[ccv]

1: Yes, but if the spreadsheet has been linked to another workbook then no. You must open the workbook and update it (either manually or through vba).

2: If you simply clear the contents of the linked spreadsheet the rows will be empty. You need to delete the rows.

 

[SkipVought]

Excel has this "wonderful" tendency to believe that empty rows are meaningful.

Deleting data in a row is different than deleting rows. You can essentially duplicate this behavior in Access if you have no key and delete each value in a row, one at a time

BTW, since your frame of reference is Excel, you probably ought to be posting in form68 or forum707.




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]