Delete Problem When Migrating from MS Access 2003 to MS SQL 2005

Question

Delete Problem When Migrating from MS Access 2003 to MS SQL 2005

gedlar

Mr or Mrs. 500

Points: 504
More actions
December 23, 2008 at 8:34 am

#199184

I'm in a project where I have to fix bugs when migrating from MS Access 2003 to MS SQL 2005. After migration, I cannot delete a record in a table from the user end. It gives me the error message "Could not delete from specific table." I looked at the code for the delete button, and it had a problem with this code:
CurrentDb.Execute "DELETE * FROM tblEventsDiversity WHERE ERecordNo = " & CStr(Me!ERecordNo.Value)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

First I'd get rid of the "*" in the delete as it is not necessary in SQL Server. Then I would look at permissions on the table for the user.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gedlar Mr or Mrs. 500 Points: 504 More actions · Answer 2

I got rid of the * and that didn't matter. I set the user's permission to do everything (delete, add, update, etc.), and that didn't help either.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Is the table in the dbo shema or another schema?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gedlar Mr or Mrs. 500 Points: 504 More actions · Answer 4

I don't know much about schema. How do I check that, and what should it be?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

From BOL:

Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. You can think of a schema as a container of objects. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

If you use SSMS, it shows schema along with table as schema.table. You can also run this query:

[font="Courier New"]SELECT

S.name AS schemaname,

T.name AS tablename

FROM

sys.tables T JOIN

sys.schemas S ON

T.schema_id = S.schema_id[/font]

When you create a table and do not specify the schema it is created in the creator's default schema.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gedlar Mr or Mrs. 500 Points: 504 More actions · Answer 6

I ran the query, and it gives me all of the tables, like

schemaname tablename

1 dbo EmailText

I used the MS SQL Migration assistant to migrate, so I didn't create the tables from scratch. Does that mean it is created in the creator's default schema?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

It is in dbo, which is usually the default for the database creator.

What did the table you are trying to delete from turn up as?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gedlar Mr or Mrs. 500 Points: 504 More actions · Answer 8

dbo tblEventsDiversity

All the tables have schema dbo.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

I'd recommend using SQL Profiler to see what command is being sent to the SQL Server. This can be found under the tools menu in SSMS. You can choose the standard template, the on the evnets tab make sure the textdata column is selected for all the events.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gedlar Mr or Mrs. 500 Points: 504 More actions · Answer 10

I don't know how to use SQL Profiler. Can you teach me how?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

Use Books on line and/or go to http://www.jumpstarttv.com. Here are the search results for Profiler at jumpstarttv

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

If you're trying to delete "from the user interface" - you mean deleting rows from the Access interface in linked tables?

If you don't have primary keys set up (or didn't identify one or more columns to ACT as a primary key), the linked tables are de facto read-only. If that's the case, then drop and add the linked tables again. When it prompts you for what columns "are unique", make sure to pick a good combination.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

Matt Miller (12/23/2008)
If you're trying to delete "from the user interface" - you mean deleting rows from the Access interface in linked tables?
If you don't have primary keys set up (or didn't identify one or more columns to ACT as a primary key), the linked tables are de facto read-only. If that's the case, then drop and add the linked tables again. When it prompts you for what columns "are unique", make sure to pick a good combination.

Ahhh, my limited Access experience bites again. Thanks, Matt.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gedlar Mr or Mrs. 500 Points: 504 More actions · Answer 14

Matt Miller (12/23/2008)
If you're trying to delete "from the user interface" - you mean deleting rows from the Access interface in linked tables?

Yes

If you don't have primary keys set up (or didn't identify one or more columns to ACT as a primary key), the linked tables are de facto read-only. If that's the case, then drop and add the linked tables again. When it prompts you for what columns "are unique", make sure to pick a good combination.

Here's the situation. There's a parent table called TBLEvents, which uses the primary key called ERecordNo. ERecordNo is pointing to a child table called TBLeventsDiversity. I'm trying to delete a record from this child table, which should not necessarily have a unique key. Are you saying I need to pick two keys to make this work?