How to edit a table accessed thru an Inner Join SQL

  • Thread starter Thread starter Mota
  • Start date Start date
M

Mota

Hello;
I have to Edit and Update a Table,accessed thru an Inner Join SQL (If it
helps,no matter inner join or left join or right join,for two tables are
joined by a one-to-one relationship havin referrential integrity for both
update and delete).My code is this:

Dim DB As Database, Rs As DAO.Recordset, StrSql As String
Set DB = DBEngine(0)(0)
StrSql = "select drugsTBL.*,OrdersTBL.* from drugsTBL inner join OrdersTBL "
StrSql = StrSql & "on DrugsTBL.DId=OrdersTBL.DId where DrugsTBL.Did=5"
Set Rs = DB.OpenRecordset(StrSql, 2)
With Rs
..Edit
!Order = "Take one Each 6 Hours"
..Update ' The line raises Error "Can not update.DB Object is ReadOnly"
End With
Rs.Close

For some reasons,i need to keep it as a Sql statement, and avoid from
converting it to a stored query.
Is there a trick to do such an Edit?
Thank you in advance.
 
Hello;
I have to Edit and Update a Table,accessed thru an Inner Join SQL (If it
helps,no matter inner join or left join or right join,for two tables are
joined by a one-to-one relationship havin referrential integrity for both
update and delete).
Click to expand...

Ummm... WHY do you have to do it through the join? For that matter,
why do you have a one to one anyway? They are VERY uncommon, since you
can just use one table with all the fields. A one to one relationship
between drugsTBL and ordersTBL suggests that every drug will be
ordered once, and only once, for the entire life of the project. Is
that the case?
My code is this:
Click to expand...

What's the context? Where do you call this code from? How does the
code know that DId 5 is the drug that you want to edit? Is DId the
Primary Key of both tables?
Dim DB As Database, Rs As DAO.Recordset, StrSql As String
Set DB = DBEngine(0)(0)
StrSql = "select drugsTBL.*,OrdersTBL.* from drugsTBL inner join OrdersTBL "
StrSql = StrSql & "on DrugsTBL.DId=OrdersTBL.DId where DrugsTBL.Did=5"
Set Rs = DB.OpenRecordset(StrSql, 2)
With Rs
.Edit
!Order = "Take one Each 6 Hours"
.Update ' The line raises Error "Can not update.DB Object is ReadOnly"
End With
Rs.Close

For some reasons,i need to keep it as a Sql statement, and avoid from
converting it to a stored query.
Click to expand...

If there is a unique Index on DId in both tables; and if both tables
are local or linked Access tables; if your security privileges are
such that you can edit the tables; and given that acOpenDynaset is
equal to 2 (you might want to consider using the named constant), it
should work. However, since you only create this recordset to edit one
field in one table, why not set strSQL to

"SELECT [OrdersTBL].[Order] FROM [OrdersTbl] WHERE DId = 5;"

You don't need the other fields or the other table!
 
Dear Andy;
Thank you for ur attention
1-Yes,it gives me back the selected record from two joined tables.
2-No,a query w/ this SQL property is not updatable too,while this query is
the record source for Rs Variable.
3-Im wonderin why you,as a proffessional,say that.or at least,i cant
understand u well.Because dbOpenDynaset is an intrinsic constant for 2.No
difference which one to use.Basically,the thing that is not updatable is the
joining query itself (StrSql),not its container(RecordSet Variable).and im
looking for a Trick to update it.For example,an Update Sql Statement that
can select this record from both joined tables.Is it possible?Any
suggestions?
Thank you.
 
Thank you John.You solved my problem.
But the thing was interesting in ur reply is:
The OrdersTBL is not the Order Table u think,as in NorthWind sample DB!!!
As a pharmacist,when we say Order,it reffers to a drug order,such as for
example "Take 1 tablet each 8 Hours".and since a drug has just one DEFAULT
order in my present design,its relationship wd be a 1-to-1.we can edit this
order in its relative bound txtBox before printing,if necessary.I accept,it
was my fault to use this word here.
Since a drug have many properties,if i want to put all in 1 table,it might
have more than 20 fields.So i splitted DrugsTBL.Do u recommend using such a
big table is more efficient than 1 to1 relations?If yes,i will join and
simplify 3 tables.(Im waiting for ur answer on this topic)
And since i want to edit more than 1 field from two tables,ur suggested SQL
is not much useful for me.However,in a complicated code,i can edit both
tables separately,if needed.
The problem was that the OrdersTBL hadnt a PK on its dID field.In the
future,we may want to consider 2 default order for a drug,selectable from a
Combo.For this reason i hadnt set a PK on its dID.Anyway,thx for ur
help,especially for this sentence:"If there is a unique Index on DId in BOTH
tables".

John Vinson said:
Hello;
I have to Edit and Update a Table,accessed thru an Inner Join SQL (If it
helps,no matter inner join or left join or right join,for two tables are
joined by a one-to-one relationship havin referrential integrity for both
update and delete).
Click to expand...

Ummm... WHY do you have to do it through the join? For that matter,
why do you have a one to one anyway? They are VERY uncommon, since you
can just use one table with all the fields. A one to one relationship
between drugsTBL and ordersTBL suggests that every drug will be
ordered once, and only once, for the entire life of the project. Is
that the case?
My code is this:
Click to expand...

What's the context? Where do you call this code from? How does the
code know that DId 5 is the drug that you want to edit? Is DId the
Primary Key of both tables?
Dim DB As Database, Rs As DAO.Recordset, StrSql As String
Set DB = DBEngine(0)(0)
StrSql = "select drugsTBL.*,OrdersTBL.* from drugsTBL inner join OrdersTBL "
StrSql = StrSql & "on DrugsTBL.DId=OrdersTBL.DId where DrugsTBL.Did=5"
Set Rs = DB.OpenRecordset(StrSql, 2)
With Rs
.Edit
!Order = "Take one Each 6 Hours"
.Update ' The line raises Error "Can not update.DB Object is ReadOnly"
End With
Rs.Close

For some reasons,i need to keep it as a Sql statement, and avoid from
converting it to a stored query.
Click to expand...

If there is a unique Index on DId in both tables; and if both tables
are local or linked Access tables; if your security privileges are
such that you can edit the tables; and given that acOpenDynaset is
equal to 2 (you might want to consider using the named constant), it
should work. However, since you only create this recordset to edit one
field in one table, why not set strSQL to

"SELECT [OrdersTBL].[Order] FROM [OrdersTbl] WHERE DId = 5;"

You don't need the other fields or the other table!
Click to expand...
 
Thank you John.You solved my problem.
But the thing was interesting in ur reply is:
The OrdersTBL is not the Order Table u think,as in NorthWind sample DB!!!
As a pharmacist,when we say Order,it reffers to a drug order,such as for
example "Take 1 tablet each 8 Hours".and since a drug has just one DEFAULT
order in my present design,its relationship wd be a 1-to-1.we can edit this
order in its relative bound txtBox before printing,if necessary.I accept,it
was my fault to use this word here.
Click to expand...

Ah. I understand. But you could have an [Order] field in the table,
and use VBA code to default it to the normal [Order] for that drug
(from the Drug table), and allow the user to override that default in
(what I'd call) the Prescription table... no?
Since a drug have many properties,if i want to put all in 1 table,it might
have more than 20 fields.So i splitted DrugsTBL.Do u recommend using such a
big table is more efficient than 1 to1 relations?If yes,i will join and
simplify 3 tables.(Im waiting for ur answer on this topic)
Click to expand...

20 fields isn't very big. 60 is big. One to one tables are rather
uncommon, and since Access must open two recordsets and two indexes
and do a join, will always be less efficient in terms of performance
than a single table - but it may nonetheless be worth that price. You
might use one if some of these properties apply to very few drugs and
would be NULL in most records.
And since i want to edit more than 1 field from two tables,ur suggested SQL
is not much useful for me.However,in a complicated code,i can edit both
tables separately,if needed.
The problem was that the OrdersTBL hadnt a PK on its dID field.
Click to expand...

Ah. I understood that it did. Glad I put in that constraint!
 
Back
Top