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