Access Database Default Date cannot contain 00 hour, 00 min or 00 sec

Question

So, we have an old VB 6 App written many many years ago.



It uses an Access Database to store data, and the VB6 App as the interface to this data Now, to change the VB6 app would be quite difficult, and I'm trying to avoid this.



The problem is, when the VB6 inserts data into a table in the access database, the table has a column which uses 'Now()' to set the default value.


Now there are calculations done on this date and time value, including multiplication. So.. when any of the date/time components are 0, the final result is 0.


I.E dd * mm * yy * hh * n * s - If any are zero, the result is zero



To overcome this I can

• Change the code to specify the date (not very ideal)

• hopefully in the default value of the Access Database put some validation to make sure there are no 0 values in the time



Is there any way to validate the default value like this? Something like

If DatePart(‘hh’,date) == 0 OR DatePart(‘n’,date) = 0 OR DatePart(‘s’,Date) = 0
Then add one to all of them.

Edit
This is not my code, this is code written way before I, and ther other developers started.
We can't be certain the code we have is the code being used in production. Hence why I'd rather not change code. We are planning on re-writing this soon, when time permits.
The problem only occurs once or twice a month, so it's not vital to the running of the application.

Answer 1

While it may not be ideal to edit the VB6 application, it would be the most robust way of dealing with the problem.

Your proposal to alter the data itself seems like a very bad idea since it can potentially throw off dates if the data were ever to be used by another application later on. Better to let the application, with its unique requirements, transform the data at runtime to perform the calculations.

Always store your data in the most standard, normalized format possible. You have no idea who will be using that data years from now when you're no longer working there. The last thing you want is the bad karma of a new developer cursing your name because you thought it would be a good idea to alter the standard format for storing dates in a database.

It is also worth noting that many Date() objects or date libraries already have ways of calculating the number of seconds in a particular date, so it could even simplify your code.

Answer 2

Can't be done using the column's DEFAULT.

You could create a 'helper' procedure to generate the date e.g. (Access Database Engine ANSI-92 Query Mode syntax):

CREATE PROCEDURE AddOrderWithDefaultDate
(
  arg_OrderID INTEGER, 
  arg_CustomerID NVARCHAR(5), 
  arg_EmployeeID INTEGER 
)
AS 
INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate) 
SELECT DISTINCT arg_OrderID, arg_CustomerID, arg_EmployeeID, 
       DATE() 
          + TIMESERIAL(
                       IIF(DATEPART('H', NOW()) = 0, 1, DATEPART('H', NOW())), 
                       IIF(DATEPART('N', NOW()) = 0, 1, DATEPART('N', NOW())), 
                       IIF(DATEPART('N', NOW()) = 0, 1, DATEPART('S', NOW()))
                      ) AS OrderDate
       FROM Products;

Prior to the ACE (2007) version of the Access Database Engine you could remove the INSERT and UPDATE privileges from the table and grant to your 'helper' procedures.

Additionally, you should add a CHECK constraint or Validation Rule to the column to ensure the ensure the column is not explicitly inserted or updated using an illegal value. Yes, you need this even when privileges are only on your 'helper' functions, unless you are the mythical kind of coder who only writes bug-free code ;)

But, frankly, why bother? It sounds like there is a problem with your business logic being unable to handle zeros in DATETIME values. Don't just to treat the symptoms: cure the disease and fix the faulty logic,