Migrating Calculated Fields Access BE to SQL Server

[erich_18]

Hi All,



I'm currently migrating my access back end tables (.accdb) to SQLExpress 2017 using the SQL server migration application.

I've successfully migrated the tables and connected my access front ends to the SQL back end.

but one of the access table did not completely migrate to SQL server, it did not copy the calculated fields (I believe called computed columns) to SQL.

after repeating the whole migrating process I noticed some warning on that particular table. attached an image.

here's how the calculating fields work in access

[BAF] the variable that when updated then additional columns are automatically updated:

[20OFRFULL] is a fixed value no formulas here.
[20BAFFULL] is a calculated field. formula here is: [BAF]*[20OFRFULL]
[20TTLFULL] is a calculate field. formula here is: [20OFRFULL]+[20BAFFULL]

not sure why the assistant does not copy these columns as such. the warnings state that there's problems in the column names but my column names do not have any special characters nor any reserved words.

I tried to manually add the formula in SQLServerManagement but no luck.

any ideas on how I can get the assistant to copy the calculated fields formulas to SQL ?

tks

Eric

[erich_18]

figured out the problem. I cannot reference a computed column to another computed column. I'll update my post once I find the solution.

thanks all.

[June7]

Access Calculated field can reference another Calculated field. However, AFAIK, SQLServer does not have table level calculated fields and Access is the only db platform with this feature. Solution is to not use calculated fields in Access table, do calcs in query.

[pbaldy]

However, AFAIK, SQLServer does not have table level calculated fields and Access is the only db platform with this feature.

https://docs.microsoft.com/en-us/sql...ql-server-2017

[June7]

Ah, looks like enhancement as of SQLServer 2016. Now re-educated. So SQLServer doesn't allow calculation with calculated field. Conversion of Access table with Calculated fields still not straightforward. Too bad.

[Minty]

Calculated fields have been available in SQL for ages , they certainly work in SQL 2008. https://www.mssqltips.com/sqlservert...sisted-values/

However what SQL won't like is field names beginning with numbers.

[June7]

Rats! Then I have always misunderstood the argument against calculated fields in Access was because calculated fields were not in SQLServer. They are there but there is incompatibility when migrating?

[pbaldy]

Minty, have you used them? A while back I thought "hey, I'll add a FullName computed column that concatenates the first and last name". I did and the data looked fine. An hour later a user is getting an error, turns out new records couldn't be added to the table. I didn't care enough to research, just deleted the field (it was a mature app, nothing depended on that field). Curious what would have caused the table not to accept new records just because of that field.

[Minty]

Hi Paul, I think I have somewhere, but can't remember how successful the implementation was.

I'll have a dig around - funnily enough, I believe that the First Name + Last name was one of my attempts... It's lack of working may have been unhandled nulls.