Solved

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

Posted on 2006-11-29
8
1,168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 4

Assisted Solution

by:Clothahump
Clothahump earned 75 total points
ID: 18040480
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
ID: 18040614
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
ID: 18040630
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18040648
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 18041129
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
ID: 18041282
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
ID: 18045458
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
ID: 18045552
Glad to be of assistance :)

Thanks for the points, Tom!

~Jillyn
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

691 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