Solved

Why am I getting blank records in my Access Table...??

Posted on 2006-11-29
8
1,163 Views
Last Modified: 2011-10-03
I am importing data from an Excel spreadsheet into an Access table daily.  Before I import the records each day, I first run a delete query in Access to rid the table of yesterday's records.  When I import the new Excel data into the table it is creating several hundered rows of blank records, but it doesn't seem to do it everytime.  This makes me think it has something to do with the Excel data I am creating every morning to update the table.

In Excel, I working with a spreadsheet that identifies if a task has been "done" or is "pending".
I only want to take the "pending" records into the Access table.   In general, every day the number of "done" tasks goes up, and the number fo "pending" tasks goes down.  (Anything to do with the blank records?)  I filter the spreadsheet on a column for those identified as "pending" and then I copy those filtered records and paste special into another spreadsheet tab.  Then I go to Access and import the "paste special" tab that has the "pending' records on it.  But when I check the import I find that even though there were only 12000 records that should have been imported, I am getting 12484.  

All this is happening in Office 2000.  Any thoughts?  - Tom
0
Comment
Question by:tomfarrar
  • 4
  • 3
8 Comments
 
LVL 4

Assisted Solution

by:Clothahump
Clothahump earned 75 total points
Comment Utility
One of the things that has helped me when importing from Excel -

After creating the spreadsheet and loading the data, move to the first blank line at the end.  Click on the row selector number and drag down until you have a couple of thousand blank rows highlighted, then delete them.

That will usually prevent blank records from being imported.
0
 
LVL 8

Expert Comment

by:Jillyn_D
Comment Utility
Hi tomfarrar,

Another way to do this is to hit <Ctrl>+<End>.  This will take you to the last row Excel thinks exists.  Then use Clothahump's method to highlight all rows from there to the last valid row in your worksheet, and delete them!

Good luck!
~Jillyn
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Clothahump - Well, that certainly could be the solution.  It appears you have had the same problem.  Do you know why it might be happening?  That is what I would like to first know, and then what can be done about it (one of which is your suggestion)....
0
 
LVL 8

Expert Comment

by:Jillyn_D
Comment Utility
tomfarrar,

The most common cause for this that I've come accross is when a user selects columns and formats them.  However, it also happens when information is deleted from rows, but the rows themselves are not deleted.  Excel recognizes these formated or newly empty rows as valid members of the worksheet.

~Jillyn
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Well we seem to be getting somewhere on this.  I just noticed that the number of blank rows that are created in the Access table appears to be the difference between the number of records yesterday and the number of records today.  For instance, yesterday I had 12800 valid records in Access (that I used a delete query to get rid of) and I imported 12200 records today from Excel.  The number of blank rows was 600.  I use the same spreadsheet tab in Excel to bring each days records in after first deleting the old and then pasting in the new.  So you are perhaps right about what you are saying, but could the problem be in Access's delete query.  If it is an Excel problem, is there anything I can do on the front end, to eliminate going through the steps you've suggested?  I am going to turn this over to another user and I am trying to keep the number of steps to a minimum.

Thanks to both of you for helping me on this.  - Tom
0
 
LVL 8

Accepted Solution

by:
Jillyn_D earned 175 total points
Comment Utility
tomfarrar,

It would be much simpler to run a delete query in Access to delete any blank recorrds once the information has been imported.  You could add it to the import routine, then it would be automated and wouldn't add another step for the new person.

You could also write a macro on the Excel side to take care of it.

~Jillyn
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Good answer, Jillyn.  Clothahump gets an assist.  I appreciate both of your responses to my issue.  Thanks again.  - Tom
0
 
LVL 8

Expert Comment

by:Jillyn_D
Comment Utility
Glad to be of assistance :)

Thanks for the points, Tom!

~Jillyn
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now