Access/Excel Import_3000+ records

[yamefui]

Here's my situation:

I have an Access table that has 34 fields, the majority are text and have
field size restrictions. I also have an autonumber, hyperlink, OLEobject,
memo, Yes/No, Date field(s).

If I have a source that can provide me between 3000-5000 records in a
spreadsheet of information on business contacts such as name, address, city,
state, zip, phone number, fax number, pager, etc., is there a method to be
able to import that data into my current formatted access table instead of
keying it in?

Excel appears to classify everything as text so when I try to import I
cannot since there is a format clash.

*Is there a way to change the format of the fields/records in Excel to
exactly mirror that of Access so the data becomes importable? *Also, how
would I build a perfect column match between the two programs so Access knows
exactly where to put the Excel data? The Excel spreadsheet only has a much
lesser amount of the columns that the Access table has because the provider I
would get it from only maintains/collects certain data fields, not nearly as
many as I have in the table.

*Last, if this process is possible, once the data is in the target Access
table, I could use a duplicate query to locate duplicate information,
correct? This information is revised on a regular basis because it contains
real estate agent info. Due to the turnover/attrition ratio in this
employment field, the provider source (spreadsheet) is constantly in flux so
periodic updates to the Access table have to be made which will entail data
integrity issues (removing the duplicate name entries if there are any or
updating current entries with new information).

I know it's not wise to trust another provider's data but I'm just seeking
alternatives to the potential data entry situation with this many records on
a continual basis.

Thanks for your suggestions and direction.

jon

 

[yamefui]

I am using Access 2003 and Excel 2003.

 

[Ken Snell \(MVP\)]

Import the EXCEL data to a temporary table that will accept the EXCEL data
in the current format. TransferSpreadsheet will do this for you, including
the creation of the temporary table if you want. (Otherwise, just create a
table with 34 fields, all set at 255 characters Text.)

Then use an append query to copy the data from the temporary table to the
permanent table.

 

[yamefui]

OK, thank you. I wasn't aware of this macro option. Somehow I overlooked it
in Online Help. I will give it a try.

 

[Ken Snell \(MVP\)]

TransferSpreadsheet can be done by a macro or by VBA code.