×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Importing Excel Spreadsheets

Importing Excel Spreadsheets

Importing Excel Spreadsheets

(OP)
Hi,

I am currently experiencing trouble importing an Excel 97 spreadsheet into an already existing empty Access 97 table.

The spreadsheet has over 7600 rows and 118 columns and was originally exported from Access.  I am using the 'TransferSpreadsheet' method as it will need to be automated.  The problem is is that it only imports just over half of the records.  Any ideas?

MartinF

RE: Importing Excel Spreadsheets

Martin,

Do you get any error messages?  Can you post the code that you are using?

Kathryn

Kathryn


RE: Importing Excel Spreadsheets

(OP)
Hi Kathryn,

No I don't get any error messages at all.  It takes a while to run as expected, but then returns just over half of the records

Here is the code I am using:

Function Import()
Dim OpenFile As String

On Error GoTo RetrieveError

DoCmd.SetWarnings False

OpenFile = "s:\private\access\Database Front End source code\CD tools\CD contents\data\dataattached.xls"

DoCmd.RunSQL "DELETE DataAttached.* FROM DataAttached"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "DataAttached", OpenFile, True
DoCmd.SetWarnings True

Exit Function

RetrieveError:
    MsgBox Error$
End Function


Martin F

RE: Importing Excel Spreadsheets

What happens when you try to do it manually?  Are all the records returned?

Kathryn


RE: Importing Excel Spreadsheets

(OP)
Hi Kathryn,

I've just tried it manualy and it comes up with a message box stating the Access was unable to append all of the data.  It then goes on to say that 0 record(s) were deleted and 0 records(s) were lost due to key violation errors.  When I check the data only half of it is there!  So it has the same results, but just brings up that message box saying that not all the data has been appended, but nothing has been lost!!

A puzzled MartinF

RE: Importing Excel Spreadsheets

Is there a primary key in the table into which you are appending data?  (grasping at straws here!)

Kathryn


RE: Importing Excel Spreadsheets

(OP)
Kathryn,

There was a primary key, which I removed and tried again, but still same results.

MartinF

RE: Importing Excel Spreadsheets

OK, what if you try to import it into a new table, not an existing table?  Does the same thing happen?

Other thoughts:

I wouldn't worry about the future automation at this point; first we have to get the process working.  Try naming the spreadsheet as 3 or four named ranges, each having about 2000 records and see if they will import.  If each imports, then you know that there is no trouble with the data.  This brings up a thought:  does the data always import up to the same row??

If there is a key field(s), import the data as two tables, again using named ranges, both of which have the key field(s) in them.  Then you will have two tables which are in 1-1 relationship.

Kathryn


RE: Importing Excel Spreadsheets

(OP)
Kathryn,

I've just tried spliting the data in half across two worksheets and tried importing each worksheet in to the table, and again this time only half of the records off each worksheet inmported.  I then tried the same thing but imported the first worksheet into a new table, and then the second worksheet into the same table and all the records imported ok.  Now I have tried putting all the records back together on one worksheet and then tried importing them all into a new table in one go, and again they have all now imported ok.  I have compared the new table structure with the existing table and the only obvious difference is is that one field that was an auto-number is now just a number field, however i did not think this would affect it because i have imported data in to auto-number fields before without problems.  The other thing that is puzzling me though is that the existing table that the records will not import into is exactly the same table that they were exported from.

When attempting to import the data into the existing table, it is always the same number of records that get imported.

MartinF

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close