We help IT Professionals succeed at work.

can't delete data from any tables

GordonPrince asked
I've created a SQL 2005 database from a backup from another system and am writing an application with Access 2003 (as a project) to work with the data. I'm experienced with SQL permissions and programming & Access programming. Users have been able to read data and add it, but I can't enable deleting anything on the production server. On the copy of the database on my development system, I can delete things.

Even logged onto the production systems as me -- the creator of the database, and according to the SQL manager, I'm the owner of the database -- I can't delete data from Access. I can from the SQL Management Studio, though. The behavior is in both converted tables and in a new table I created. Both tables I've tried have primary keys defined on them. The newly created table I've dropped and recreated, but the behavior persists. The behavior occurs both from browsing a table (shown in screenshot) or in SQL code in the application.

Any idea why users can't delete data? screenshot of error message
Watch Question

Bill RossProgrammer


What is the connect string you are using?  
What ODBC driver?
What version of SQL?

There could be a mis-match between the client-side driver and the version of SQL on teh server.  Investigate the above and make sure you are using the correct client.  If you can fill in the blanks then I can help.




Here's the connection string from the code using ADODB.Connection to execute the delete statement:
"App=E|P Bankruptcy;Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;" & _
                                                                   "Data Source=EPBankruptcy;Integrated Security=SSPI;" & _
                                                                   "Initial Catalog=Bankruptcy;Data Provider=SQLOLEDB.1"

Also I've attached an image obtained from the Access > File > Connection dialog. connection dialog screenshot odbc dialog screenshot
They're both SQL-2005 -- 9.0.5057 on my development system, 9.0.4053 on the production system.

Try using DAO and a pass-thru query as follows:
1.  Create a new query named PTQ with and routine SQL statement like select * from tablename;
2.  Change the properties to SQL passthru
3.  Create a function as follows:

public function deleterecord(vID)
  dim vCon as string, qdf as DAO.querydef
   vCon = "ODBC;Driver={SQL Server};Server=DBSVR;UID=yourusername;PWD=yourpassword;APP=Microsoft®  Access;DATABASE=Bankruptcy;LANGUAGE=us_english;LOGINTIMEOUT=60;Network=dbmssocn;Persist Security Info=False"
    Set qdf = db.QueryDefs("PTQ")
    qdf.Connect = vCon
    qdf.ReturnsRecords = FALSE
    qdf.SQL = "DELETE FROM template where TemplateID=" & vID & ";"
end function

Note:  vCon is all on one line and this is not cleaaned up code.  Just to test to see if it works.


Looks like you don't have permission on deletetion of record on the database. Check for the permissions.



I can't find a pattern. But I have a workaround. Instead of connection.execute "delete ..." I created a stored procedure that performs the statement. That runs with connection.execute "deletedSP". So I'm still baffled, but on we go.

Thanks for your ideas, guys.


No solution to the problem, I came up with something, so these answers are not proven to solve the problem or not.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.