Access 2007 / SQL 2008 slow to open....

  • Thread starter Thread starter Maurice Maglalang
  • Start date Start date
M

Maurice Maglalang

Hello,

I have posted this bug to other locations but have yet to receive a reply
from anyone.

We are experiencing a HUGE problem with opening Access 2007 SP2 connected to
SQL 2008 SP1 environment. I doubt it is related to the DoCmd.RunSQL command
(per this forum:
http://social.msdn.microsoft.com/Fo...e/thread/c2538e59-ab8e-45ee-b35d-11863e71b5b2)
because we do not use that at all but we do use many of the other DoCmd
methods.

I ran profiler and opened our ADE then noticed that it's always hanging on
the same query as Duvanelj mentioned in the above link. Although, this is
only happening when Access is first opened for our REGULAR USERS. This is
NOT a problem for ADMINS.

I've isolated profiler to specific users (1 admin - 3 seconds to open Access
2007, 4 regular users - average 30 seconds to open Access 2007) and can
submit the .trc files to you if you wish.

And here's the logic for our frmSwitchBoard when it first opens:


Private Sub Form_Open(Cancel As Integer)

On Error GoTo eh

'Check for Administrator Role
If CheckRole("db_owner") Then
If CurrentProject.Properties("AllowBypassKey") = False Then
Me.lblEnableThingy.Visible = True
Me.lblDisableThingy.Visible = False
Else
Me.lblEnableThingy.Visible = False
Me.lblDisableThingy.Visible = True
End If
strRole = "db_owner"
Else
'Figure out which group the user is in and adjust the forms
accordingly
strRole = GetRole()
Me.lblEnableThingy.Visible = False
Me.lblDisableThingy.Visible = False
End If

End Sub

Function CheckRole(strRole As String) As Boolean

'This makes sure that the client is in the role required
'Stop
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

CheckRole = False
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "IF IS_MEMBER('" & strRole & "') = 1 " & _
"Select Role = -1 " & _
"ELSE IF IS_MEMBER('" & strRole & "') = 0 " & _
"Select Role = 0 " & _
"ELSE IF IS_MEMBER('" & strRole & "') Is Null " & _
"Select Role = 0 " & _
"ELSE IF IS_SRVROLEMEMBER('" & strRole & "') = 1 " & _
"Select Role = -1 "
End With
Set rs = cmd.Execute

If rs!Role = -1 Then
CheckRole = True
Else
CheckRole = False
End If

Set cmd = Nothing
Set rs = Nothing

End Function

Function GetRole() As String

'This establishes the first screen that a client will see on the
switchboard
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "spCheckRole"
End With

Set rs = cmd.Execute
GetRole = rs!Role

Set cmd = Nothing
Set rs = Nothing

End Function

---------

And here's 1 entry from the trace file. Notice how the ADMIN's duration is
84 but the USER's is 4388 for the exact same query:

SQL:BatchCompleted select tbl.name, schema_name(tbl.schema_id), tbl.type,
cast(case when tbl.is_ms_shipped = 1 then 1when (select major_id from
sys.extended_properties where major_id = tbl.object_id and minor_id = 0 and
class = 1 and name = N'microsoft_database_tools_support')is not null then 1
else 0 end as bit)from sys.all_objects AS tbl where tbl.type in (N'U', N'S')
and permissions(tbl.object_id) & 4096 <> 0 2007 Microsoft Office system
admin ADS\admin 78 3165 0 84 3536 77 2009-10-22 12:19:30.237 2009-10-22
12:19:30.320

SQL:BatchCompleted select tbl.name, schema_name(tbl.schema_id), tbl.type,
cast(case when tbl.is_ms_shipped = 1 then 1when (select major_id from
sys.extended_properties where major_id = tbl.object_id and minor_id = 0 and
class = 1 and name = N'microsoft_database_tools_support')is not null then 1
else 0 end as bit)from sys.all_objects AS tbl where tbl.type in (N'U', N'S')
and permissions(tbl.object_id) & 4096 <> 0 2007 Microsoft Office system
user1 ADS\user1 4360 1068 0 4388 3096 64 2009-10-22 12:31:53.500 2009-10-22
12:31:57.887

I should also add that the query above is hanging immediately before and
after FORM_OPEN and that I have no control over this query because it is
something that Access is automatically doing. Not sure if that's relevant
yet....but the proof is in the .trc which I can email / skydrive just for you
if necessary.

I greatly appreciate any help ANYONE can provide!

Sincerely, Maurice Maglalang
 
I'd recommend moving all of that startup logic from your client code
to a stored procedure that returns a single row that you cache in a
global recordset. The only thing you should see in Profiler is the
Execute statement, and you could reasonably expect sub-second response
time from the server.

--Mary

On Tue, 3 Nov 2009 16:08:05 -0800, Maurice Maglalang <Maurice
 
Hi Mary, Thanks for your immediate response (not sure why I had to find it at
a usenet) but I really doubt you took the time to recreate my problem. It
makes absolutely no sense to me that this code we've been using since Access
2000 without any problems is all of a sudden hanging after upgrading to
Access 2007. What I think you're failing to understand is that WITH or
WITHOUT my startup code that query is ALWAYS being executed. Please advise
or put a different MVP on my issue...Sincerely, Maurice Maglalang
 
Hi Mary, Thanks for your immediate response (not sure why I had to find it at
a usenet) but I really doubt you took the time to recreate my problem. It
makes absolutely no sense to me that this code we've been using since Access
2000 without any problems is all of a sudden hanging after upgrading to
Access 2007. What I think you're failing to understand is that WITH or
WITHOUT my startup code that query is ALWAYS being executed. Please advise
or put a different MVP on my issue...Sincerely, Maurice Maglalang
 
Hi,
I don’t think this is Access issue, as both Access 2000 and 2007 uses same
library to run your code, perhaps something wrong in SQL server
configuration.
Try to narrow the problem - find what TSQL statement running slower under
user, run it under sql server environment. Then try to ask at SQL server
newsgroups

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
I got past the problem by giving the PUBLIC database role rights to the
database I was having a problem with. Considering that applying USER roles
in SQL 2000 and SQL 2005 worked just fine in the past, I have no idea whether
this is the correct way to resolve the problem. I've received NO REPLIES
from any "MVP" so I'm hoping I made the right decision for my 600 users. The
good news is that adding layers of role-based security at the SCHEMA level
overrode anything I did at the parent level. Hope that helps! Sincerely,
Maurice Maglalang (maurice at akademe dot net)
 
Maurice Maglalang said:
I got past the problem by giving the PUBLIC database role rights to the
database I was having a problem with. Considering that applying USER
roles
in SQL 2000 and SQL 2005 worked just fine in the past, I have no idea
whether
this is the correct way to resolve the problem. I've received NO REPLIES
from any "MVP" so I'm hoping I made the right decision for my 600 users.
The
good news is that adding layers of role-based security at the SCHEMA level
overrode anything I did at the parent level. Hope that helps! Sincerely,
Maurice Maglalang (maurice at akademe dot net)

"Maurice Maglalang" wrote:
Click to expand...

I don't want to be blunt but at 600 users, you should have opened a support
ticket at Microsoft.

These public forums are open to everyone and the questions here are answered
free of charge on a free time basis. The "MVP" don't work for MS; they just
appears to give a little more of their freetime than other people around
here. I'm sure that if anyone here would have known the answer, they would
have answered back to you.

Finally, thanks for your solution; I'm sure it will be helpful to other
people.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
I don't want to be blunt, but you've obviously never opened a ticket with
Microsoft. Maybe your clients can wait 3-6 months for a resolution or an
upgrade or an SP release from them, but my clients will NOT.
 
When I had a pretty obscure bug with Access-SourceSafe interactions, I got a
response in days. The tech spent a lot of time to reproduce the issue and
identify two very reasonable work-arounds. I'm sure that not all support
requests go as well as that, but my MS support experience was excellent.
 
Back
Top