.MDB DRI different from .MDF?? (1 Viewer)

Still on the learning curve .mdb link to SQL

Situation
1. A Query is created with fields from 2 primary keyed tables in BE .mdb. These 2 tables have 1:many DRI. Deletion of a record in this query is allowed as it only deletes a record on the many side.
2. These 2 tables were earlier upsized to an SQLEXPRESS .mdf which are similarly keyed with 1:many DRI.
3. Both DRI has no cascading updates just plain enforce RI.
4. A View modeled against the .mdb Query is created from these 2 tables in the .mdf
5. When i try to delete a record from the View, SQLX rejects stating that 'modification affects multiple base tables'. Essentially i think the deletion is also deleting the key field in the 1 side of the link which is not allowed as it will cause orphans.

Question
1. Why is the deletion possible within .mdb & not in VIEW of .mdf?
What did i missed?
2. Some mention that 'Views are not for deletes only selects' use 'either use query analyzer (if not permanent) or create a stored proc'. I don't understand this.
3. What is the best approach?

Truly need the gurus to lead the way.
Thanks in Advance.

Hi,

Views can be updatable but only if the following is true: (taken from BOL)


I personally only use views for select statements and use stored procedures for inserts, updates and deletes. This is common practice because stored procedures can have input and output parameters which most data manipulation tasks require for dynamic criteria.


You can continue with the view methos if you like but I think it would be quicker and easier to make a stored procedure.

Thanks mate for the quote & views. If i remember correctly you are the 'some' I mentioned in #1 above.

By the way, as u are a SQL DBAdmin while i am just a novice into SQLSrv, hope you can enlighten me on one aspect of my imaginary plan in the conversion of a working FE .mdb originally linked to a BE .mdb to BE SQLSrv. For yr info, my series of threads follows Pat Hartman's advice to use .mdb instead of .adp as FE which i believe to be correct because i found real hardship in linking to an updateable .mdb.

According to Pat, all the FE forms should use queries as RecordSource RS; I presume he meant Access queries created from linked SQL tables within the FE .mdb. Meanwhile i was tinkering with the use of linked updateable Views created in the SQL from its tables as RS.

Questions:
1 From the BookOL it appears that that if there is MORE THAN 1 BASE TABLE in an updatable View, it is really not updateable at all? Therefore is it correct that i should avoid using linked updateble Views as RS in my forms?
2 You suggest to use SProc; can i use SP in my FE.mdb as RS? If so just a tiny pointer to how to leap into it.
3. Or should I just stick to Access queries as RS? What really prompt me to consider VIEW is that I found DRI issues in updating the FE forms that have more than 1 base tables. Alternatively it will require more codes eg ADO RecordSet to manage the DRI issues.

Thanks again & Kind Regards. Michael

Hi again,

Sorry for the delay mate, been rather busy today!

1. Yes
2. I suggest using SP's for updates deletes ect, but for access form Record Source then I know I have had problems using stored procs in the past (this is probably the only exception to the rule of stored procs btw)

just use a string of sql like
"select * from dbo.products inner join dbo.objects blah blah blah"

ensuring you have a primary key in your table and your sql server tables are linked.

3. when you say access queries do you mean like I have suggested in question 2? if so yes they get parsed through to sql server and processed at sql server end (so no bad access performance).

Thanks again SQL ; No sweat, i only get into this forum daily.

As I read, yr item 2, code snaps & 'your sql server tables are linked' may be slightly different from an Access Queries or it may be same

The RS string i use is as follows
SELECT dbo_Cust.*, dbo_GL0.AcNum FROM dbo_GL0 INNER JOIN dbo_vCust ON dbo_GL0.AcNum=dbo_Cust.CustID;

wherein the dbo_xxx are ODBC SQL linked names of tables dbo.xxx in .mdf.

With the above do you still think my Access Queries are what you would use in the RS of a form.


The 'bad access performance' really goes like this
1 I have a subform within the main form using the above Query.
2 The RS for the subform is
SELECT dbo_GLTran.*, dbo_Batch.BName FROM dbo_Batch INNER
JOIN dbo_GLTran ON dbo_Batch.BthANo=dbo_GLTran.BthANo
WHERE ...

3 The subform link Child field is dbo_GLO.AcNum link to the same in the Main; (linked table dbo_GLTran also have the a AcNum field)

4 DRI links in the SQLSrv are
4.1. dbo.GL0.AcNum = dbo.Cust.CustID
4.2 dbo.Batch.BthANo = dbo.GLTran.BthANo
4.3 dbo.GLTran.AcNum = dbo.GL0.AcNum


The 'bad' event occurs when there is a child record in the subform, SQL rightly forbids the deletion of the of the Main record due to the 4.3 DRI.

The sad part is it will also delete from the Main, the dbo.Cust portion leaving the dbo.GLO part intact. In normal RS of Jet, it will not do this sad part.

Any pointers??

Just read the thread .. interesting experiment started by Banana on Jun 27 2008 with the comments from Brent Spaulding & Pat Hartman; it is very interesting for a novice to read. I gives me ideas about the options for queries that i never thought about as yet.

Period: Access responses differently to SQL DRI

Found the solution to my problem after some tinkering.

By having an extra DRI (4.4) on the other half of the join in the Main form, the bad deletion is stopped ie it will forbid deletion of the Cust record in the join.

4.4 dbo.GLTran.AcNum = dbo.Cust.CustID.

In linked .mdb, 4.4 is not required.