If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Table vs. multiple tables
On Dec 21, 3:09 pm, "Pat Hartman" please no wrote:
Perhaps you are getting me confused with someone else... the only time you would create a "third" table to hold the relationship is if the relationship were m:m. That's not what I said. I said in this thread, "The relationships [in Northwind] between entities order:customer and order:employee should (IMO) be modelled using distinct relationship tables." I'm really confused that you would seemingly try to tell me I do something that I explicitly said I would not. We had a discussion regarding one not too long ago. I don't remember the details but it had something to do with someone wanting to implement a "cascade to null" action with Access. Since this feature is not supported, I suggested using a junction table which would allow the relationship to be deleted as a result of cascading the delete of the one side row. I don't think it could have been with me because I rarely have nullable columns at all and I have never used the ON DELETE SET NULL referential action BTW this is supported in Jet 4.0 (and above) but not ON UPDATE SET NULL despite what the Access Help says. Jamie. -- |
#12
|
|||
|
|||
Table vs. multiple tables
Jamie Collins wrote:
On Dec 21, 1:01 am, "Tony Toews [MVP]" wrote: Whereas I don't have a clue about entities and attributes and all that other fancy, schmancy terminology and definitions. I think in terms of tables and relationships. You are probably being flippant for comic effect but I'll make the obvious point anyway... Only somewhat. I have no format training in databases or computers or software. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#13
|
|||
|
|||
Table vs. multiple tables
On Dec 21, 3:09 pm, "Pat Hartman" please no wrote:
I don't remember the details but it had something to do with someone wanting to implement a "cascade to null" action with Access. Since this feature is not supported Just to clarify: the SQL-92 standard, to which Jet 'aspires', supports four referential actions: CASCADE | SET NULL | SET DEFAULT | NO ACTION Jet 4.0 (and above) supports CASCADE, SET NULL and NO ACTION, though it supports SET NULL for the delete rule only. Please clarify what you mean by, "a 'cascade to null' action... is not supported." TIA. Jamie. -- |
#14
|
|||
|
|||
Table vs. multiple tables
On Dec 22, 5:59 pm, "Tony Toews [MVP]" wrote:
I have no format training in databases or computers or software. You certainly do not require any to be able to express opinions in this public Usenet discussion group. Jamie. -- |
#15
|
|||
|
|||
Table vs. multiple tables
Thanks. I stand corrected on that. I rarely use DDL to create or modify
table objects so I forget the things that the GUI does not support. I'm going to add "updating the GUI to reflect current Jet options" to the wish list I send to the Access team every year. "Jamie Collins" wrote in message ... On Dec 21, 3:09 pm, "Pat Hartman" please no wrote: I don't remember the details but it had something to do with someone wanting to implement a "cascade to null" action with Access. Since this feature is not supported Just to clarify: the SQL-92 standard, to which Jet 'aspires', supports four referential actions: CASCADE | SET NULL | SET DEFAULT | NO ACTION Jet 4.0 (and above) supports CASCADE, SET NULL and NO ACTION, though it supports SET NULL for the delete rule only. Please clarify what you mean by, "a 'cascade to null' action... is not supported." TIA. Jamie. -- |
#16
|
|||
|
|||
Table vs. multiple tables
On Dec 26 2007, 3:40 pm, "Pat Hartman" please no
wrote: I rarely use DDL to create or modify table objects so I forget the things that the GUI does not support. I'm going to add "updating the GUI to reflect current Jet options" to the wish list I send to the Access team every year. I guess there will always be advanced features and settings only accessible via code but, sure, there are glaring omissions in the Access UI. Your wish is a good one but a little non-specific for me. I'd request you make a specific proposal e.g. for the exposure of Jet's CHECK constraints in the UI, which would have a positive knock on effect for the Query Builder Tool Thing as they'd need to expand it to be able to generate subqueries and should also enhance our understanding of Jet CHECK constraints (to date Microsoft have published only scant information). If non-specific wishes are the norm (I really don't know how these things work, what with the MVP program being non-transparent) then something more achievable IMHO for the Access team would be to address the impedance between DAO and Jet (again, as an example, CHECK constraints are not exposed via DAO). Perhaps someone reading this with an Access-related website will be inspired to create some handy reference lists of Jet features not exposed in the Access UI and DAO respectively. PS my wish would be to get more details about the features already present in the engine e.g. the equivalent of the below article for Jet's DECIMAL data type (this kind of stuff must be documented somewhere, surely we have a right to know g?!): Precision, Scale, and Length http://msdn2.microsoft.com/en-us/lib...4(SQL.80).aspx PPS just think of the features and settings that must exist in the Jet engine but are not exposed at all (DAO, DDL, OLE DB provider, registry key) by accident or design ;-) Jamie. -- |
#17
|
|||
|
|||
Table vs. multiple tables
I was just generalizing here. When I send the list, I will provide specific
examples. The Jet Programmer's Guide used to provide a lot of useful information but it hasn't been updated since Jet 3.5 and so is somewhat out of date. You might want to contact the author/publisher to see if you can repubulish it if you have any interest in writing. There is lots of new stuff to include now that the Access team has taken over Jet (now called ACE?) from the SQL Server team. If you have the time and inclination to create the cross-reference, I can get it into the hands of the Access team for review. "Jamie Collins" wrote in message ... On Dec 26 2007, 3:40 pm, "Pat Hartman" please no wrote: I rarely use DDL to create or modify table objects so I forget the things that the GUI does not support. I'm going to add "updating the GUI to reflect current Jet options" to the wish list I send to the Access team every year. I guess there will always be advanced features and settings only accessible via code but, sure, there are glaring omissions in the Access UI. Your wish is a good one but a little non-specific for me. I'd request you make a specific proposal e.g. for the exposure of Jet's CHECK constraints in the UI, which would have a positive knock on effect for the Query Builder Tool Thing as they'd need to expand it to be able to generate subqueries and should also enhance our understanding of Jet CHECK constraints (to date Microsoft have published only scant information). If non-specific wishes are the norm (I really don't know how these things work, what with the MVP program being non-transparent) then something more achievable IMHO for the Access team would be to address the impedance between DAO and Jet (again, as an example, CHECK constraints are not exposed via DAO). Perhaps someone reading this with an Access-related website will be inspired to create some handy reference lists of Jet features not exposed in the Access UI and DAO respectively. PS my wish would be to get more details about the features already present in the engine e.g. the equivalent of the below article for Jet's DECIMAL data type (this kind of stuff must be documented somewhere, surely we have a right to know g?!): Precision, Scale, and Length http://msdn2.microsoft.com/en-us/lib...4(SQL.80).aspx PPS just think of the features and settings that must exist in the Jet engine but are not exposed at all (DAO, DDL, OLE DB provider, registry key) by accident or design ;-) Jamie. -- |
#18
|
|||
|
|||
Table vs. multiple tables
On Jan 3, 8:10 pm, "Pat Hartman" please no wrote:
The Jet Programmer's Guide used to provide a lot of useful information but it hasn't been updated since Jet 3.5 and so is somewhat out of date. You might want to contact the author/publisher to see if you can repubulish it if you have any interest in writing. There is lots of new stuff to include now that the Access team has taken over Jet (now called ACE?) from the SQL Server team. It's a fantastic idea but the underlying problem is catch-22 in natu where do I get the information on which to write the revision? Let's take a first step and say I was interested in publishing an article on Jet's DECIMAL data type, introduced in Jet 4.0. Jet is a Window component 'owned' by the SQL Server team (IIRC the Access take have merely been granted a private branch of the code). [Aside: yes, 'ACE' is the accepted term in these groups and the general term 'Jet' is taken to encompass ACE]. From the aforementioned SQL Server 2000 article I note, "In previous versions of SQL Server, the default maximum was 28."From the few details Microsoft has given us about Jet's DECIMAL data type we know that it has a maximum precision of 28; from experience I've noted that the default precision is often 28 e.g. ? CurrentProject.Connection.Execute("SELECT 0.5;")(0).Precision 28 So I would speculate that a good starting point would be the equivalent SQL Server 7.0 article; a quick Google suggest such a document did exist because I found a broken URL. [Aside: the chapters from the Jet Database Engine Programmer's Guide, which I believe is the book you referred to, I found on Microsoft's site are classed as "SQL Server 7.0 Product Documentation", which may just be a coincidence (http://www.microsoft.com/technet/prodtechnol/ sql/70/proddocs/msjet/jetch04.mspx).] I could then test behaviour in Jet against the SQL Server 7.0 article... but I foresee problems. If I experience an inconsistency, how do I know whether it is a bug or behaviour by design? (assuming I was able to ascertain that the behaviour was in fact Jet rather than another component) Surely, I first need to know what the designer's intention was i.e. a spec? Allen Browne once wrote of sorting a column of DECIMAL values: "Nulls and zeros sort unpredictably - at the beginning, middle or end, depending on the data." (http://allenbrowne.com/bug-08.html). Microsoft have told us that Jet's NULL collation is "that null values are sorted at the low end of the list" (http://msdn2.microsoft.com/en- us/library/aa140022(office.10).aspx), based on the reasonable assumption that the OLE DB provider reflects its underlying engine. I cannot reproduce a simple sort where NULLs in a DECIMAL column sort anywhere other than and the end of a resultset, even when using the mdb file supplied by the author. I think if NULL collation was broken in Jet 4.0 then it would be significant enough to warrant its own MSDN article. I've nothing to suggest the NULL collation is broken other than the anecdotal evidence above i.e. no description of the steps to reproduce such a scenario. In case you think I've gone OT let me make my point: observation alone doesn't amount to proof. That is why IMO the onus is on Microsoft to provide such information rather than for me to discover their designs by trial and error. Jamie. -- |
|
Thread Tools | |
Display Modes | |
|
|