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.

 

[Pat Hartman]

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

It is only non-database linked tables such as text files and spreadsheets that you can't delete from. That is because of the underlying access method the operating system uses to read/write them. It wasn't a decision by the Access team.

 

[misscrf]

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

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

 

[Pat Hartman]

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

What includes SQL? SQL tables are completely updatable from Access as are other RDBMS that you link to through ODBC such as Oracle, DB2, and Sybase. It is only "flat" files that don't support certain types of updates.

 

[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.