how to import excel data into access database through visual basic code and eliminating the redundant data?

Question

I have to import Data from Excel to Access through vb code.Data will be present in access.consider the excel sheet is having 65500 rows of data and the data in access will be repeated in this Excel Sheet.so If I check in Loop, it will take time. Is there alternative way to do this?

Excel sheet will be in Client Machine. so import will be possible thru code only.

Answer 1

In the Modules Section of Access, start a new module.
Create your Sub, name it (Private sub ()) and in it put this code:

DoCmd.TransferSpreadsheet

When you press space after typing in the command above, you get prompted for the parameters and options for them. There are several parameters to this command. Look them up in VBA help for more explanation, but you tell the command to Import, Export or Link, You tell it what kind of Excel file it is, The name of the table imported to or the name of the link to the Excel sheet, the name of the file, including it's location(path), if it has field names, etc.

Maybe you would consider linking your Access database to the Excel sheet. If the Excel sheet is available at all times, it would be more efficient to not have a full copy inside your database, but have a link to the data. It can be used in Access as if it were a table, but it doesn't take room and the data isn't duplicated.

If you import text files in the future, it is best to first import your file once manually and set up an Import Specification that you save. Remember the name, because you will refer to it in your code that will import your file later. Import Specs are not used with Excel sheets.

The command details can be seen at the link below:

Regarding the redundant data being excluded: You can Link to the excel sheet, then use an append query to append to a table in your database. The table you append to must have a Primary Key assigned. When you attempt to append, it will reject records with duplicated Primary Keys. Your Primary Key can be several fields, but normally it is a record ID or something that you use to link to other records. You can set up a table with primary key put on fields like Streetnumer, Streetname, LastName and only unique combinations of those three fields will be imported.

Answer 2

I just now tried copy pasting each and every column from excel to access. Mind you i had 16,000 rows in it and it hardly took 2 mins. Why go through the pain of writing a program?