Linking excel with access

[AnnPhil]

I have an excel spreadsheet that i have linked to an Access database. I need to append and delete records from the spreadsheet into an existing table in my Access database. I am able to append specific records from the spreadsheet into my table but then i want to delete the records from the spreadsheet. When i run the delete query i get the following message; "Deleting data in a linked table is not supported by this ISAM" Is there some way i could get this to work?

I have records in this spreadsheet that needs to get into the database but once it is appended to the database i need those records deleted from the spreadsheet. I am kinda using this spreadsheet as a temporary table so one employee who does not use access can put data into his spreadsheet and then the other employees can pull want they need from this into the database.

Any suggestions?

 

[misscrf]

I had a thought, so I figured I would post it. There is probably a much easier way. My thought would be to run a command that opens the excel spreadsheet, and then runs the delete command directly to it - careful and make a backup to test on till you are sure it is running right.


It would take some vb code.

It is true that you cannot delete data from linked tables though, and I think they did that for good reason.

You might try switching the setup around. Have the table reside in access, this fixes the delete issues. Then create and excel worksheet and do and import data to that database. Set in tools options to refresh datasource on open. The only issue here, is getting his work back to the db. I don't know how to take care of that.

Would it be too hard for your user to input into a form in access set up as a datasheet? It looks the same as excel.

I hope something of this helps.

 

[misscrf]

well, thats a good reason then, isn't it? lol

it also includes sql , but yes for the same reason - access method.

 

[nealberk]

Actually you used to be able to do this until, if I remember, Access 2003 SP2. Apparently Microsoft lost a legal battle and rather than paying royalties, they decided to drop the feature.