Application Role w/Access 2000 combo boxes, list boxes, reports 
Author Message
 Application Role w/Access 2000 combo boxes, list boxes, reports

I am trying to use an application role in an Access 2000 project (ADP)
connected to a SQL 7.0 database.  Issuing sp_setapprole via
CurrentProject.Connection allows access to the tables and stored procedures
when used as a form's record source and when referenced by code.  However,
today I found out that Access creates a secondary connection for use with
combo boxes, list boxes, and reports.  As such, whenever you try to use a
table or stored procedure with one of those objects, you get a "permission
denied" error because the second connection doesn't have any rights to
access the information.  This seems to render application roles from within
Access 2000 useless.  Is there something I'm missing?  Is there a way to
make Access use the CurrentProject.Connection for those as well or some way
to execute sp_setapprole on the secondary connection to fix this?  If not,
are there any other workarounds or alternate methods that will still allow
me to secure the database so that the tables and stored procedures can only
be accessed from the application I supply and prevents the user from getting
at the data via their own project, etc?  Thanks.

--

Eric Woodruff



Sun, 03 Aug 2003 10:39:22 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports

Application roles are difficult to use with Access, but not
impossible. you need to manually create a global Connection object,
activate the application role, and use that connection object. You
also need to turn off connection pooling when you connect, to prevent
those secondary connections from being created by inserting "OLE DB
Services = -2" in the connection string:

    strConn = "Provider=sqloledb;" & _
     "Data Source=(local);" & _
     "Initial Catalog=Products;" & _
     "OLE DB Services = -2;" & _
     "Integrated Security=SSPI"

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446/mcwtechnologies/

On Tue, 13 Feb 2001 18:39:22 -0800, "Eric Woodruff"

Quote:

>I am trying to use an application role in an Access 2000 project (ADP)
>connected to a SQL 7.0 database.  Issuing sp_setapprole via
>CurrentProject.Connection allows access to the tables and stored procedures
>when used as a form's record source and when referenced by code.  However,
>today I found out that Access creates a secondary connection for use with
>combo boxes, list boxes, and reports.  As such, whenever you try to use a
>table or stored procedure with one of those objects, you get a "permission
>denied" error because the second connection doesn't have any rights to
>access the information.  This seems to render application roles from within
>Access 2000 useless.  Is there something I'm missing?  Is there a way to
>make Access use the CurrentProject.Connection for those as well or some way
>to execute sp_setapprole on the secondary connection to fix this?  If not,
>are there any other workarounds or alternate methods that will still allow
>me to secure the database so that the tables and stored procedures can only
>be accessed from the application I supply and prevents the user from getting
>at the data via their own project, etc?  Thanks.



Mon, 04 Aug 2003 01:14:14 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports
Quote:
> create a global Connection object, activate the application role, and use

that connection object

So, does that imply that you can only use unbound forms and controls in
conjunction with the global connection object?  I can't find any way to
apply the global connection object to CurrentProject.Connection.  After
creating a global connection object and applying the role to it, the
CurrentProject.Connection does not inherit the same connection and role
settings with or without connection pooling.  I tried an unconnected project
and called CurrentProject.OpenConnection passing it the same connect string
as the global object but that didn't make a difference either.  I think you
can create a recordset and assign that as a form's record source, but it
still wouldn't fix the problems with the combo boxes etc.  Access still
creates its own, separate connection for those.  Short of creating a
recordset for each combo box and list box and using a function as their row
source to get the column and row values, I don't see a way around it.  That
still leaves reports without a way to use the role though.



Mon, 04 Aug 2003 11:27:54 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports
The following code will set your project to use a single connection
and turn off pooling-- adjust strings as needed.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446/mcwtechnologies/

Public Function OpenProjectConnection() As Boolean
    Dim strConn As String
    Dim strLogin As String

On Error GoTo HandleErr

    strConn = _
      "PROVIDER=SQLOLEDB.1;" _
      & "OLE DB Services = -2;" _
      & "INTEGRATED SECURITY=SSPI;" _
      & "PERSIST SECURITY INFO=FALSE;" _
      & "INITIAL CATALOG=Products;" _
      & "DATA SOURCE=(local)"

    ' Connect to SQL Server
    CurrentProject.OpenConnection strConn
    If CurrentProject.IsConnected Then
        ' Activate application role
        CurrentProject.Connection.Execute _
          "EXEC sp_setapprole 'ProductApprole', 'password'"
        OpenProjectConnection = True
    Else
        OpenProjectConnection = False
    End If

ExitHere:
    Exit Function

HandleErr:
    MsgBox Err & ": " & Err.Description
    Resume ExitHere
End Function

On Wed, 14 Feb 2001 19:27:54 -0800, "Eric Woodruff"

Quote:

>> create a global Connection object, activate the application role, and use
>that connection object

>So, does that imply that you can only use unbound forms and controls in
>conjunction with the global connection object?  I can't find any way to
>apply the global connection object to CurrentProject.Connection.  After
>creating a global connection object and applying the role to it, the
>CurrentProject.Connection does not inherit the same connection and role
>settings with or without connection pooling.  I tried an unconnected project
>and called CurrentProject.OpenConnection passing it the same connect string
>as the global object but that didn't make a difference either.  I think you
>can create a recordset and assign that as a form's record source, but it
>still wouldn't fix the problems with the combo boxes etc.  Access still
>creates its own, separate connection for those.  Short of creating a
>recordset for each combo box and list box and using a function as their row
>source to get the column and row values, I don't see a way around it.  That
>still leaves reports without a way to use the role though.



Tue, 05 Aug 2003 02:08:31 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports
Sorry, but it still doesn't work.  The only thing I changed was the "Initial
Catalog" and "Data Source" to point to our SQL server and database.  Since I
see you set Data Source to "(local)" maybe you are using MSDE or something
other than SQL Server proper and that's why it works.  The other reason it
might be working for you is that you are testing it while logged in as
yourself and you are the database owner.  If I do that it works for me too
using any method of applying the role to the connection.  The problem occurs
when another user who isn't the database owner tries to use the application
(i.e. sp_grantdbaccess has be ran and they're just in the Public role which
has no permissions).  Access creates the secondary connections internally
for its own use regardless of whether pooling is on or off.  You can see the
extra connections using sp_who.

Anyhow, I've manage to come up with a workaround using a fixed, standard
login rather than NT authentication.  I can still apply the application role
to the primary connection to limit access to the important data.  Applying
permissions to the standard login to let it only run stored procedures or
select data out of tables related to the combo boxes, list boxes, and
reports keeps Access happy on its secondary connections.



Wed, 06 Aug 2003 11:07:59 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports
you could always get around this if you assign the recordsource and
rowsource in code -- simply execute the sp_setapprole stored procedure
if it isn't active, and then assign the SQL statement to the form or
control.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446/mcwtechnologies/

On Fri, 16 Feb 2001 19:07:59 -0800, "Eric Woodruff"

Quote:

>Sorry, but it still doesn't work.  The only thing I changed was the "Initial
>Catalog" and "Data Source" to point to our SQL server and database.  Since I
>see you set Data Source to "(local)" maybe you are using MSDE or something
>other than SQL Server proper and that's why it works.  The other reason it
>might be working for you is that you are testing it while logged in as
>yourself and you are the database owner.  If I do that it works for me too
>using any method of applying the role to the connection.  The problem occurs
>when another user who isn't the database owner tries to use the application
>(i.e. sp_grantdbaccess has be ran and they're just in the Public role which
>has no permissions).  Access creates the secondary connections internally
>for its own use regardless of whether pooling is on or off.  You can see the
>extra connections using sp_who.

>Anyhow, I've manage to come up with a workaround using a fixed, standard
>login rather than NT authentication.  I can still apply the application role
>to the primary connection to limit access to the important data.  Applying
>permissions to the standard login to let it only run stored procedures or
>select data out of tables related to the combo boxes, list boxes, and
>reports keeps Access happy on its secondary connections.



Mon, 11 Aug 2003 02:02:03 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports
Hi Eric --

Mary's last suggestion should work like a charm:

- Open a new ADO connection to SQL

- Fire sp_setapprole down this connection

- Use this connection to build a recordset which holds the values
  you want to appear in your combobox

- Assign the recordset as the rowsource of the combobox

Note, however that you won't have the ability to assign a recordset as the
rowsource of a combo-box until you get your hands on Access 2002, which
recently released to manufacturing.

Regards,

Russell
Microsoft



Tue, 26 Aug 2003 05:08:22 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports

Quote:
> - Assign the recordset as the rowsource of the combobox
> Note, however that you won't have the ability to assign a recordset as the
> rowsource of a combo-box until you get your hands on Access 2002

So it works like a charm but doesn't work with the current Access 2000?  Not
much good to me right now.  Whatever the case, that's still more work than
we should have to do to make application roles work with Access.  Access
should provide a way for the developer to tell it what application role to
use for itself when it creates connections behind the scenes such as those
for combo boxes, list boxes, and reports.  Ideally, we should be able to
call a method on CurrentProject that tells it what role name and password to
use (or perhaps the appropriate SQL so we aren't dependent on the
'sp_setapprole' name if it changes or something better comes along) and it
applies it to any open connections it has and any it creates in the future.
That way, we don't have to{*filter*}around with a lot of extra code for basic
stuff like combo boxes.

Eric



Tue, 26 Aug 2003 10:52:18 GMT
 Application Role w/Access 2000 combo boxes, list boxes, reports
Hi Eric --

We're on the same page on this one; I agree completely.

What I'd ask you to do is send your request / thoughts on the subject to

this alias to help decide what features to include in future versions of
their products. Realistically you're not going to see a change in Access
2000, but it would be a nice feature for Access Developers in future
versions.

Regards,

Russell



Mon, 01 Sep 2003 23:30:33 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. List box /combo box usage in a DTS VB custom task

2. adding to a combo box or list box

3. pls help : COMBO box or list box

4. List Box and Combo box

5. Populate a combo box from another combo box selection

6. selecting records for 2nd combo box dependant on first combo box

7. Populating a combo box from another combo box

8. Filling a combo box at run time from access 2000

9. Access Combo Box List

10. VB combo list box and Access Table

11. Using form fields from Access 2000 Project as parameter in combo box source

12. Access 2000 combo boxes


 
Powered by phpBB® Forum Software