Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...Just wanted to let you know that I registered today, and your site is fantastic. I found solutions to problems that I have been encountering for months!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Forms Forum

bound forms to SQL views and recordset types plus edit settingsHelpful Member!(2) 

thread702-1680821
Read More Threads Like This One
1DMF (Programmer)
18 Apr 12 9:51
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

Helpful Member!  lameid (Programmer)
18 Apr 12 10:03
I think more of a SQL Server issue than Access but try the WITH(NOLocks) table hint in your view...

CODE

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

 
Helpful Member!  PHV (MIS)
18 Apr 12 10:06
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

1DMF (Programmer)
18 Apr 12 10:19
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 (Programmer)
18 Apr 12 10:27
dang!

here is the changed table view joins...

CODE

         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...

Quote:

"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 (Programmer)
18 Apr 12 13:37

Quote (1DMF):


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 (Programmer)
18 Apr 12 13:51

Quote:

Mind providing a link to opinions of it being a bad idea?
thread962-1680419: SQL keeps hanging, web apps just freeze
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 (Programmer)
19 Apr 12 9:07
I found the problem, invalid syntax , the syntax is

CODE

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

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Forms

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close