?
Solved

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

Posted on 2006-11-29
8
Medium Priority
?
1,178 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 300 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 700 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

839 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