We help IT Professionals succeed at work.

can't delete data from any tables

GordonPrince
GordonPrince asked
on
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
Comment
Watch Question

Bill RossProgrammer

Commented:
Hi,

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.

Thanks,

Bill

Author

Commented:
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.
Programmer
Commented:
Hi,

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 & ";"
    qdf.OpenRecordset(dbOpenDynaset)
end function


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

Regards,

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

http://office.microsoft.com/en-us/access-help/about-user-level-security-mdb-HP003070410.aspx

Author

Commented:
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.

Author

Commented:
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.