Error when using linked SQL Server View

[Terrell Miller]

Got an Access XP databse that has ODBC links to SQL Server
tables and views.

I've noticed a weird thing with the views: if I make
changes to them in SQL, or if I change the underlying
table in SQL, then if I try to use the linked view in
Access I get ODBC Falied errors.

The only workaroiund I've found is to go into Enterprise
Manager, drop the view, and create it again, or use an
ALTER VIEW clause.

If I do that, when I go back to Access and try the view,
it works fine.

Anybody know what's going on to cause this hiccup? I don't
want to have to keep touching the views every time we
change a table (the views just Select * from one table
with a WHERE clause).

TIA,

Terrell

 

[Joe Fallon]

You have to re-link all objects in Access whenever you modify the underlying
SQL Server table.
That is "just the way it is".
I always use code to re-link my tables at the touch of a button.
I have posted it here about 7,589 times over the years. I think you can find
it if your need to see how to do it. <g>

 

[Guest]

-----Original Message-----
You have to re-link all objects in Access whenever you modify the underlying
SQL Server table.
That is "just the way it is".

Thanks Joe, but something else is actually happening. I
can update the link in Access repeatedly and still get the
ODBC error.

It's almost the opposite of linking to a SQL table, which
behaves the way you mentioned and which is what you were
perhaps thinking about.

For some reason, with a View (as opposed to a table) I
can't get Access to see the data unless I refresh the View
*in SQL Server* (got a sproc that executes the
sp_refreshview stored procedure for each View I need to
refresh).

Then if I open the view in Access (without even having to
do Linked Table Manager, just doubleclick the icon), it
works fine.

No idea why Access handles views differently than linked
tables, hopefully someone can shed some light.

HTH,

Terrell

 

[Joe Fallon]

Hmmm.
That is odd.
I haven't dealt with views a lot but in my tests they seem to work.
I vaguely recall that a view (or SPT query?) wouldn't run when the DB was
first opened but if I opened ANY table first then it would work fine. Maybe
it has to do with establishing a connection.

Try that and see if it helps.