900+ blank records appearing in Table! (1 Viewer)

marthacasting · Feb 13, 2018

In Access 2007, I have a table with just shy of 20,000 records.

In the last few days, suddenly I have been having over 900 blank records appear at the top! I delete them all, back-up, and then the next day the same thing happens again. I do import a small number (maybe 20 average) a few days a week.

Any ideas?!

Thank you!

plog · Feb 13, 2018

Sounds like you have a suspect. Test it to see if it truly is guilty.

marthacasting · Feb 13, 2018

Why would that happen?

CJ_London · Feb 13, 2018

Why would that happen?

depends on what you are doing - importing an excel file might import a number of blank rows if the range is wrong for example

marthacasting · Feb 13, 2018

Thank you. It does seem like importing would be the trigger. And I do use Excel; however I am using the same (Excel) template that I've been using for a couple of years (without incident), so I'm not sure what has changed. And does it seem disproportionate to receive well over 900 records for importing just a few rows from Excel?

With thanks.

CJ_London · Feb 14, 2018

if you are importing a worksheet it will import everything in that sheet from cell A1 to the bottom right cell that has been used by row and column (used at some point, may well be empty now)

Open your excel file and tap the end then home keys. My guess will be that you have for the sake of argument the first 20 rows filled, but end/home will take you to row 900.

The same applies for named ranges referenced for importing

Solution is to highlight and delete the blank rows and save the file.

Enterprise systems generating the old .xls files for users often (sloppily in my opinion) claim the entire worksheet (64k rows x whatever columns) even though only a few rows/columns have data. This makes the file huge.

Pat Hartman · Feb 14, 2018

People who use Excel don't seem to be aware that deleting the contents of a cell does NOT remove a row or column. You have to select the data you want to delete and use the DELETE key to actually remove rows or columns.

marthacasting · Feb 14, 2018

Thank you for your prompt and helpful responses! I will try these approaches! It's just confusing to me why, out of the blue, this would happen when I have not made any changes over the last couple of weeks (not to mention years) in what I am doing! I am going to check it out!

arnelgp · Feb 14, 2018

culprit blank rows.
open the workbook, go to worksheet.
goto A1 or whichever is your first column.
click Control+Down-Arrow, this will put you to the last row
if the last row is empty row, click on the row number and Ctrl +- (minus symbol) to delete
the entire row.
on the blank row again, click on the row number again then Shift-End-Down Arrow to
select from current row up to the last row of excel.
press Ctrl+- (ctrl+minus sign) to delete the entire range.

save and your worksheet is now clean.

isladogs · Feb 14, 2018

A quicker method of getting to the last cell ever used on a spreadsheet is to press Ctrl+End.
Particularly useful on a large spreadsheet

If you find this is an empty cell then you have cleared rows or columns but not deleted them

Ctrl+Shift+End will take you to the last populated cell

So for example if Ctrl+End takes you to T103 but CtrL+Shift+End takes you to G55, then you can safely delete rows 56-103 and columns H-T

HTH

arnelgp · Feb 14, 2018

ctrl+shift+end will not take you to the last used cell(last row+last column), it will select from current cell upto the last used cell.

ctrl+end will take you to the last used cell (lastrow+lastcolumn)

isladogs · Feb 14, 2018

To clarify, first use Ctrl+Home to go to cell A1.

Ctrl+Shift+End takes you to the populated cell with the largest row & column value - NOT the last cell that you populated

So in the context of what I was describing Ctrl+Shift+End is CORRECT to determine the end of the used cell range

Tieval · Feb 19, 2018

Quickest way of all is to just put up with it. Import the spreadsheet with all the empty rows and once complete just run a query to delete all blank rows from your table.

moke123 · Feb 19, 2018

Quickest way of all is to just put up with it. Import the spreadsheet with all the empty rows and once complete just run a query to delete all blank rows from your table.

The problem with this approach is the growth in db size over time. I tested adding and deleting 1000 blank records at a time and the db size increases @32kb's each time. Not that it's a lot but why do it when it can be avoided.

CJ_London · Feb 19, 2018

The problem with this approach is the growth in db size over time

compact and repair should be part of a regular maintenance routine. Otherwise change the way the data is imported. Link to the excel file then run an append query, filtering out the blank rows.

900+ blank records appearing in Table! (1 Viewer)

marthacasting

Registered User.

plog

Banishment Pending

marthacasting

Registered User.

CJ_London

Super Moderator

marthacasting

Registered User.

CJ_London

Super Moderator

Pat Hartman

Super Moderator

marthacasting

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

Tieval

Still Clueless

moke123

AWF VIP

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread