time serial question

[Guest]

hi - how do i make this thing:
=TimeSerial(Hour([OpenedTime])+1,Minute([OpenedTime]),Second([OpenedTime]))
populate my table, field called ExpectedResponseTime ?

 

[Guest]

Hi Tammy,

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
__________________________________________

 

[Jamie Collins]

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.

--

 

[Guest]

Jamie,

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.

--

 

[Guest]

[You seem to have missed the fact that Celko derides the use of
triggers ...

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
__________________________________________

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.

 

[Jamie Collins]

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.

--

 

[Guest]

I think you meant to say "would not prevent a user..."


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

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.

 

[Jamie Collins]

I think you meant to say "would not prevent a user..."

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.

--

 

[Guest]

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)
);

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]

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.

--

 

[Guest]

Are you in ANSI query mode?

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
__________________________________________

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.

 

[Guest]

Okay, I got it to work after all.

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
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[Guest]

I just answered my first question:

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]

I just answered my first question:

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
__________________________________________

Okay, I got it to work after all.

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
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

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.

 

[Guest]

Hi Roy,

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
__________________________________________

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
(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.

 

[Guest]

--
Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I just answered my first question:

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
__________________________________________

Okay, I got it to work after all.

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
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

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.

 

[RoyVidar]

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.

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 ;-)

 

[Jamie Collins]

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 bethe
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.

--