Incorrect columns displayed when view programatically altered

  • Thread starter Thread starter Debbie Keating
  • Start date Start date
D

Debbie Keating

I have a view in an Access 2002/MSDE 2000 database.

I use a form to programatically alter this view to show
various columns according to various criteria depending
upon what the user wants to see (lots of check boxes).

I create the SQL statement using VBA and run the ALTER
VIEW statement (in VBA) before then opening the view to
show the results in a datasheet.

However if I change the columns to be displayed from the
last time it was run within the same session, the query
runs correctly but displays only the previously shown
columns regardless of wether they exist in the view.

If I open the view via SQL Enterprise Manager (at the
same point where Access shows it incorrectly) the columns
are shown correctly.

I have tried to correct this by forcing a recompile of
the view before opening it but this does not correct the
problem as I suspect it is at the Access end.

Please help.

Debbie.
 
I think you're probably right that it's Access that needs refreshing. You
said you recompile the view. Did you try running sp_refreshview against your
updated view? The documentation says that refreshes the view metadata. I've
used that when underlying tables were changed, so that's probably not the
solution, but you could try it.
Paul Shapiro
 
Just tried your suggestion.

Unfortunately it didn't work. I think this is only
refreshing the SQL part and not the column definitions
stored by Access.

Thanks anyway.

Debbie
 
Back
Top