Application Roles

[Lyle Fairfield]

Recently I have modified an ADP application to access its data through the
permissions of an application role, following the suggestions of
"How to Use Application Roles with Access Projects and SQL Server 2000
Desktop Engine (MSDE 2000)"
http://support.microsoft.com/default.aspx?scid=kb;EN-US;318816.

I have found this article helpful but incomplete and plan to write a few
supplementary notes (in my spare time, which never happens, of course) eg.
using GetString with combo and list boxes which require filtering through
parameters.

Regardless, for some of my forms I have specified a RecordSource of
"EXEC StoredProcedureName" as the article suggests. These seem to work
properly both in Access XP, (development), and Access 2K (use).

But reports based on the same Stored Procedure and with a RecordSource set
to the same string, "Exec StoredProcedureName" fail in AC2K but not in AcXP
with a message that "Exec StoredProcedureName" cannot be found.

For some reports I have substituted the SQL string of StoredProcedureName
but many of these strings exceed the maximum allowable length for Report
RecordSource in AC2K (AcXP is fine).

The solution I plan to test next is to implement the SQL string within a
table returning function and to set the RecordSource of the report to
"SELECT * FROM Owner.FunctionName".

Do you have any experience with this, comments or suggestions? Unless you
are using application roles, IMO it's unlikely anything I have written here
will have much relevance to your work.

 

[Sylvain Lafontaine]

Did you have tried "EXEC Owneer.StoredProcedureName" instead of "EXEC
StoredProcedureName"? Maybe the Record Source Qualifier must also be set
under A2000?

For the option of using a function, like SELECT * FROM Owner.FunctionName
(...)", for the recordsource of a subform, I had the following problem one
year ago: each time I changed the current record in the main form, the
subform got requeried; even if it doesn't have to (ie.: no change in the
RecordSource, not even by trying to replace it with the same string). Quite
disturbing on a Continuous Form.

It is quite possible that I will use the Application Role myself for a
project (if I don't make the decision to do with the .NET framework); so if
you find any interesting solution that is not obvious, I will be glad to
hear about it.

S. L.

 

[Lyle Fairfield]

It is quite possible that I will use the Application Role myself for a
project (if I don't make the decision to do with the .NET framework); so
if you find any interesting solution that is not obvious, I will be glad
to hear about it.

I found combo and list boxes challenging especially when I wanted to apply
some dynamic filter to them. My solution was to make their record source a
string (Value List) and create that string with ADO's GetString as:

Private Sub Form_Open(Cancel As Integer)
Dim r As ADODB.Recordset
Dim TSQL As String

If SysCmd(acSysCmdGetObjectState, acForm, "frmAssignTeachers") And
acObjStateOpen = acObjStateOpen Then
TSQL = "SELECT 0 AS fldTeacherID, NULL AS fldTeacherName" _
& vbNewLine & "UNION" & vbNewLine & _
"SELECT fldTeacherID, fldTeacherName FROM tblTeachers WHERE
fldSchoolID = " & Form_frmAssignTeachers.txtSchoolID.Value & " ORDER BY
fldTeacherName"
With Me
.lstTeachers.RowSource = CurrentProject.Connection.Execute
(TSQL).GetString(, , ",", ",")
.InsideHeight = Form_frmAssignTeachers.InsideHeight
End With
PlaceForm Me
End If
End Sub

 

[Daryl]

Lyle
Did you encounter any problem with length of string when using 'Value
List' property. I think one time I tried to use this that it can only
take a limit number of characters, maybe 2048??

daryl

 

[Lyle Fairfield]

Lyle
Did you encounter any problem with length of string when using 'Value
List' property. I think one time I tried to use this that it can only
take a limit number of characters, maybe 2048??

daryl

I did not test this as I use only short lists. For longer selections I use
floating continuous forms. But I expect that you have identified a problem
with the value list solution.