Uploading Excel to Access?

Maddie

Hi,

Hopefully someone can help me - I had a search the other day and couldn't find an answer for myself.

I'm trying to find out if there is a way to upload an excel spreadsheet to an existing access database to edit existing info.

We have a database of client info at work which only one secretary has access to. It needs to be updated monthly. Currently she prints out info and passes to the relevent people to update, then manually inserts it into the database.

I'm sure there must be an easy way to download, send by email and then upload, but having had a look at the help menus and a play myself I can't work it out. (We have managed to download to excel but can't upload).

Any help would be much appreciated.

Cheers,

Maddie

benjus

Using Access 2003 (I'm not sure about other versions) you can open the database, then go to File->Get External Data->Import. This can open Excel files and dump the contents to a new database table.

You could write an Access module to merge the changes from new table into the existing table and then delete the new table.

However, it seems to me that the "correct" way to do this would be to share the database between all employees that need to make changes, with appropriate access rights for each user. It all depends on how much time and resource you want to spend on it.

timbim_2

Yes, that would be the logical way to do it. However, it requires a separate file server to allow multi-user acess.

PhilCo_2

If you follow the above you can set import specifications to bring in just the columns you require. If you have a form you can create a button and put a macro on it that will allow you to upload the file by clicking it. For the macro you'll need to use the TransferText action.

Alternatively if you already have the table created in Access in the same order as the spreadsheet you can delete all the entries in the table, copy the spreadsheet data, select the first cell of the record 1 in Access and do Edit - Paste Append.

PhilCo_2

If you've got seperate spreadsheets to upload you would probably have to upload to a dummy table and run a Paste Append query to load the data to main table.

isofa

You can also link an Excel sheet as a table in Access...

Maddie

Loads of thanks for all the replies. I've never used Access before, so will need to have a little play and get my head round it.

Cheers!

Maddie