Updating of linked table in Access

rmccafferty · August 26th, 2009, 08:14 PM

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?

SerranoG · August 28th, 2009, 10:14 AM

Quote:

1. If changes are made to the Excel worksheet, does the linked Access table update immediately?

Linked Excel spreadsheets started acting funny with the later "fix" of the XP version. I have 2003 at work and 2007 at home. So far, the 2003 still misbehaves with linked Excel sheets to I have to import them. I hope that they fixed it in 2007 (I've not tried) but before the XP "fix," they used to update immediately for me.

Quote:

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?

When I highlight the entire rows by clicking on the row numbers, say, 51 to 100 and choose EDIT > DELETE from the menu, the print area automatically shrinks for me. If I highlight the rows and just press the DELETE key on my keyboard, the printer area does NOT automatically reset. Either way, when I linked in Access, Access showed the correct number of rows.

gbianchi · August 28th, 2009, 12:07 PM

To add what greg answer about your second question, think that MS is believing this:

When you select several rows, and press delete, it only delete the content, but is leaving any format you put them on. So if all your rows has a red background it will keep it.
When you delete rows (using delete) it will shrink the data, moving the cells up and deleting the format also. So delete has to meaning according to MS, just depending which delete you use.