bound forms to SQL views and recordset types plus edit settings 2

[1DMF]

Hi,

I'm having problems with a bound form to SQL view locking up SQL.

The form is only needed to display a datasheet view of these table view records, no requirement to update the data , allow edits, data entry or anything else, just display.

Is it possible the fact the form was set as Dynaset, and allow updates / edits etc, that this was causing the issue.

If I just want to display the data, does this mean I should always set recordset type to 'Snapshot' and allow edits / updated / deletes etc to 'No'.

Does this help with not locking up tables in a view?

Thanks,

1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[lameid]

I think more of a SQL Server issue than Access but try the WITH(NOLocks) table hint in your view...

From X With(Nolocks)
   Inner join Y With(Nolocks)
       On X.ID = Y.ID

 

[PHV]

If I just want to display the data, does this mean I should always set recordset type to 'Snapshot' and allow edits / updated / deletes etc to 'No'
IMHO, yes (quicker and safer)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Thanks PHV,

I feel like an idiot sometimes, but at least i'm getting better by the day!

lameid, in the SQL forum i've been told that 'no locks' is a very bad idea indeed and not to use it unless it is a last resort.

Having said that one of the tables in the join really is just to get a couple of fields that very rarely changes if at all, so I guess, locking that table for no reason is not a good idea either!

hmm, snapshot plus nolocks.... sounds like a plan.

thanks guys!



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

dang!

here is the changed table view joins...

         dbo.Case_Checking INNER JOIN
                      dbo.Business_Register With(Nolocks) ON dbo.Case_Checking.Case_ID = dbo.Business_Register.Rec_ID INNER JOIN
                      dbo.Members With(Nolocks) ON dbo.Business_Register.Adv_MemNo = dbo.Members.ID

only now i get the follwoing error when trying to update the view...

"Nolocks" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[lameid]

lameid, in the SQL forum i've been told that 'no locks' is a very bad idea indeed and not to use it unless it is a last resort.

Mind providing a link to opinions of it being a bad idea? My inclination is that is the case depending on the data but here they insist on using it (occasionally I sneak in something with it off when I think it matters).

When you say you get an error when updating the view... you mean when altering it? I have no issues on SQL 2008 with the table hint. If you mean running an Update statement, I would expect it would not work in that scenario as you need a write lock to update.

 

[1DMF]

Mind providing a link to opinions of it being a bad idea?

thread962-1680419

Sorry if I wasn't clear, when tryng to change the view in studio manager , and adding Nolocks as per post above i get that error when trying to execute the update (to the view, not an update SQL statement)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

I found the problem, invalid syntax , the syntax is

WITH (NOLOCK)

there is no 's' in the table hint!

I have applied it to all views and SP's and so far (touch wood) , things are much better!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads