Use SQL View as table in Access 2007

A

AngelGirl

Is it possible to use a SQL view from another SQL database as a table in
Access and act like an Access table so several user can be on at once? We
are currently linking to Excel and more than one person can't be on the
database at once because of the Excel link.
 
D

Dirk Goldgar

AngelGirl said:
Is it possible to use a SQL view from another SQL database as a table in
Access and act like an Access table so several user can be on at once? We
are currently linking to Excel and more than one person can't be on the
database at once because of the Excel link.
Click to expand...


Yes, you can link to a SQL Server view just as if it were a table.
 
D

Dirk Goldgar

John W. Vinson said:
Generally you can't update it though... or am I mistaken?
Click to expand...


I *think* you're mistaken, though I can't verify it at the moment. But I
believe that if you specify a unique index on the view when you link it, it
will be updatable. On the other hand, it's been a while since I did this,
and I could be wrong.
 
A

a a r o n . k e m p f

you can update it sometimes, depends on what you're doing

you CAN use an insteadof trigger for when you can't (normally) update
against the view
 
R

Rick Brandt

Dirk said:
I *think* you're mistaken, though I can't verify it at the moment. But I
believe that if you specify a unique index on the view when you
link it, it will be updatable. On the other hand, it's been a while
since I did this, and I could be wrong.
Click to expand...

The rules are similar though more restrictive to those used for Access
queries. Some views allow edits and some don't. Those with joins for
example do not unless you use an instead-of trigger to make the actual
updates.
 
A

a a r o n . k e m p f

I thought that the 'uniqueTable' property can alleviate that even
further?
 
A

a a r o n . k e m p f

UNLESS you reference any functions / expressions that are non-
deterministic.. right?
I think that there's actually about a thousand more exceptions to the
rule.
 
Top