Couple questions on importing from Excel to Access

[naab0001]

I'm an intern and am fairly unexperienced in working with Access and Excel together. I'm trying to import just one record from an Excel worksheet into the current database. I've read that it's usually a good idea to create a "dummy" table from the imported Excel file, and then append the dummy table to the primary table using a query. 2 questions/problems:

1. When I import the record from excel into the new table, it creates 300 records. All are blank except for the one record, which is the only one I want to import. Is there a way to have access import just the one record with data in it?

2. Can I create a macro that will import the record from the excel file, put it in the dummy database, and then append it using a query? I just want to have it as automated as possible. I'd been keying in all the data manually before. Thanks for any help and insight into these issues,

Bronson

 

[Bob44TheCat]

Hi naab,

From bitter experience, yes it is a good idea to import into a 'dummy' or 'holding' table, then run an Append or Update query (note - if you run an Update query it will update an existing record OR append a new record if there is not a matching record already in the target table).
Re your q's:
1. I'd guess it's importing blank rows from the Excel file. In Excel do a Ctrl+End and see where the cursor ends up. If it is way below the end of your actual data, delete those rows before importing.
2. Yes, you can automate this either by macro or preferably using VB. Either way you need to read up on the TransferSpreadsheet function. (VB can be a bit frightening at first but it's worth looking into. If you're pushed for time or on a steep learning curve, use macros by all means, but be prepared to move up to VB in time). However - until you are 100% confident that the data you are importing into the database is 'clean', I'd suggest you automate the import into the 'dummy' table, then look at what you've imported, and then, when you're happy with it, run the Append or Update query.

Hope this helps,

Cheers,

Bob.