Updating a table using a spreadsheet as input

[Ken]

Greetings all:

I have a client who is using an Access 2003 application to keep their
membership data. On occasion they receive spreadsheets from
organizations in their field with prospective member data. My client
would like to use this information to add these records into her
application.

The database uses a sequential number as it's primary key, linking
member info, and addresses, as well as two or three other tables not
in play here. Is there any way in Access to both parse the data from
the spreadsheet, and assign this unique ID number in the process?

Any help is appreciated as always.

Ken Halpern

 

[John W. Vinson]

The database uses a sequential number as it's primary key, linking
member info, and addresses, as well as two or three other tables not
in play here. Is there any way in Access to both parse the data from
the spreadsheet, and assign this unique ID number in the process?

Sure. Use File... Get External Data... Link to link to the spreadsheet (choose
Excel from the "files of type" dialog).

Run an Append query to append the data from the spreadsheet into the proper
fields in the table. Don't append anything into the autonumber field and it
will increment normally.

Since Excel doesn't have "strong" datatypes, you might run into some problems
with fields being misinterpreted (e.g. zipcodes as Number fields instead of
text, losing leading zeros). So check the data once it's imported!

John W. Vinson [MVP]

 

[Ken]

Sure. Use File... Get External Data... Link to link to the spreadsheet (choose
Excel from the "files of type" dialog).

Run an Append query to append the data from the spreadsheet into the proper
fields in the table. Don't append anything into the autonumber field and it
will increment normally.

Since Excel doesn't have "strong" datatypes, you might run into some problems
with fields being misinterpreted (e.g. zipcodes as Number fields instead of
text, losing leading zeros). So check the data once it's imported!

John W. Vinson [MVP]

Thanks for the direction John. I think this was one of those that was
staring me in the face and I didn't see it.

I'll let you know how that works out.

Ken