time serial question
Tammy
value in my table? Is this just a calculation? It's only working my from
OpenedTime field. I want it to store the info in ExpectedResponseTime.. How
do I make it store in the ExpectedResponseTime field?
=TimeSerial(Hour([OpenedTime])+1,Minute([OpenedTime]),Second([OpenedTime]))
Thanks for any advice.
Tammy
Tammy
=TimeSerial(Hour([OpenedTime])+1,Minute([OpenedTime]),Second([OpenedTime]))
Douglas J. Steele
[OpenedTime]?
Simply create a query with a computed field in it, and use the query
wherever you would otherwise have used the table.
For what it's worth, you can get by with simply DateAdd("h", 1,
[OpenedTime]), thus saving a few function calls.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Tammy" <Ta...@discussions.microsoft.com> wrote in message
news:85A9F260-2F73-4CD6...@microsoft.com...
Tom Wickerath
You should avoid storing the results of any calculations. Doing so violates
the rules of database normalization. Here are two quotes for you to consider
on this topic:
http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")
<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>
As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Tom Wickerath
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Tammy
hour greater than OpenedTime ??
thanks, Tammy
John Vinson
<Ta...@discussions.microsoft.com> wrote:
>could you tell me how to solve my problem? I need an ExpectedResponseTime 1
>hour greater than OpenedTime ??
They did. Did you read their answers?
create a Query based on your table.
In a vacant Field cell in the query type
ExpectedResponseTime: DateAdd("h", 1, [OpenedTime])
Use this Query as the source for a form, report, export, etc.
Unless there's some issue that you haven't told us, there is *no* need
or justification for storing ExpectedResponseTime in the Table.
John W. Vinson[MVP]
Tom Wickerath
Use the DateAdd function in the control source for the ExpectedResponseTime
textbox, ie:
=DateAdd("h",1,[OpenedTime])
The point is that you should not attempt to store the results of this
calculation in your table. Calculate it on-the-fly, as needed, in a query, or
in the control source for a textbox on a form or report.
Here are some references that you might want to bookmark:
http://office.microsoft.com/en-us/assistance/HA010546621033.aspx
http://support.microsoft.com/?id=210276
Tammy
store in my table? The field name is different from the label name. Does
that make sense?
I typed it like this ExpRespMilTimes=DateAdd("h",1,[OpenedTime]) - with
ExpRespMilTimes being the field I want it stored in but it didn't work. If I
just type in a text box control property =DateAdd("h",1,[OpenedTime]) it
doesn't store anywhere.
Tom Wickerath
> If I just type in a text box control property =DateAdd("h",1,[OpenedTime])
> it doesn't store anywhere.
That's what I've been saying all along. You should not be attempting to
store the results of any calculation. Did you read my initial answer to your
second posting?
Jamie Collins
Tom Wickerath wrote:
> As fellow Access MVP John Vinson likes to say "Storing calculated data
> generally accomplishes only three things: it wastes disk space, it wastes
> time (a disk fetch is much slower than almost any reasonable calculation),
> and it risks data validity, since once it's stored in a table either the
> Total or one of the fields that goes into the total may be changed, making
> the value WRONG."
John Vinson and yourself oversimplify the point, I feel. See:
Calculated Columns by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko4/view?searchterm=celko
" You are not supposed to put a calculated column in a table in a pure
SQL database. And as the guardian of pure SQL, I should oppose this
practice. Too bad the real world is not as nice as the theoretical
world.
"There are many types of calculated columns. The first are columns
which derive their values from outside the database itself....This type
of calculated column is fine and presents no problems for the database.
" The second type is values calculated from columns in the same row...
There is truly no reason for doing this today; it is much faster to
re-calculate the data than it is to read the results from secondary
storage.
"The third type of calculated data uses data in the same table, but not
always in the same row in which it will appear. The fourth type uses
data in the same database.
"These last two types are used when the cost of the calculation is
higher than the cost of a simple read."
Jamie.
--
Tom Wickerath
You're certainly entitled to your opinion, however, consider the following
facts:
1.) First, Joe Celko agrees that he "should oppose this practice."
2.) "The second type is values calculated from columns in the same row...
There is truly no reason for doing this today; it is much faster to
re-calculate the data than it is to read the results from secondary
storage."
This is *exactly* the situation that the OP was dealing with.
3.) Joe's column goes on to discuss Triggers in the next section. As you
likely know, JET does not support triggers. So, you lose that safety valve
for updating a stored value if a dependent value is changed in the table.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Jamie Collins
> Joe's column goes on to discuss Triggers in the next section. As you
> likely know, JET does not support triggers. So, you lose that safety valve
> for updating a stored value if a dependent value is changed in the table.
If a 'safety value' is what's required, you can write a CHECK
constraint to prevent modifications that would invalidate the trend
values; I'm sure Celko would approve because CHECK constraints are in
the ANSI SQL-92 standard.
[You seem to have missed the fact that Celko derides the use of
triggers e.g. "Did your trigger change the trend in the 2000 April 03
row or not? If I drop a row, does your trigger change the trend in the
affected rows? Probably not."]
To use the example scenario in the Celko article, I think this CHECK
will do the job:
ALTER TABLE StockHistory ADD
CONSTRAINT safety_valve
CHECK (
NOT EXISTS (
SELECT *
FROM StockHistory AS S2,
StockHistory
WHERE S2.stock_id = StockHistory.stock_id
AND S2.sale_date < StockHistory.sale_date
AND S2.sale_date =
(
SELECT MAX(S4.sale_date)
FROM StockHistory AS S4
WHERE S4.stock_id = StockHistory.stock_id
AND S4.sale_date < StockHistory.sale_date
)
AND StockHistory.trend <> SWITCH(
StockHistory.price = S2.price, 0,
StockHistory.price < S2.price, -1,
StockHistory.price > S2.price, 1
)
)
);
To demonstrate that this can be implemented in Jet, here's my demo
code:
Sub Celko_calculated_columns()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe1.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE StockHistory ( stock_id CHAR(5)" & _
" NOT NULL, sale_date DATETIME DEFAULT DATE()" & _
" NOT NULL, price DECIMAL(10,4) NOT NULL," & _
" trend INTEGER DEFAULT 0 NOT NULL, CHECK(trend" & _
" IN(-1, 0, 1)), PRIMARY KEY (stock_id, sale_date)" & _
" );"
.Execute _
"ALTER TABLE StockHistory ADD CONSTRAINT" & _
" safety_valve CHECK (NOT EXISTS ( SELECT" & _
" * FROM StockHistory AS S2, StockHistory" & _
" WHERE S2.stock_id = StockHistory.stock_id" & _
" AND S2.sale_date < StockHistory.sale_date" & _
" AND S2.sale_date = ( SELECT MAX(S4.sale_date)" & _
" FROM StockHistory AS S4 WHERE S4.stock_id" & _
" = StockHistory.stock_id AND S4.sale_date" & _
" < StockHistory.sale_date ) AND " & _
" StockHistory.trend <> SWITCH(" & _
" StockHistory.price = S2.price," & _
" 0, StockHistory.price < S2.price, -1," & _
" StockHistory.price > S2.price, 1))) "
.Execute _
"INSERT INTO StockHistory (stock_id, sale_date," & _
" price, trend) VALUES ('XXX', #2000-04-01#," & _
" 10.75, 0);"
.Execute _
"INSERT INTO StockHistory (stock_id, sale_date," & _
" price, trend) VALUES ('XXX', #2000-04-02#," & _
" 313.25, 1);"
.Execute _
"INSERT INTO StockHistory (stock_id, sale_date," & _
" price, trend) VALUES ('XXX', #2000-04-03#," & _
" 200.00, -1);"
' Attempt to DELETE row ('XXX', #2000-04-02#)
' should fail with the CHECK biting:
Dim errMsg
On Error Resume Next
.Execute _
"DELETE FROM StockHistory WHERE stock_id" & _
" = 'xxx' AND sale_date = #2000-04-02#"
errMsg = Err.Description
On Error GoTo 0
MsgBox _
"Attempt to DELETE row ('XXX', #2000-04-02#):" & _
" does CHECK bite?" & vbCr & vbCr & _
IIf(Len(errMsg) = 0, "(no error)", errMsg)
' Attempt to INSERT row ('XXX', #2000-04-04#,
' 999.99, 0) should fail with the CHECK biting:
errMsg = vbNullString
On Error Resume Next
.Execute _
"INSERT INTO StockHistory (stock_id, sale_date," & _
" price, trend) VALUES ('XXX', #2000-04-04#," & _
" 999.99, 0);"
errMsg = Err.Description
On Error GoTo 0
MsgBox _
"Attempt to INSERT row ('XXX', #2000-04-04#," & _
" 999.99, 0): does CHECK bite?" & vbCr & vbCr & _
IIf(Len(errMsg) = 0, "(no error)", errMsg)
' Attempt to INSERT row ('abc', #2000-04-01#,
' 55.55, 0) should succeed:
errMsg = vbNullString
On Error Resume Next
.Execute _
"INSERT INTO StockHistory (stock_id, sale_date," & _
" price, trend) VALUES ('abc', #2000-04-01#," & _
" 55.55, 0);"
errMsg = Err.Description
On Error GoTo 0
MsgBox _
"Attempt to INSERT row ('abc', #2000-04-01#," & _
" 55.55, 0): does CHECK bite?" & vbCr & vbCr & _
IIf(Len(errMsg) = 0, "(no error)", errMsg)
End With
Set .ActiveConnection = Nothing
End With
End Sub
Perhaps another CHECK constraint is required to ensure where only one
row exists for a stock_id that the trend is zero...
Jamie.
--
Tom Wickerath
I guess so, since I only skimmed the link that you provided; at this time, I
have not "read" the document. I only opened it up long enough to confirm your
quote, and my eye caught the next section on triggers.
Thank You for providing your sample code. Now answer this if you would. Does
your check constraint either prevent a user from changing a dependent value,
such as [OpenedTime] or cause the recalculation of [ExpectedResponseTime],
since you've indicated it's okay to store this value? I suspect that it will
not.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Jamie Collins
Tom Wickerath wrote:
> Thank You for providing your sample code. Now answer this if you would. Does
> your check constraint either prevent a user from changing a dependent value,
> such as [OpenedTime] or cause the recalculation of [ExpectedResponseTime],
> since you've indicated it's okay to store this value? I suspect that it will
> not.
My example related to the article, because Celko provdes more info then
the OP. To answer your question, a CHECK constraint would prevent a
user from changing a dependent value unless they *additionally* changed
the calculation.
Jamie.
--
Tom Wickerath
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Jamie Collins
Tom Wickerath wrote:
> > a CHECK constraint would prevent a
> > user from changing a dependent value unless they *additionally* changed
> > the calculation.
>
I did not. That would make no sense!
My point is, storing a calculation as well as the dependent values
could mean they get out of synch *unless* you write a constraint to
ensure this does not happen.
Perhaps the example I posted was too complex. Try this simplified one:
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col1_plus_col2 INTEGER NOT NULL
);
This is useless because I can do this:
INSERT INTO Test (col1, col2, col1_plus_col2)
VALUES (2, 2, 4);
UPDATE Test
SET col1 = 0;
The below revision should prevent the calculation getting out of synch
with its dependents:
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col1_plus_col2 INTEGER NOT NULL,
CHECK (col1_plus_col2 = col1 + col2)
);
INSERT INTO Test (col1, col2, col1_plus_col2)
VALUES (2, 2, 4);
UPDATE Test
SET col1 = 0;
-- the CHECK bites!
UPDATE Test
SET col1 = 0,
col1_plus_col2 = 2;
-- succeeds
Obviously, the above is an example of the type of calculation (i.e.
values of the same row) that everyone seems to agree has no place in a
SQL DBMS. However, the logic can be entended to other types of
calculated column i.e. that the calculation can become out of synch
with its dependents is no reason in itself for avoiding a calculated
column because a constraint can be written to prevent such a situation
from arising.
Jamie.
--
Tom Wickerath
> with its dependents:
CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col1_plus_col2 INTEGER NOT NULL,
CHECK (col1_plus_col2 = col1 + col2)
);
results in "Syntax error in field definition" when I try to run it using
Access 2003.
Also, your example shows a check constraint with a simple math operation,
addition. Can you make it work to satisfy Tammy's requirements? In other
words, can you build a workable check constraint that adds one hour to the
value entered into the [OpenedTime] field?
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Jamie Collins
Tom Wickerath wrote:
> CREATE TABLE Test (
> col1 INTEGER NOT NULL,
> col2 INTEGER NOT NULL,
> col1_plus_col2 INTEGER NOT NULL,
> CHECK (col1_plus_col2 = col1 + col2)
> );
>
> results in "Syntax error in field definition" when I try to run it using
> Access 2003.
Are you in ANSI query mode? If not, try executing the SQL against an
ADO connection.
I suspect you do not need to actually run the SQL to know what it is
supposed to achieve; I would struggle to find a simpler example to for
you to comprehend.
> your example shows a check constraint with a simple math operation,
> addition. Can you make it work to satisfy Tammy's requirements? In other
> words, can you build a workable check constraint that adds one hour to the
> value entered into the [OpenedTime] field?
The OP has already supplied the logic:
CHECK (ExpectedResponseTime = TimeSerial(
Hour([OpenedTime])+1,
Minute([OpenedTime]),
Second([OpenedTime]))
)
A CHECK constraint is analogous to an Access 2003 Validation Rule (but
is more powerful in that it can reference data in other rows and other
tables). Using the OP's example, if the OpenedTime was changed the
CHECK would not actively change the ExpectedResponseTime, as you seem
to be suggesting; rather, it would ensure than OpenedTime could not be
altered without simultaneously changing ExpectedResponseTime
accordingly. In other words, the CHECK, assuming it has been defined
correctly, would ensure the values did not get out of synch.
Jamie.
--
Tom Wickerath
I was not in ANSI query mode. Okay, I got that last one to work as soon as I
switched to ANSI 92 mode.
> I suspect you do not need to actually run the SQL to know what it is
> supposed to achieve; I would struggle to find a simpler example to for
> you to comprehend.
Umm...Jamie, you can lose that snooty attitude anytime! I'm simply trying
to follow the example you provided, and get it to work without errors. In
other words, I was open to learning something from you. I'll certainly be the
first to admit that using SQL DDL and using Check constraints are not
something that I have a lot of experience doing.
> The OP has already supplied the logic:
CHECK (ExpectedResponseTime = TimeSerial(
Hour([OpenedTime])+1,
Minute([OpenedTime]),
Second([OpenedTime]))
)
Okay, if one can use a built-in function (TimeSerial) then it seems logical
that they should be able to use the DateAdd function as well. It's not
immediately obvious to me that one can use a built-in function, because now
that I AM in ANSI-92 mode, I am getting a "Syntax error in field definition"
error when I attempt to run the following SQL statement. Can you suggest a
correction?
CREATE TABLE Test2 (
OpenedTime DATETIME NOT NULL,
ExpectedResponseTime DATETIME NOT NULL
CHECK (ExpectedResponseTime = TimeSerial(
Hour([OpenedTime])+1,
Minute([OpenedTime]),
Second([OpenedTime]))
);
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Tom Wickerath
CREATE TABLE Test3 (
OpenedTime DATETIME NOT NULL,
ExpectedResponseTime DATETIME NOT NULL,
CHECK (ExpectedResponseTime = DateAdd("h",1,[OpenedTime]))
);
A couple more questions for you, if you don't mind. How does one go about
deleting a table that includes a check constraint? I suspect that one must
run some type of SQL DDL statement to drop the constraint.
Do you happen to know if the rather ugly error message produced by violating
the check constrait is a trappable error that one can intercept and provide a
more 'user-friendly' error message for?
Tom Wickerath
DROP TABLE TableName;
is all it took to delete the table. Okay, but what if I just wanted to
modify or drop the check constraint, without deleting the table. Is that
possible?
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
RoyVidar
<E09D82A4-CAFA-4426...@microsoft.com>:
I think that if you add an unnamed check constraint, you'd probably
need
to fetch the name of it thrugh for instance openschema method
(adSchemaCheckConstraints -> will return something like
Check_32E994CD_345E_4FE8 when not named), then use that in a drop
constraint statement
ALTER TABLE MyTable
DROP CONSTRAINT NameOfMyConstraint
After dropping the constraint, you can delete the table through the
Access interface. I don't think ALTER CONSTRAINT is supported (but I
don't really know)
Perhaps more convenient, is to name the constraint
CREATE TABLE Test3 (
OpenedTime DATETIME NOT NULL,
ExpectedResponseTime DATETIME NOT NULL,
CONSTRAINT NameOfMyConstraint
CHECK (ExpectedResponseTime = DateAdd("h",1,[OpenedTime])));
Here's an article with some of the methods (watch linebreak in link).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
It's just a bit unusual, I think (or is it just me), to find Jet DDL,
which can't even be executed within the Access interface in the usual
operating mode, in a newsgroup dedicated to general Access issues.
Here's an interesting test. After creating the table through this DDL,
try using the Access interface to export/import it to another database.
--
Roy-Vidar
Tom Wickerath
Thank You for your input. I can see the value of naming a check constraint
when you create it.
> It's just a bit unusual, I think (or is it just me), to find Jet DDL,
> which can't even be executed within the Access interface in the usual
> operating mode, in a newsgroup dedicated to general Access issues.
It's not just you. I think anyone who post JET DDL that requires ANSI-92
mode should be very clear about stating this as a requirement.
> Here's an interesting test. After creating the table through this DDL,
> try using the Access interface to export/import it to another database.
At first, after reading your statement, I thought I may not even be able to
import the table. In which case, I would stay an arm's length distance away
from any check constraints. However, I see that I can import the table
without the constraint. That's better, but certainly not perfect. The other
thought that comes to mind is that if one relys on check constraints to allow
them to store the results of a calculation (as this thread started), then how
easy/difficult might it be to migrate such a BE database to a different
platform, such as SQL Server, MySQL, Oracle, DB2, etc? Are check constraints
a JET thing only?
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
"RoyVidar" wrote:
> I think that if you add an unnamed check constraint, you'd probably need
> to fetch the name of it through for instance openschema method
Tom Wickerath
--
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
RoyVidar
>> It's just a bit unusual, I think (or is it just me), to find Jet
>> DDL, which can't even be executed within the Access interface in the
>> usual operating mode, in a newsgroup dedicated to general Access
>> issues.
>
> It's not just you. I think anyone who post JET DDL that requires
> ANSI-92 mode should be very clear about stating this as a
> requirement.
No need for ANSI-92 mode, it's enough to fire it off on an ADO/OLE DB
connection.
>> Here's an interesting test. After creating the table through this
>> DDL, try using the Access interface to export/import it to another
>> database.
>
> At first, after reading your statement, I thought I may not even be
> able to import the table. In which case, I would stay an arm's
> length distance away from any check constraints. However, I see that
> I can import the table without the constraint. That's better, but
> certainly not perfect.
Agree - say you use a check constraint. Then in a couple of years,
there's a corruption issue, and some local maintenance crew, who you've
taught to handle such, in the process of deleting the old table, get a
message box saying "DDL cannot be completed on this table..." which
doesn't make much sence, since she's only hitting delete - it's
probably
just a stupid message caused by the corruption issue, no need to look
it
up in the documentation - but the mentioning of DDL gives an idea - ah
-
DROP TABLE worked, problem solved, case closed... ;-)
Who would expect a check constraint, I mean, we're talking "Access
database", dosn't it come with some validation thingies in stead ;-)
> The other thought that comes to mind is that
> if one relys on check constraints to allow them to store the results
> of a calculation (as this thread started), then how easy/difficult
> might it be to migrate such a BE database to a different platform,
> such as SQL Server, MySQL, Oracle, DB2, etc? Are check constraints
> a JET thing only?
As I understand, check constraints can be found on several/most
platforms, but Jet is among the more superior. As demonstrated in this
thread, Jet allows sub queries as part of check constraints. SQL
server,
as far as I've understood, does not. In a way, one might say that the
check constraints that are interesting to create in a Jet database,
needs to be handled with different tools on other platforms ;-)
--
Roy-Vidar
Jamie Collins
Tom Wickerath wrote:
> Do you happen to know if the rather ugly error message produced by violating
> the check constrait is a trappable error that one can intercept and provide a
> more 'user-friendly' error message for?
Perhaps the best way to trap the error is to use a WithEvents ADO
connection object because each event (e.g. _ExecuteComplete) is passed
an ADO Error object (which may be Nothing). Alternatively, you can use
the CHECK's meaningful name (I should have warned you not to leave it
to the engine to name your objects - sorry! <g>) i.e. test for its
presence in the VBA Err object's Err.Description:
(aircode)
Const CHECK_NAME As String = _
"earnings_history__no_overlapping_periods"
On Error Resume Next
con.Execute sqltext
If Err.Number <> 0 Then
If InStr(1, Err.Description, CHECK_NAME) > 0 Then
' Handle failed CHECK here.
End If
...
Of course, I would recommend implementing data validation in the front
end and use the above approach to catch an omission (i.e. bug).
Jamie.
--
Jamie Collins
> Umm...Jamie, you can lose that snooty attitude anytime! I'm simply trying
> to follow the example you provided, and get it to work without errors. In
> other words, I was open to learning something from you. I'll certainly be the
> first to admit that using SQL DDL and using Check constraints are not
> something that I have a lot of experience doing.
I admit I thought you were intentionally trying to misinterpret me e.g.
your earlier comment "I think you meant to say <<the complete
opposite>>". It is clear from your later comments this was not the case
and therefore I offer my sincerely apologies that my defensiveness
sounded snooty.
I hope you can understand why I would have assumed an Access MVP would
be familiar with CHECK constraints. The way I see it, there is simply
no other mechanism (e.g. triggers) available to write effective
database constraints.
Consider a table of employee's salary history (employee_nbr,
start_date, end_date, salary_amount) and the following business rules:
for the same employee
· no periods overlap (i.e. the table's primary key);
· all periods are contiguous;
· for two consecutive periods the salary amount is not the same.
Here is my proposed solution using CHECK constraints (long post):
http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc
How is the same achieved without CHECK constraints?
I think it is worth restating that in the above example, being a
standard 'history' table, the primary key cannot be implemented without
a CHECK constraint. The candidate keys are:
(employee_nbr, start_date)
(employee_nbr, end_date)
(employee_nbr, start_date, end_date)
yet none of these will prevent this duplication:
INSERT INTO EarningsHistory
(employee_nbr, start_date, end_date, salary_amount)
VALUES ('H1', #2001-01-01 00:00:00#, #2001-12-31 23:59:59#, 15000.00);
INSERT INTO EarningsHistory
(employee_nbr, start_date, end_date, salary_amount)
VALUES ('H1', #2001-12-01 00:00:00#, #2002-12-31 23:59:59#, 18000.00);
The above is duplication because during the month December 2001 the
employee would have two salary amounts, 15K and 18K respectively.
It is no exaggeration when I say that without table-level CHECK
constraints I would not use Jet; to not be able to enforce the primary
key in a history table would be untenable for me.
Jamie.
--
Jamie Collins
RoyVidar wrote:
RoyVidar wrote:
> say you use a check constraint. Then in a couple of years,
> there's a corruption issue
Say you omit the check constraint. How long before there's a data
integrity issue? How long before it's detected?
Jamie.
--
RoyVidar
<1154941323.6...@n13g2000cwa.googlegroups.com>:
Why should that suddenly start happening now when it has never happened
before?
--
Roy-Vidar
Jamie Collins
RoyVidar wrote:
> Why should that suddenly start happening now when it has never happened
> before?
Because there is no constraint to prevent it and someone made a typo, a
bug was introduced into the front end code, someone accidentally
changed a value without also changing the calculation, an Excel user
was experimenting using MSQuey, etc.
Or are you asking me, 'Why have database constraints...?'
Jamie.
--
RoyVidar
> RoyVidar wrote:
>> Why should that suddenly start happening now when it has never
>> happened before?
>
> Because there is no constraint to prevent it
Eh - so what you're saying is that the natural consequence of not using
a check constraint when working with Access, where said check
constraint
isn't even supported, break standard Access behaviour and runs at a
risk
of not being operative after maintenance, is that one does not use any
constraints or validation at all? Thanx <g>
--
Roy-Vidar
Jamie Collins
RoyVidar wrote:
> Eh - so what you're saying is that the natural consequence of not using
> a check constraint when working with Access, where said check
> constraint
> isn't even supported, break standard Access behaviour and runs at a
> risk
> of not being operative after maintenance, is that one does not use any
> constraints or validation at all? Thanx <g>
I'm not sure why you've misinterpreted my point but you have.
In case it was not clear, I'll restate it: the natural consequence of
not using a data integrity constraint when one is available is a
needless risk of loss of data integrity.
I have a problem with your counter-argument in that it appears to be
based on a number of 'what ifs' e.g. what if there is corruption, what
if the maintenance person is not operating ANSI query mode on a schema
known to contain CHECK constraints, what if the maintenance person is
unfamiliar with the schema, what if they fail to consult the
documentation, what if they ignore an error message about DDL when
attempting to delete the table, etc. But let's say all those bad things
do happen and the CHECK constraint accidentally gets 'lost'. I don't
see how 'it might get lost' is a viable argument for not having
something in the first place.
I don't know what you mean by 'standard Access behaviour'. ANSI query
mode is available; as you pointed out, an ADO connection can also be
used; for me, both are 'standard behaviour'.
Jamie.
--
RoyVidar
>
> I'm not sure why you've misinterpreted my point but you have.
>
> In case it was not clear, I'll restate it: the natural consequence of
> not using a data integrity constraint when one is available is a
> needless risk of loss of data integrity.
No, I think it's the other way around. You have misinterpreted me. This
is an Access NG. People come here seeking answers to Access questions.
You provide Jet suggestions, some which aren't even supported by
Access,
and may have consequences on standard behaviour of Access. So, in the
context of this being an Access NG, the point is that it isn't check
constraint vs no constraint, it is check constraint vs what can be
achieved within the context/interface of Access.
I feel that when working within the context of Access, a good design (a
good design would also mean no values are dependent on column(s) in the
same row), indices, validation rules, relationship and usage of
relevant
form events will provide sufficient validation to ensure data
integrity.
Some of the features of Jet that are not supported by Access are
interesting, but when working in an Access context, I'm not sure I find
them interesting enough that it'd be worth utilizing them. If one
hasn't
experienced any integrity issues when utilizing what Access offers, why
fix what ain't broken?
> I have a problem with your counter-argument in that it appears to be
> based on a number of 'what ifs' e.g. what if there is corruption,
> what if the maintenance person is not operating ANSI query mode on a
> schema known to contain CHECK constraints, what if the maintenance
> person is unfamiliar with the schema, what if they fail to consult
> the documentation, what if they ignore an error message about DDL
> when attempting to delete the table, etc. But let's say all those bad
> things do happen and the CHECK constraint accidentally gets 'lost'. I
> don't see how 'it might get lost' is a viable argument for not having
> something in the first place.
You seem to recognize a faint possibility of loosing the check
constraint. I think the danger of loosing validation is less when
working without Jet check constraints. So, again, if one hasn't
experienced any integrity issues when utilizing what Access offers, why
fix what ain't broken?
Anyway, how common is it for Access developers to operate in ANSI mode?
How common is it for Access developers to utilize check constraints?
> I don't know what you mean by 'standard Access behaviour'. ANSI query
> mode is available; as you pointed out, an ADO connection can also be
> used; for me, both are 'standard behaviour'.
Standard behaviour of Access? Out of the box? No ANSI mode, no non
supported Jet thingies, able to copy/paste tables at will, exporting/
importing tables between mdb files, both manually and through for
instance the TransferDatabase method of the DoCmd object (SaveAstText
is
perhaps stretching it a bit?), including definition, contents and
constraints/validation rules? He, he - setting the AllowZeroLength
property in table design view ;-)
--
Roy-Vidar
Jamie Collins
RoyVidar wrote:
> You provide Jet suggestions, some which aren't even
> standard behaviour of Access. So, in the context of this
> being an Access NG, the point is that it isn't check
> constraint vs no constraint, it is check constraint vs
> what can be achieved within the context/interface of
> Access.
You know you can create a CHECK constraint in the interface of Access
while in ANSI-92 mode. Therefore, check constraints *can* be achieved
within the context (e.g. ADO) and interface (e.g. ANSI-92 mode) of
Access.
If we only had Access 2000 and the Jet 4.0 goodies were only available
via ADO I can see it would be difficult to convince a long-time Access
user to utilize them. However, we have Access 2002 and Access 2003 with
ANSI-92 mode (previously known as 'ANSI query mode').
Which of my 'Jet suggestions' are not supported by Access while in
ANSI-92 mode? I can't think of any.
> how common is it for Access developers
> to operate in ANSI mode?
> How common is it for Access developers to utilize
> check constraints?
This is a bit 'chicken and egg'. In this thread we have an Access MVP
who was not familiar with CHECK constraints in any context (the SQL
standards, Jet, Access interface, etc).
If more people were aware of the benefits of ANSI-92 mode, even if it
was to use a single SQL DDL statement against an ADO connection in the
VBE Immediate Window as a one-off to create vital database constraint,
then I'm sure more people would use it.
What the Jet 4.0 features need is an Access MVP champion (clearly I
suck at this job myself <g>). We have Michel Walsh Vanderghast Access
MVP but someone more evangelical could get the message across.
What percentage Jet 4.0 users are Access users? If Jet 4.0 is not for
Access users then who?
> Standard behaviour of Access? Out of the
> box? No ANSI mode, no non supported
> Jet thingies,
Plenty of Access features are unavailable using out of the box settings
e.g. aren't VBA macros disabled by default? FWIW I get ANSI-92 mode out
of the box in Access2007; maybe the world is changing for the better
<g>.
Did you see the recent thread, 'What exactly is Microsoft Access
(seriously)?':
I think the conclusion is, Access is many things to many people.
Using Jet 4.0 features in ANSI-92 mode is perfectly legitimate
*supported* behaviour in Access; this is NOT 'under the hood' stuff,
I'm not cracking the mdb format with a hex editor <g>! It seems the
best we can do is agree to differ on the significance of 'standard'
behaviour.
> a good design would also mean no values are
> dependent on column(s) in the same row
That is a misstatement, presumably unintentional. Consider the salary
history example I posted above: start_date and end_date are parts of a
single atomic fact and end_date cannot occur before start_date; a good
design would have a validation rule or CHECK constraint to ensure this
could never occur in the database. This can be achieved using an Access
row-level Validation Rule (which the pre-Access2007 help confusingly
describes as 'table' level), which would presumably meet your
requirements for standard Access behaviour.
> setting the AllowZeroLength
> property in table design view ;-)
You picked a good example. I think many people who use this setting
would be surprised it will not prevent:
INSERT INTO (my_text_col) VALUES ('');
Of course, what is required is something at the engine level e.g. a
validation rule:
<> ''
or
CHECK (LEN(my_text_col) > 0)
Even if people do not use ANSI-92 mode they should be aware of its
existence. For example, a rule to allow only alpha characters:
NOT LIKE '*[A-Z]*'
However, if I used an ADO connection to the database, I could execute
this:
INSERT INTO Test (my_text_col) VALUES ('123');
it would succeed and my data integrity is gone.
To avoid this, I could execute a one-off SQL DDL statement against my
database:
ALTER TABLE Test ADD
CONSTRAINT test__my_text_col__alpha_only
CHECK (
text_col NOT LIKE '%[!A-Z]%'
AND text_col NOT LIKE '*[!A-Z]*'
);
Now my data integrity is secure until the day when the constraint is
dropped (how to make a schema immune to the tampering of well-meaning
yet ignorant maintenance staff is another matter).
> If one hasn't
> experienced any integrity issues when utilizing what Access offers, why
> fix what ain't broken?
I refer you again to my example salary history table, where the
'primary key' (i.e. the thing at the engine level that prevents
duplicates) can only be implemented via a (truly) table-level CHECK
constraint. Sure, the absence of a primary key does not in itself lead
to duplicates at some time in the future but there is a risk. On the
other hand, having a primary key guarantees there will never be
duplicates. Which would you recommend?
If you haven't experienced any integrity issues when defining your data
integrity constraints utilizing what Jet 3.51 and Access Forms offer,
the chances are you didn't ask someone like me to test it.
Put up defences now rather than wait for it to be broken for want of
defences later. Adding a belt won't break the braces.
Jamie.
--
Jamie Collins
Jamie Collins wrote:
> FWIW I get ANSI-92 mode out
> of the box in Access2007
>From the online Access 2003 help:
About ANSI SQL query mode (MDB)
http://office.microsoft.com/en-us/assistance/HP030704831033.aspx
"The ANSI SQL query mode default for new Access databases in 2002 -
2003 and 2000 file formats"
Although that doesn't read like a proper sentence and this help as a
whole is notorious for inaccuracies, it does suggest that ANSI-92 mode
is already the out of the box setting.
Jamie.
--
RoyVidar
When clicking the header you copy/pasted, you'll find which ANSI mode
is
default per the different file formats.
"ANSI-89 is the default setting for a new Access database in 2002 -
2003
and 2000 file format."
I thought ANSI-89 format denoted standard "Access dialect", and ANSI-92
what we've discussed?
I think this suggests ANSI-92 mode isn't an out of the box setting in
2002/2003. I don't know anything about 2007.
--
Roy-Vidar
Jamie Collins
RoyVidar wrote:
> I thought ANSI-89 format denoted standard "Access dialect", and ANSI-92
> what we've discussed?
Agreed, though I'd prefer 'traditional' (as per the Access 2003 help)
in place of your word 'standard' which has potentially misleading
connotations.
Apologies, the confusion is mine. I think I have been mistaking the use
of 'ANSI SQL query mode' to mean 'ANSI-92 mode'.
> I think this suggests ANSI-92 mode isn't an out of the box setting in
> 2002/2003.
I think you are correct. It's a confusing issue <g>.
Jet 4.0 SQL syntax, although including some full SQL-92 features (e.g.
cascade update on referential actions and subqueries in check
constraints are notable), doesn't fully comply with entry level SQL-92
(Jet's proprietary UPDATE syntax is notable here).
Jet 3.51 SQL syntax doesn't always resemble ANSI-89 syntax (OUTER JOIN
is notable here).
Jamie.
--
RoyVidar
> RoyVidar wrote:
>> I thought ANSI-89 format denoted standard "Access dialect", and
>> ANSI-92 what we've discussed?
>
> Agreed, though I'd prefer 'traditional' (as per the Access 2003 help)
> in place of your word 'standard' which has potentially misleading
> connotations.
Agree.
> Apologies, the confusion is mine. I think I have been mistaking the
> use of 'ANSI SQL query mode' to mean 'ANSI-92 mode'.
No, I should apologize, this was really a cheap shot from me, I've
"suffered" more or less the same confusion too, but couldn't resist
posting, sorry.
--
Roy-Vidar
RoyVidar
>
> You know you can create a CHECK constraint in the interface of Access
> while in ANSI-92 mode. Therefore, check constraints *can* be achieved
> within the context (e.g. ADO) and interface (e.g. ANSI-92 mode) of
> Access.
I wasn't aware of that. Where in the interface can you do that? Or do
you mean that the interface allows you to execute DDL to that effect,
in
stead of having to execute through ADO/OLE DB :-)
> If we only had Access 2000 and the Jet 4.0 goodies were only
> available via ADO I can see it would be difficult to convince a
> long-time Access user to utilize them. However, we have Access 2002
> and Access 2003 with ANSI-92 mode (previously known as 'ANSI query
> mode').
>
> Which of my 'Jet suggestions' are not supported by Access while in
> ANSI-92 mode? I can't think of any.
Swithching to ANSI-92 mode in an old Access app, in itself might answer
some of that question. This will break the "Access SQL dialect", but
whether or not that's a bad thing, belongs to another discussion ;-),
but it might also break existing queries. More "amusing", and unhealthy
for employment, is possible consequenses of executing queries doing
calculations, due to differences in execution between the modes.
Even in ANSI-92 mode, the behaviour when copying tables are different
when you're using the unsupported Jet feature check constraints than
when not. For those using such methods for maintenance, backup,
recovery, updates... this is broken when check constraints are
utilized.
I can't bring myself to call it supported by Access. Sure you can run
DDL, but in a visual/graphic interface like Access, I wouldn't call
such
settings supported unless you can find it somewhere in the UI and be
able to select it, alter or delete it. I haven't found such. Like, if
you fire off a check constraint on SQL server, then you will find that
constraint listed in the UI of most tools you use. For instance in SQL
Server Management Studio Express, it is a property/folder of the table,
in which you can add, alter and delete it through the UI (and, there's
a
wizard).
ADO is kind of "standard", and we were exposed to massive marketing
some
time ago, but one might question the success of this, since it seems
ADO
is frowned upon when working with Access/Jet, the preference is the
more
native DAO. In the following branch of a thread (where you also
participated elsethreads), some of these views and reservations are
expressed in relation to some ADO/DDL.
Then I'm going to aim a bit low. I've read several of your replies in
these NGs, and it seems some of the suggestions you've given, might
break some of the reasons for choosing Access. Here's one sample with a
"groan" ;-)
http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/df29dea99f93f956
If using check constraints, and implementing engine level validation
and
constraints means to toss bound forms overboard, then it also means
tossing Access overboard as frontend.
Bound forms are a core functionality or Access, it's what Access is
designed for, and what makes Access the brilliant RAD tool it is. If
going unbound is what one has to do to be able to use engine level
validation, then using these kind of constraints rules out Access as
front end. This is what I've been trying to say - I perceive it to be a
mismatch between your enthusiasm for engine level stuff and usage of
the
product for which this NG is dedicated. Stuffing everything into the
engine will either force users to become scripting masochists - and
hey,
aren't we living in the time of graphic intefaces now - or that they
just throw Access out with the bathwater. You are a bit more to the
point in this comment
http://groups.google.com/group/microsoft.public.access/msg/d3b7b020c625ff8b
<ot>I think you can do pretty much what you ask for there with ADO form
recordset within a transaction and 2002 and later versions, but you
probably know about that</ot>
So, unless there are interesting changes in the 2007 version, I don't
foresee a rush among Access developers to utilze check constraints.
> If Jet 4.0 is not for
> Access users then who?
Jet 4.0 is a very good backend database for a lot of different
purposes.
Connecting to a Jet database from Word, Excel, from systems built with
(Classic) VB, VB.Net, ASP, C#, different script technologies and all
the
things I've forgotten... There are quite a lot of Jet databases out
there where the main means of usage are through technologies that has
nothing to do with Access.
In that context, I too utilize features of the engine that aren't
supported by Access in default operating mode, since basically, in this
context Access isn't involved in normal day to day operation so
whatever
limitations Access might have aren't relevant.
>> a good design would also mean no values are
>> dependent on column(s) in the same row
>
> That is a misstatement, presumably unintentional.
Yup - lack of language skills
>> setting the AllowZeroLength
>> property in table design view ;-)
>
> You picked a good example. I think many people who use this setting
> would be surprised it will not prevent:
>
> INSERT INTO (my_text_col) VALUES ('');
It does prevent that on my setups (2000/2002/2003), unless I've
misunderstood.
I'd be happy to know under which circumstances it has problems. Here's
test code with the excact same result on 2000, 2002 and 2003, in the
latter two, regardless of ANSI mode ;-)
Sub ZLSPropertyTest()
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
'cn.Execute "DROP TABLE zlsTest"
cn.Execute "CREATE TABLE zlsTest (" & _
"id Int Identity PRIMARY KEY, " & _
"testText Varchar(25) NOT NULL UNIQUE)"
With CreateObject("ADOX.Catalog")
Set .ActiveConnection = cn
.Tables("zlsTest").Columns("testText").Properties( _
"Jet OLEDB:Allow Zero Length").Value = False
End With
On Error Resume Next
DBEngine(0)(0).Execute "INSERT INTO zlsTest (testText) " & _
"VALUES ('')", dbFailOnError
Debug.Print Err.Number, Err.Description
Err.Clear
cn.Execute "INSERT INTO zlsTest (testText) " & _
"VALUES ('')"
Debug.Print Err.Number, Err.Description
Err.Clear
End Sub
Immediate pane shows
3315 Field 'zlsTest.testText' cannot be a zero-length string.
-2147467259 Field 'zlsTest.testText' cannot be a zero-length string.
--
Roy-Vidar
Jamie Collins
RoyVidar wrote:
> I wouldn't call [CHECK constraints] supported
> unless you can find it somewhere in the UI and
> be able to select it, alter or delete it.
Let's face it, the existing Access 'table design view' (including
Access 2007) is flawed in this respect anyhow.
You can only create one Validation Rule per field/column. In real life,
I tend to want to test many individual rules at the column level. For
example, validation for an email address includes length (<= 255), the
set of legal characters, the presence of exactly one commercial at, the
length of the 'mailbox' portion, and the length and pattern of the
'domain' portion. Writing one rule for each makes maintenance easier. I
want to give helpful feedback by stating which rule has not been
satisfied. Using a field/column level Validation Rule/Text mean I must
roll all rules into one long rule and give unhelpful feedback, both of
which makes maintenance harder.
And if one rule per field/column is a problem, record/row level
Validation Rule is even worse!
BTW why are record/row level Validation Rules referred to as table
level? This is most unhelpful. I can see that it is defined in the
table's properties. The Help does not help, either: "Unlike field
validation rules, record validation rules can refer to other fields in
the same table." Wrong mental model, of course. A row/record is made up
of columns, not the other way around, so it would read better as
"...other fields in the same record". What would a validation rule that
can refer to values in other records/rows be known as? Table level, of
course!
So if a truly table level Validation Rule was introduced in the UI,
where would they put it to make it distinct from a record/row level
one? If multiple Validation Rules were to be permitted, as they surely
must be, at each level the interface would need to be significantly
redesigned. Again, I don't think there are funds for this kind of
investment and, again, perhaps the assumption is that the UI will be
tailored to beginners, for whom one rule is fine, and make the more
advanced stuff is available via DDL, DAO, etc.
Are you implying you want to see a combined expression builder/query
designer GUI to build a column/row/table level CHECK constraint? That
would be quite a beast! Let's face it, the most powerful aspect of
Jet's implementation of CHECK constraints is the ability to use
subqueries and the existing Access query designer GUI sucks at writing
subqueries. I guess the decision was made that if you want CHECK
constraints you will have to be proficient in Jet SQL; also, I guess
there has been no cash to fund an improved query designer with improved
subquery support (I imagine the same argument: if you want subqueries
it is most likely you are proficient in Jet SQL anyhow).
Looking at constraints generally, the Access UI isn't very good at
revealing them, IMO. There is a quick visual way of seeing whether a
column is part of the PK but the same does not hold true for a UNQIUE
constraint (yet I am repeatedly reminded that Jet implements UNIQUE
constraints via indexes, something which is of minority interest).
Another aspect is that the Access UI lags behind Jet development even
though the development of the latter ceased 5+ years (and three
releases of Access) ago...
> the following branch of a thread (where you also
> participated elsethreads), some of these views and
> reservations are expressed in relation to some
> ADO/DDL.
I'm glad you linked to that thread.
<OT> Did you note Allen Browne's 'you are dead to me' attitude? What's
my crime? Pointing out that Jet 4.0 features are indeed useable in the
Access UI! </OT>
As you too participated in this thread the, why didn't Allen Browne get
the backlash I'm getting here? The issue seems to be the same i.e.
obscurity of Jet 4.0 features due to lack of support in Access. Allow
me to quote from the Allen Browne article:
Cascade to Null Relations by Allen Browne
http://allenbrowne.com/ser-64.html
<quote>
There is now a way to get JET (the data engine in Access) to
automatically set the LetterID back to Null... Cascade-to-Null was
introduced six years ago, but has remained below the radar for most
developers...But what if someone else needs to rebuild the database at
some stage? Since the interface cannot show them that cascade-to-null
relations are in force, they may recreate the tables and have no idea
that your application relies on this type of cascade.
</quote>
The author goes on to propose a way of documenting the existence of ON
UPDATE SET NULL which is... well, I don't want to appear rude; suffice
to say it has 'kludge' written all over it <g>.
<OT> Why didn't I post my reservations about this article e.g.
'relations' in a SQL DBMS, that CASCADE and SET NULL are mutually
exclusive, the usage case being questionable, that elsewhere he's said
he considers ADOX to be so buggy it's unusable, etc? Well, I'm a
political lobbyist and I want to concentrate my efforts on getting
Allen Browne to review his years-old yet popular debunking of the
DECIMAL data type (see
http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/039f62de8d903224
ad nauseam), one of the Jet 4.0 features that is better implemented in
Access. It would not help the cause to appear as though I've got it in
for the guy (I truly have not) or to dilute or distract from the main
issue.</OT>
To me, Mr Browne's words (above) sound a bit like something you've said
in this thread but with a different conclusion i.e. Allen Browne
concludes that lack of support in Access is something that can be
tolerated if the Jet 4.0 feature is desirable.
Jamie.
--
Jamie Collins
RoyVidar wrote:
> > I think many people who use [AllowZeroLength]
> > would be surprised it will not prevent
>
Correction. The surprise is all mine.
Jamie.
--
Jamie Collins
RoyVidar wrote:
> > a good design would also mean no values are
> > dependent on column(s) in the same row
> >
> > That is a misstatement, presumably unintentional.
>
> Yup - lack of language skills
Please, then, restate your point. Either language - Jet SQL or English
- will do <g>; using my 'salary history' example would be ideal.
TIA,
Jamie.
--
Brendan Reynolds
could be a valued member of this community. Otherwise, you're just going to
find your way into lots of kill-files along with the rest of the trolls.
It's up to you.
--
Brendan Reynolds
Access MVP
Jamie Collins
RoyVidar wrote:
> Swithching to ANSI-92 mode in an old Access app, in itself might answer
> some of that question. This will break the "Access SQL dialect", but
> whether or not that's a bad thing, belongs to another discussion ;-),
> but it might also break existing queries.
I encourage people to write SQL to take account of, and preferably
service, both ANSI modes of operation. This is especially important for
data integrity constraints e.g. Validation Rules in Access; no use
writing constraints that can be circumvented by switching between ADO
and DAO connections.
> More "amusing", and unhealthy
> for employment, is possible consequenses of executing queries doing
> calculations, due to differences in execution between the modes.
What did you have in mind here?
The only thing I can think of is native decimals e.g.
SELECT UnitPrice * 1.175 AS UnitPrice_incl_VAT
FROM Products;
will coerce the value to Double in Jet 3.51 and Decimal in Jet 4.0 (the
above is an example of sloppy casting in Jet 3.51, of course). However,
I think this is directly related to the engine/provider rather than the
ANSI query mode.
Jamie.
--
Jamie Collins
RoyVidar wrote:
> > If Jet 4.0 is not for
> > Access users then who?
>
> Jet 4.0 is a very good backend database for a lot of different
> purposes.
> Connecting to a Jet database from Word, Excel, from systems built with
> (Classic) VB, VB.Net, ASP, C#, different script technologies and all
> the
> things I've forgotten
Where do these people go from advice about the Jet engine? Is there,
for example, a Jet 4.0 newsgroup? Should the people who post questions
that are *clearly* about Jet rather than Access be politely advised to
post their questions elsewhere? I don't think that is right; I don't
even seek to correct their usage of 'Access' (well, I don't anymore
<g>) unless I think there is value in making a distinction.
Jamie.
--
Jamie Collins
If you would please take the time to point out where in my post I have
been a 'troll' or a 'smart alec' then I will strive to alter my
approach accordingly. I will listen to your comments without replying
myself.
Many thanks,
Jamie.
--
Jamie Collins
RoyVidar wrote:
> Then I'm going to aim a bit low. I've read several of your replies in
Ah. Only after several replies to your long post did I spot this. Kinda
changes thing a bit. Is my face red...
Brendan Reynolds
I see you've made your decision. So be it. Plonk.
--
Brendan Reynolds
Access MVP
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1155122081.3...@75g2000cwc.googlegroups.com...
RoyVidar
>> More "amusing", and unhealthy
>> for employment, is possible consequenses of executing queries doing
>> calculations, due to differences in execution between the modes.
>
> What did you have in mind here?
What I had in mind, was the warning in the page you linked to
yesterday;
"Why you should avoid mixing queries created under different ANSI SQL
query modes" at
http://office.microsoft.com/en-us/assistance/HP030704831033.aspx
Here using the example from the page, the stored query with the funny
aliasing.
Sub CreateTest()
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
On Error Resume Next
cn.Execute "DROP TABLE Orders"
cn.Execute "DROP VIEW testOrders"
On Error GoTo 0
cn.Execute "CREATE TABLE Orders (" & _
"OrderID Int Identity PRIMARY KEY, " & _
"Freight Decimal NOT NULL DEFAULT 0)"
cn.Execute "INSERT INTO Orders (Freight) VALUES (100)"
cn.Execute "CREATE VIEW testOrders AS " & _
"SELECT Orders.OrderID AS Freight, Orders.Freight, " &
_
"[Freight]*1.1 AS [New Freight] FROM Orders"
End Sub
Doubleclick the query in the database window in both modes, and pretend
the query is the recordsource of a vital report.
--
Roy-Vidar
RoyVidar
>> the following branch of a thread (where you also
>> participated elsethreads), some of these views and
>> reservations are expressed in relation to some
>> ADO/DDL.
>> http://groups.google.com/group/microsoft.public.access.tablesdbdesign/browse_frm/thread/2b8601b4391581d/
>
> I'm glad you linked to that thread.
I did not link to that thread, I linked to
--
Roy-Vidar