Importing unique records from excel into access

  • Thread starter Thread starter kejo41
  • Start date Start date
K

kejo41

I am really new to both access and VBA (any kind of programming actually). I
want to be able to import an excel spreadsheet into an access table. I know
I can use the TransferSpreadsheet function, however, the spreadsheet I want
to import is updated regularly. I don't want to import duplicate information
each time I import the data. I only want to append the new records from the
excel spreadsheet to the end of an existing table in access while ignoring
records that have already been imported. How do I do this?? I would
appreciate any help I can get, as I said I am very new to all of this.
 
Okay, first lets work on terminology. TransferSpreadsheet is not a Function.
A Function is a procedure that returns a value. You may or may not have to
pass it one or more arguments for it to return a vaule. For example, the
Date() function requires no arguments. It just returns the current system
date. But, the DateAdd funtion requires 3 arguments. You have to tell it
what time interval to add, The number of the time intervals to add, and a
Date/Time value for it to use in the addtions. For example, if I want to
return a date for 3 days from today's date, I would use = DateAdd("d", 3,
Date)

TransferSpreadsheet is a Method. A Method is a child of an object that does
some work. You already know how it works, so I wont bother explaining that.

Now, what you want to do doesn't directly involve the TransferSpreadsheet
itself. This is done using one or more queries. To be able to append new
records only to your table, you will have to identify one or a combination of
fields that define it as a unique record. In other words, that field value
or the values of combination of fields can exist only once in the the table.
For example, if you were adding new people to a table, you could not be sure
a name in the table would be unique, but each person has a unique Social
Security Number. So you can filter an append query so that it only adds
records with SSNs that are not already in the table. You do that by using a
WHERE clause in the append query:
WHERE NewDataTable.SSN NOT IN (SELECT PersonTable.SSN FROM PersonTable)

So, the procedure I would recommend would be to create a table with the same
structure as your main table to use for the import from Excel. The steps
would be:

Delete the data from the Import Table
Import from Excel to the Import Table
Run an Append Query to add only new records to the Main Table

If you need more detail, post back.
 
Thanks so much I think this is exactly what I needed. A little more detail
would be much apprecitated. Also, uniqueness in this table is based on two
fields not just one. Does this change how I use the where clause at all or do
I simply use it in both fields?

Thanks again.
 
Back
Top