Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 9th, 2007, 09:19 AM
Friend of Wrox
 
Join Date: Apr 2007
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default Empty rows created in Access table

Hi All

I am tyring to import excel sheet to Access Table doing this

DoCmd.TransferSpreadsheet acImport, , "TestData", FileName, True

But it created many empty rows in begining

Any idea

regards

Ayaz

 
Old May 9th, 2007, 11:02 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Does this happen every time you use this sub? Were the blank records there from a previous test of the sub?

If you delete all the blank records and run the sub again, are there more blank lines? Did you try setting the spreadsheet version in the empty set of commas?

Just some ideas to get started.




mmcdonal
 
Old May 9th, 2007, 11:12 AM
Friend of Wrox
 
Join Date: Apr 2007
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default

if I run that sub again it will create more empty records with some data in the end

and this is data in excel sheet

SXID PID SubPID Quantity Date User
1 Alt489-3B05 Blank489-3B05 1 13-Jan-07 12
2 Alt489-3B06 Blank489-3B06 1 13-Jan-07 12
3 Alt489-3B05 Blank489-3B05 1 13-Jan-07 122
4 Alt489-3B06 Blank489-3B06 1 13-Jan-07 23
5 Alt489-3B05 Blank489-3B05 1 13-Jan-07 34
6 Alt489-3B06 Blank489-3B06 1 13-Jan-07 45
7 Alt489-3B05 Blank489-3B05 1 13-Jan-07 99
8 Alt489-3B06 Blank489-3B06 1 13-Jan-07 12


this is imported data in Access table from above sheet

SXID PID SubPID Quantity Date User







1 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 12
2 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 12
3 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 122
4 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 23
5 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 34
6 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 45
7 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 99
8 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 12



 
Old May 9th, 2007, 11:14 AM
Friend of Wrox
 
Join Date: Apr 2007
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default

If i delete all these records and run sub again it will create same empty rows

and i didnt try spreadsheet version in the empty set of commas

 
Old May 9th, 2007, 11:27 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Are there characters in the empty rows anywhere, something that looks like a small box?

I am thinking that you have deleted records from the excel spreadsheet, and excel still considers them valid rows. And since there is no row ID (which you seem to be importing) then it sorts them to the top.

Try adding an ID to the number of rows BELOW the data in your spreadsheet that are showing at the top of your table. So if there are 10 empty rows at the top of the table after import, then add 10 more IDs in sequence at the bottom of your spreadsheet.

If when you do the import, you have no empty rows, and ten more blank records except for the IDs, then I think the issue is with Excel and not Access or the DoCmd parameters.

I am not sure how to remove empty rows from Excel, but it probably still thinks they are valid. Worse case, copy and paste the spreadhseet data to a new spreadsheet (without copying the empty rows) and try importing that. I think that will resolve it since the new spreadhseet will have no history of those rows containing data.

Did that help?

mmcdonal
 
Old May 9th, 2007, 05:47 PM
Authorized User
 
Join Date: Mar 2007
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Ayaz,

I too have had this problem from time to time with importing excel files into access, and i tend to agree with mmcdonal that this is a issue with excel. I have found that best practice when importing data into access is to use a staging table rather than import directly into a table. If you create a staging table in the same format as you primary table, then import all the data into the staging table, once the data is imported you can then append your primary table, excluding null/blank records and this also allows you to apply any formatting to fields such as Date/Time etc. I have found this to be by far the best way to import data, my database imports xls, csv and txt all over the place, then exports simular, and since implementing staging tables my problems have been greatly reduced. Also ensure you clear the staging tables before you import new data (goes without saying i guess).
I post this as an alternative to trying to resolve bugs in other apps that impact your database.

Regards

Darren

 
Old May 10th, 2007, 02:50 AM
Friend of Wrox
 
Join Date: Apr 2007
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default

Hi all,

Problem with excel sheet
Records were deleted from the excel spreadsheet, and excel consider those rows valid even it was not visible.
Thanks all
Regards
Ayaz

 
Old May 10th, 2007, 06:23 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default


  I don't think that is a bug in Excel, just that there must be a way around it in Excel.
  I like the idea of the staging table, however, since this accounts for this 'feature' in Excel.


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
populate details view or list view non empty rows iinfoque ASP.NET 2.0 Basics 0 March 11th, 2007 06:11 AM
The last empty record in table should not show alie VB Databases Basics 4 March 9th, 2006 07:38 AM
Dynamic Table calls 2 empty rows nancy Dreamweaver (all versions) 1 January 18th, 2006 06:00 PM
Empty table for dropdownlist. macupryk BOOK: ASP.NET Website Programming Problem-Design-Solution 2 August 16th, 2004 06:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.