Could Not Delete from Specified Tables

[markthien]

Hi guys,

I am able to delete record from specified table when I just run the program from visual studio 2008. I am using ms access as database. My connection string is:

Provider=Microsoft.Jet.OLEDB.4.0;data source=" + @Directory.GetCurrentDirectory() + "\\smsmanager.mdb;Jet OLEDB:Database Password=matmat

However, after setup and deployed in Program Files folder and try to delete record from ms access db, I got the error saying "Could Not Delete from Specified Tables".

Please kindly advice on this issue. I have tried to google it but to no avail where this website provide no valued information: http://support.microsoft.com/default.aspx?kbid=240098.

regards,
Mark

[gusano79]

What is the specific query you were trying to run? Access isn't able to handle some kinds of deletes that other database systems can.

[markthien]

What is the specific query you were trying to run? Access isn't able to handle some kinds of deletes that other database systems can.

Hi gusano79,

I found out that it's the folder permission issue. After setting the permission to FULL CONTROL under Properties->Security of the folder, I am able to perform delete operation in ms access. I am running under windows vista

However, this is not really a solution. I need to distribution my software to users and it's kind of shame to ask users to do this extra step.

Appreciate any further advice please. Thanks in advance!

Cheers,
Mark

[nick.crane]

I ran in to the same problem some time ago.
One easy way is to put the files in a folder off the root. (e.g. "C:\MyData").

The more elegant but much more difficult solution is to have a custom action in the installer to modify the security of the path.
Note: Only and Administrator can modify file security, so you can not do this in your program.

These links may help you find a solution.
Set file permissions from an Installer custom action
HOW TO: Alter the "SetSecurity" project ...
If you have any questions just post and I'll try to help.

[gusano79]

Is Access something you have to use, or are you able to consider alternatives? Access is great for prototyping and rapid development, but IMO it's more trouble than it's worth if you're delivering an application to other people.

[markthien]

Hi gusano79,

I was considering mysql but I can't budle mysql together with my program to deliver to users as it's illegal. It's also kind of shame if I ask users to download mysql separately in order to use my program.

Can you please share with us what kind of trouble you encountered with access ?

regards,
Mark

[gusano79]

Provider=Microsoft.Jet.OLEDB.4.0

A quick (flame retardant =) distinction--Jet is the database system, and Access is an application front end that is often used with it. A lot of people conflate the two. We're really talking about Jet here.

I was considering mysql but I can't budle mysql together with my program to deliver to users as it's illegal. It's also kind of shame if I ask users to download mysql separately in order to use my program.

For a file-system database, I prefer SQLite; it resembles its server-based counterparts more closely than an Access database does. If you're doing .NET development, there's System.Data.SQLite.

Can you please share with us what kind of trouble you encountered with access?

This was a while ago and I don't remember specifics; sorry. In general, I had problems with more complex queries. There was usually a way to do what I wanted, but it ended up being ugly (== hard to maintain) sometimes. For example, deleting based on an inner join requires a keyword specific to Jet SQL.

We also had an Access database in a production environment for the longest time... it wasn't in something I'd call a high-volume application--maybe 30 concurrent users as an absolute maximum spike, but we had problems with database corruption.