SQL Server View backed Form

  • Thread starter Thread starter riyaz.mansoor
  • Start date Start date
R

riyaz.mansoor

Hi

MS Access 2003 SP2 - SQL Server 2000 SP4

Background: Same Access VBA code works fine on 15 other forms. But a
form backed by the following view gives concurrency problems. IE, says
another user has modified the record and gives options to "Save
Record", "Drop Record" etc. This happens if I try to save a record a
second time (modify-save-modify-save[concurrency.error]). I assume
that the TimeStamp field (in T_Invoice) is not updated when after the
first save. I don't see a reason why this should be because I use
"select I.*" which should return updated record values back right?

Anyways, just to check that it is the view that's giving the problem I
had the table (T_Invoice) back the form and everything worked fine.

create view Q_Invoice as
select I.*, VT.VT_Details
from T_Invoice as I inner join T_VesselTrip as VT on
I.I_VesselTrip = VT.RefNo
where (I.LastTimeStamp + 60) > getdate()
go

So I assume this is a limitation of Access ???

Besides doing a lookup of a snapshot of T_VesselTrip for each
Form_Current event, to display VT_Details, do I have any other
options?

Riyaz
 
Hi

MS Access 2003 SP2 - SQL Server 2000 SP4

Background: Same Access VBA code works fine on 15 other forms. But a
form backed by the following view gives concurrency problems. IE, says
another user has modified the record and gives options to "Save
Record", "Drop Record" etc. This happens if I try to save a record a
second time (modify-save-modify-save[concurrency.error]). I assume
that the TimeStamp field (in T_Invoice) is not updated when after the
first save. I don't see a reason why this should be because I use
"select I.*" which should return updated record values back right?

Anyways, just to check that it is the view that's giving the problem I
had the table (T_Invoice) back the form and everything worked fine.

create view Q_Invoice as
select I.*, VT.VT_Details
from T_Invoice as I inner join T_VesselTrip as VT on
I.I_VesselTrip = VT.RefNo
where (I.LastTimeStamp + 60) > getdate()
go

So I assume this is a limitation of Access ???

Besides doing a lookup of a snapshot of T_VesselTrip for each
Form_Current event, to display VT_Details, do I have any other
options?

Riyaz
Click to expand...

I forgot to mention that a Form.Refresh call, after the record is
saved, removes the concurrency problem. This, however, does not seem
to be a solution (for server performance, and client response times).
To me this suggests that the TimeStamp field is NOT getting updated
after the save operation.

Any ideas?

Riyaz
 
Hi

Solution: Need to set the UniqueTable property on the Access.Form to
T_Invoice.

Aaaah. Everything is rosy again :)

Riyaz
 
For performance reasons, your should always use stored procedures instead of
Views when communicating between the ADP and the backend SQL-Server. Also,
beside the UniqueTable property, you should also set up the Resync command;
again for performance reasons but also because it will expand the level of
complexity that you can make with a query before it becomes too complicated
for ADP (ie., before it becomes read-only).
 
For performance reasons, your should always use stored procedures instead of
Views when communicating between the ADP and the backend SQL-Server. Also,
beside the UniqueTable property, you should also set up the Resync command;
again for performance reasons but also because it will expand the level of
complexity that you can make with a query before it becomes too complicated
for ADP (ie., before it becomes read-only).
Click to expand...

All of my views generate a subset of records available, ie only those
modified within the last 2 months. I expect that the most a view would
generate would be a 1000 rows (no heavy transactions here). Less than
10 people would access the database simultaneously - all on 100Mbps
LAN, entry level server.

Each record is expected to be modified many times (especially TEXT
fields which record the changes made to the record as a log)

All in all, my conclusion was that views alone would still be very
efficient for this particular requirement. For this requirement, do
you think I should consider Resync and SPs?

Also, what's the best way to give the users the most recent data? I
ask this in a concurrent context as many might be modifying the same
record. I don't want Access to display the warning which says another
user has modified this record blah blah.

Should I be Requerying the form, when the form has gone idle for say 2
minutes or something? How do I catch concurrent modification exception
and handle it?

Thanks
Riyaz
 
The problem here is that it's only through stored procedures (SP) that the
sql-server can return a collection of records to the exterior; ie to a a
client such as ADP or anything else. (This type of collections is called a
Resultset). Other things like views and table functions will return a table
instead and as such, must be enclosed inside a SP or a call to a SP (either
explicitely or implicitely) before returning the result to the client; so
each time you call a View or a UDF instead of a SP, you automatically add
some kind of overhead.

Another thing to think about is the fact that's only with SP that you can
tap the full power of T-SQL and manipulate such thing as enclosing a
multi-step procedure inside a transaction. The more complicated your
database is, the more likely that you will have to use the full power of
T-SQL. If you start with calling Views, then you will have to convert must
of them later one a time, with the possibility of bugs and performance
problems (ie., forget to convert all calls in the client - including useless
or contradictory sorting order, massive use of triggers now no longer really
required, dispersing your entry points into different types of objects
(views, functions, sp), etc.). The design of SQL-Server has been based on
the use of SP for communicating with the exterior and everything else (Views
and UDF) should be used inside a SP and not as an interface between a client
and a sql-server.

Resync commands are particular to ADP and will help diminushing the number
of calls make to SQL-Server after an updating to refresh the values for the
current record. These calls are always made by ADP even when you don't need
them (ie, when you know that there is no update trigger on the sql-server to
change the newly updated values to something else); so you must live with
them. They will also expand the range of complexity of your queries allowed
by ADP before they become read-only (ie, too complexe to be manipulated by
ADP). Setting the UniqueTable property is also important in this regard.

For your concurrent problem, this shouldn't happen in most well designed
databases. Usually, two users shouldn't edit the same record and if this
happens, this usually indicates that you should either divide a table into
two or more tables or that you should implement some kind of flag for an
emulation of pessimistic locking or that you shoud implement some kind of
escrow table (ie, a table where the values are stored as difference (delta)
instead of absolute values).

Bound forms are not the best thing for interfacing with any kind of
pessimistic locking (either true or emulated); so maybe you should take a
look at unbound forms.
 
im not positive I agree with this

why does a view run slower than a sproc?

I'm a big fan of views over sprocs for a hundred reasons
 
Back
Top