refreshing view structure in access XP if changing table

[Andreas Wöckl]

hi group!

I have a problem that drives me cracy:

I have a view that looks like this:

Select * from tbl_test

The view brings a result with 4 fields as tbl_test has 4 fields.

If i add a field to my table the view still brings the same result - althout
the table now has 5 fields and not 4. I have no idea where access xp stores
this behaviour. So every time I add a new field I have to click the "*"
twice (away and back) and than the view works

Can anyone help me?

best regards

Andreas Wöckl

 

[Paul Shapiro]

SQL Server does not automatically refresh the view when the table definition
changes. Try running sp_refreshview (see BOL) against your
view when you update the table. The documentation says that refreshes the
view metadata. The recommendation is to specify the columns instead of using
Select *. In that case you have to modify the view when the table changes,
and this problem doesn't happen.
Paul Shapiro

 

[Andreas Wöckl]

Hi Paul!

Thanks for your answer!

I tried to run the sp_refreshview - did not work until I restarted my adp ->
but I think I know can live with the situation as I will write a function to
call sp_refreshview for every view I have!

My intention not to specify the columns was that I do not have to change my
views if my tables change - now work will be easier!

many thanks!

cu

andy

 

[Paul Shapiro]

Here is a SP to refresh all views:

Create PROCEDURE dbo.spu_ViewsRefreshAll
AS
Declare @strViewName as sysname, @strSQL as varchar(1000)
Declare curView Cursor FOR
select table_name as ViewName
From INFORMATION_SCHEMA.VIEWS

Open curView

Fetch Next from curView Into @strViewName

While (@@FETCH_STATUS = 0)
Begin
Print @strViewName + ' will be refreshed now'
Set @strSQL = 'exec sp_refreshview ' + @strViewName
exec (@strSQL)

Fetch Next from curView Into @strViewName
End

Close curView
Deallocate curView

Return 0