Jet(Access) DB and Expression based columns?

Question

I occasionally work on an old project that uses classic asp as a front end and an access database as a backend.

I'd like to create a new column in one of the tables that contains logic to calculate its value from the other columns in the row.

I know how to do this in a more modern DBMS, but I don't think that access supports it. Keep in mind I'm not using the access frontend, just the Jet DB engine via ODBC.

Any pointers?

Answer 1

Can you just make a calculated column?

SELECT Table1.Col_1, Table1.Col_2, [Col_1]*[Col_2] AS Col_3
FROM Table1;

Answer 2

If you must store a calculated value, which is not generally recommended, is it not possible to perform the calculation before inserting the value?

Answer 3

In a comment, Jonathan Holland asked:

I'm wondering if Jet DB's can have embedded VBscript in them like you can do in Access

Why would you want to do something like that in the back end, and not in your ASP front end? Access has saved queries, which are like views, but when used via ODBC, they cannot include VBA functions in them (though there are a few commands that Jet supports directly that may be accessible via ODBC, such as IIf() and Nz() -- but that's a big if).

Retrieve your recordset in ASP and perform your row-by-row calculations. That's the only way to do it if you're using Jet via ODBC (I don't think ADO would work any better, BTW).