refreshing view structure in access XP if changing table

  • Thread starter Thread starter Andreas Wöckl
  • Start date Start date
A

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
 
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
 
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
 
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
 
Back
Top