Solved

on qeury access 2013

Posted on 2014-12-22
7
397 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
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.

 
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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

828 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