How to create calculated field in Access Database using SQL during runtime in Delphi

Question

I am creating a table during runtime. This is how I do it:

AdoCommand1.Connection:=AdoConnection1;
cs:='CREATE TABLE '+edname.text+' (' +
'ID Integer IDENTITY(1,1) NOT NULL UNIQUE PRIMARY KEY,' +
'[Date Added] DATETIME,'+
'[Name] TEXT(255))';
ADOCommand1.CommandText:=cs;
ADOCommand1.Execute;

I need to add a field "age" which should be calculated automatically like this:

age = DateDiff ('y',[Date Added], Now()) , Which simply gives amount in days that item is stored. How do I do this at runtime? Is there a way to add dynamically calculated fields to Access Database?

Note: I use Delphi 7, ADO components and Microsoft Jet 4.0 to connect to MDB Database.

Answer 1

JET does not support calculated fields in tables. Create a parallel Query with the calculated field there — the query should be updateable like the original table.

[Update in response to OP's comment]

"Query" is JET parlance for a view, and JET via ADO will accept a more-or-less standard SQL CREATE VIEW statement. Therefore, you should be able to do:

ADOCommand1.CommandText := 
  'CREATE VIEW TableNameVw AS SELECT *, (DateDiff (''y'',[Date Added], Now())) AS [Age] FROM TableName';
ADOCommand1.Execute;

This will create a new view (AKA query) in the database. Because it's a single-table non-aggregated view, you should be able to UPDATE it as if it were a table.

(All this assumes that the DATEDIFF function is supported at the JET level, which I believe is probably true).

Answer 2

Create a view (in Access this is called a Query) which returns the calculated data. The SQL Syntax is the same as it is for SQL Server.

CREATE VIEW TABLEVIEW AS
  SELECT TABLE.*, DATE() - TABLE.[DATE ADDED] AS AGE
  FROM [Table];

You can also create this in the ACCESS GUI by creating a new Query, which gives you the ability to play/test with the sql until it returns the correct data you are expecting.

When selecting this data, you do it just like you would a normal table:

SELECT * FROM TABLEVIEW WHERE AGE > 30