Adding Calculated field via VBA (1 Viewer)

[Design by Sue]

I am updating an existing database with a front and backend. I need to add a calculated field to an existing table in the backend. I have searched but can't find any info on how to do this. Anyone have any idea of how to accomplish this?

As always, thanks
Sue

 

[MarkK]

Well, strictly speaking there is no such thing as a calculated field in an Access table. You can have a calculated field in a query, like . . .

SELECT Field1, Field2, Field1 + Field2 As Field3
FROM Table1

. . . but no calculation will occur natively in a Jet/ACE table. You will simply have to store the result of a calculation, which is not recommended. So, the simplest solution is to query the table in question as described above.

To add a field programmatically, there are two ways that jump to mind.
1) Use a Jet SQL ALTER TABLE statement, as described here . . . http://www.devguru.com/technologies/jetsql/16880 or
2) Use DAO, as described here . . . https://msdn.microsoft.com/en-us/library/office/Ff821396.aspx

 

[jdraw]

Sue,

Can you tell us more about

I need to add a calculated field to an existing table in the backend.

Also, I think Markk has identified the options for HOW. I'd like to hear a little more about WHAT the business requirement is --just curious. Changing the table structure of an operational system is not uncommon, but does require some thought.
Good luck.

 

[spikepl]

I agree with the previous comments. But in addition, after 600+ posts you really should brush up on your googling skills. Googlig is at times more important than coding. It took about 3 minutes to find the answer using

add+calculated+field+to+table+access+vba


as search words.

 

[sneuberg]

To: spikepl In the defense of Sue I work with her and I asked her to post the question, but not before a lot of looking for a solution. I googled "ms access create calculated field programmatically" for example and only found one site that was discussing the problem but never came up with a solution.

To: MarkK The second way you posted was exactly what we were looking for. Thanks.

To: jdraw The calculate field calculates the volume in cubic feet of the dimensions of a case that are given in inches. The actual expression is "Round((([CWidth] * [CHeight] * [CDepth]) / 1728), 2)". The fields already existed in the database. We just need to add the calculation. We can't do this manually as we don't have direct access to the backend of this system. Every time we release a new version of the frontend application we need to write code to modify the backend as required. This calculation could have been performed in the frontend but that would have meant adding it in about seven places, so the hell with the Normalization Police, we want it in the table.

Thanks for your help
Steve