adding a new column in SQL SERVER but it is not showing up in access

Question

access is the front end and sql server 2008 is the backend

i am opening the specific table in access. i have no problem editing data in this table using sql server and then displaying the changes in access; however, when i add a new field in sql server, the changes are not reflected in access.

i am opening the table as a datasheet and it is not showing the new field that i added in access. ive opened and closed the file and still doesn't work

anyone know what's going on here?

Answer 1

You might need to refresh the SQL Server table in Access using Linked Table Manager.

Answer 2

I cannot add comment but I like to add...
since it irritates me.

It is the same story not only in MS Access, it is the same in SSMS (MS SQL Server Management Studio, even from the latest MS SQL Server 2008 R2) with its own "native" mdf databases and tables from it.

If to change the content of a table through another window / tab or instance of SSMS (by TSQL, or by interactive editing) then the content shown in already opened for interactive editing table is not updated, though it is updated by non-documented Ctrl+R.
And it is not available in GUI of SSMS (no menu entry, no button, etc.)!!!
Any browser/webapp can be easily configured to update the content but not MS desktop IDE GUI!!!

But if to ALTER TABLE, for ex., to add a column, then already opened tab with table (SSMS) cannot reflect the changes (cannot show altered table, for ex., with added column) even by pressing Ctrl+R.
It is necessary to close tab (window) with table and reopen it
(right-clicking on table in SSMS ObjectExplorer --> Edit tio 200 rows)!!!

I remeber I opened a bug in Microsoft Connect feedback on it and it was closed as this was a feature "by design"!

Still IMHO it is a bug and requires brute force (/ugly GUI actions) to bypass it