import multiple excel sheets to multiple tables

I have some excel workbooks that have multiple sheets that I need to import into Access.  These worksheets are all different and the fields in the tables they need to populate may not be in the exact order as on the sheets.  What would be the best way to accomplish this?
nfstrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chuck WoodCommented:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TableName", "FileName", True, "Range"

Access Developer Reference
DoCmd.TransferSpreadsheet Method

The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
expression   A variable that represents a DoCmd object.

Parameters

TransferType Optional AcDataTransferType The type of transfer you want to make. The default value is acImport.
SpreadsheetType Optional AcSpreadSheetType The type of spreadsheet to import from, export to, or link to.  
TableName Optional Variant A string expression  that is the name of the Microsoft Office Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Access select query  whose results you want to export to a spreadsheet.
FileName Optional Variant A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNames Optional Variant Use True (1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.
Range Optional Variant A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
UseOA Optional Variant This argument is not supported.
0
Chuck WoodCommented:
The fields do not have to be in the same order as the headers on the worksheets.
0
Chuck WoodCommented:
acSpreadsheetTypeExcel12 = Excel 2007
acSpreadsheetTypeExcel11 = Excel 2003
acSpreadsheetTypeExcel10 = Excel 2002
acSpreadsheetTypeExcel9 = Excel 2000
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nfstrongAuthor Commented:
> The fields do not have to be in the same order as the headers on the worksheets.

But do they need to have the same field names?
0
Chuck WoodCommented:
Yes, they do.
0
nfstrongAuthor Commented:
ok, what if they don't?
0
Chuck WoodCommented:
It won't work.
0
nfstrongAuthor Commented:
Is there another method to use besides TransferSpreadsheet to import the data since the field names don't match?
0
Patrick MatthewsCommented:
nfstrong said:
>>ok, what if they don't?

Then you either:

1) Set up the Excel worksheet as a linked table, and then use a query to append the data from the linked
table to the destination table, or

2) Import the Excel data into a staging table, and use an append query to get the data from the staging
table to the destination table.
0
nfstrongAuthor Commented:
matthewspatrick,

I tried your option and I receive the Microsoft can't append all records error.  What causes this?
0
Patrick MatthewsCommented:
nfstrong said:
>>I tried your option and I receive the Microsoft can't append all records error.  What causes this?

My first guess would be a data type mismatch.  Check the data types in Access against the data types in'
the worksheet(s).
0
nfstrongAuthor Commented:
I went through and matched all of the data types and I'm still receiving the same error.  Any other thoughts?
0
Chuck WoodCommented:
Please post the complete error description.
0
nfstrongAuthor Commented:
Here is the error.
AppendError.doc
0
Chuck WoodCommented:
Your problem is that the data you are appending violates a key in the database. For example, if you have a field set to a primary key (no duplicates) and you append a record with a value in the primary key field that is the same as a record that already exists, Access will not allow you to append that record.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nfstrongAuthor Commented:
There are no duplicates in the data I'm trying to append and the target table is empty.
0
Chuck WoodCommented:
What are the attributes of each fiel in your table?
0
nfstrongAuthor Commented:
ABCTest is the imported Excel data and tblFinSumNetInc is the table I'm trying to append to.
AppendTables.doc
0
Chuck WoodCommented:
Which fields in ABCTest are you appending to which fields in tblFinSumNetInc?
0
nfstrongAuthor Commented:
Column A is ABCTest table fields being appended to column B tblFinSumNetInc fields
AppendFields.xls
0
Chuck WoodCommented:
The types and sizes line up. What indexing do you have on the tables?
0
nfstrongAuthor Commented:
Here are the indexes
AppendTablesIndexes.doc
0
Chuck WoodCommented:
I don't see any problems with these. How many records are you trying to append?
0
nfstrongAuthor Commented:
only 33 with this one.
0
Chuck WoodCommented:
And they all fail. Hmmm. Try entering one record into the tblFinSumNetInc table manually.
0
nfstrongAuthor Commented:
Ok, I entered a record manually and received an error that there must be a record in tblExecSum, which is the parent table.  That makes sense.  So I entered the primary keys needed in tblExecSum and reran the append query.  I get the same error, but it does append the 33 records.  Why would it still show that error?
0
Chuck WoodCommented:
Delete the data in the table and try to enter one record manually again.
0
nfstrongAuthor Commented:
I input a record manually and don't receive any error.  I deleted that record and reran the append query and it ran through just fine.  Not sure what the error was about.
0
Chuck WoodCommented:
I'm glad it is working for you now. You might want to consider a query that will update tblExecSum table before appending the new records to the tblFinSumNetInc table.
0
nfstrongAuthor Commented:
I will do that.  Thanks for all your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.