Transferspreadsheet Question

[fcmedina78]

I am using the above mentioned function to import information from Excel. The
first time I tried it Access created a table with a list of the items it had
problems importing. I deleted the imported information and fixed my
formatting and all but one record imported correctly. The problem I am having
is that I have over 22 thousand records and trying to the one record that
didn't import correctly is impossible and access did not create a table this
time listing the error. Is there a way to edit my code that will force access
to make a table that lists the problem record? Here is the code I am using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblWinEntry",
"C:\Users\cmedina\Desktop\Oregon Win for Import.xls", True, "A1:C22057"

 

[Chris O'C via AccessMonster.com]

Convert the spreadsheet to a csv file and then use the transfer text wizard
to import the file. The wizard will create a table listing any records that
didn't import and the reasons why.

Did you know it's a ***lot*** faster and easier to import a text file (.csv, .
txt, .tab) into Access than an Excel spreadsheet (.xls), because Jet doesn't
have to dig through all the formatting?

Chris
Microsoft MVP

 

[Jeanette Cunningham]

Is it possible to find a unique combination of fields that you could use to
run a check to compare the spreadsheet with the access table?
If you had a primary key, you could link to the spreadsheet and create a
query on the unique field.
You could create a second query using the same unique field in the access
table and join to the query created first above.
If you create a left join with show all records from the spreadsheet based
query and show the matching records from the access table, you will be able
to find the field that didn't import - put the criteria Is Null in the
criteria row for the field from the access table.
This will show you the primary key record from the spreadsheet that has no
matching record in the access table.


Jeanette Cunningham -- Melbourne Victoria Australia