Solved

on qeury access 2013

Posted on 2014-12-22
7
395 Views
Last Modified: 2014-12-23
hi

i have a query like this on access 2013:


SELECT table1.makatID,table1.Price, table2.Description
FROM table1 LEFT JOIN table2 ON table1.MakatID = table2.makatID;

Open in new window



i have a form that is based on this query. but the problem is that when i delete a record on the form , it's delete the record  from table1 and from table 2, but i want that it will not be able do delete any value from table2, and the record will be  deleted only from table1, how can i make it with an sql string query.

thanks alot
0
Comment
Question by:bill201
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40514347
Is there a relationship set between the two tables with referential integrity set to do cascading deletes?   If so, that could be what is causing the deletes from the second table.   Also, if I remember correctly, setting the form to do "inconsistent updates" using a dynaset may be part of the solution.

I'll try to look up in a few minutes if no one else gets to this before me.  You might want to look at these two suggestions, though.. might be enough to get you squared away.
0
 

Author Comment

by:bill201
ID: 40514467
thanks a lot for your comment

there is not a relationship between the two tables.

i am attaching a sample from the database to explain the situation, when you delete a record on query1 the record will be delete from both tables why is that, and is there a way to avoid it and only the record from  table "product2t" will be deleted?

thanks a lot test2.mdb
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40514519
The only way that you can avoid having the records deleted from both tables is to not show the fields from the table you want to preserve, and set the recordset type of the query to Dynaset (Inconsistent Updates).   So, in the example database you provided, if you open Query1 and remove the productt.price column from the SELECT portion of the SQL statement, and change the recordset type, then deleting rows from the query will only delete from the Product2T table.  

Basically, any fields you put in the SELECT portion are going to impact the tables they are based on, however, you can use the other tables in the WHERE clause to filter data and those fields will not cause deletion from the underlying table.    

The bottom line is that there is probably another way to do what you are trying to accomplish.  If you want to provide some more details on what it is you are trying to do, perhaps we can come up with a better solution.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 40514694
how are you displaying the data, single record form, continuous form, datasheet?

I generally set the forms AllowDeletions property to false and have a "Delete Record" button in the form footer on those forms where I want the user to be able to delete records.  Then, in the Click event of that button, I execute a SQL statement that deletes the current record based upon in PK of the record.
Private Sub cmd_Delete_Click

    Dim strSQL as string

    On Error Goto ProcError

    strSQL = "DELETE FROM table1 WHERE [PK_Field] = " & me.txt_PKID
    currentdb.execute strsql, dbfailonerror

ProcExit:
    Exit Sub
ProcError:
    msgbox err.number & vbcrlf & err.description, , "Deletion error"
    debug.print err.number, err.Description
    debug.print strSQL
End Sub

Open in new window

0
 

Author Comment

by:bill201
ID: 40514769
Robert Sherman

thanks a lot for your answer. You're right, I already have a better idea how to do it.

But can you please show me  ( just for knowledge reasons :) ) how can i change on access query screen the recordset type,    and is there no way to relate on a query a two tables which one is update-able  and the other isn't  ?

Dale Fye

thanks for your comment, i think it's a An Interesting and Careful approach, but by my self I'm a bit too lazy to write so much code :) and i try to avoid them when i can.
0
 
LVL 7

Accepted Solution

by:
Robert Sherman earned 250 total points
ID: 40514930
in the query Design View, you can right click on the background in the upper section where your tables are and select "properties", this will bring up the Query Properties, you will find the recordset type in there.

Basically, in Access it comes down to a user interface issue -- if you see the data in the row of the query, and you select delete, the standard datasheet view pretty much assumes you want to delete what you are seeing, which is records from multiple tables..  

There's lots of ways to get around this, but not without doing some custom programming to handle specifically what you want to do, for example the way Dale Fye mentioned.  

Perhaps what you were looking to do could be accomplished with a lookup field?  If all you were looking for is a particular description from the 2nd table, you could change the field in the 1st table to be a lookup field or dropdown.  Then you would not need to pull in the second table in the query.  (Not sure that is what you were looking for, but perhaps..)
0
 

Author Closing Comment

by:bill201
ID: 40515830
thanks a lot
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question