Solved

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

Posted on 2006-11-29
8
1,166 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exporting from Access 2016 to a CSV file 4 49
Import MySQL data into MS Access using VB.Net interface 5 29
VBA modules import 4 57
Run Stored Procedure uisng ADO 5 21
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

861 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