Link Excel with Access

[airtabaki]

Hi,

I have an access databank where I store some info. Now every week I need to have an update from 3 people of these info. What I have made is a querry that filters only the data that has to be updated;
Now those 3 people can't work with Access. So I run the Querry and I copy - paste it to Excel. In this way they only need to change the Excel sheet.
Until now we have been looking for changes manually in this Excel form and changed them in the databank. It is very easy to make of the 3 files I get of these 3 people a resume file by copying and pasting. But these processes won't work in Access I'm afraid. Now I like to make it some what more automaticly. How do I do this?

PS I've already tried links in ACCESS, but I don't find a lot of info on it, and I tried it once to create such a file and when you open it in Excel, my system runs dead.

 

[mobyduck]

Why not create a temp table using:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "temp", "c:\data\data.xls",True

Then run an update query from this table against your main table.

Don't forget to kill the temp table when you're done.

MD

 

[Pigster14]

Well, you can link the spreadsheet from Excel into Access, but then you have to know what to search on to update the certain fields from the spreadsheet to the Access table.

However, here would be my suggestion. Can these people truly not work in Access? Or is it they are not willing to work in Access? Because what you could do is put the data on a form in Access and you could even do datasheet view. Have like a main form with a button for each person or you can have one button and then when they click the button the data they see is filtered to just what that person needs. They can make their changes on the datasheet view form or you can make a nice continuous form to show the data and let them edit it there. It will look some what like Excel, they can make their changes right in the database and then no manual work is needed on your part.

But, if you truly can only have them work in Excel then what you could do is import back in each spreadsheet and re-build the original table. If you gave Person1 - 30 records and Person2 - 30 records and then Person3 - 30 records, then import each spreadsheet of 30 records back in. Do a make-table query with the 1st 30 records and then append the next 2 and your table of 90 records would be back and that way you would not have to worry about updating particular fields just update the whole record. That would automate it as well. You could link in the spreadsheets if you wanted instead of importing them in code.

Hope this helps.

Pigster14