MsAccess to SqlServer: sp_setapprole problem

[adjo]

I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstrin g has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.

[Rich P]

Re: MsAccess to SqlServer: sp_setapprole problem

The issue you are experiencing is caused by the limited capabilities of
ODBC. For basic operations ODBC is an easy way to connect an Access mdb
to a sql server backend. But once the operations get a little more
sophisticated - as in your case - the limitations of ODBC become
apparent. The workaround is to switch to ADO - this is why ADO was
developed - to overcome the limitations of ODBC.

you can execute sp_setapprole through an ADODB.Command object very
easily (just make a reference in Tools/References to Microsoft ActiveX
DataObjects Library 2.5 or higher)

Sub xyz()

Dim cmd As New ADODB.Command
cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourSrvr ;Database=yourS vrDB;Trusted_Co nnection=Yes
cmd.ActiveConne ction.CursorLoc ation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setappr ole"
cmd.Execute
cmd.ActiveConne ction.Close

End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[adjo]

Re: MsAccess to SqlServer: sp_setapprole problem

On 27 jun, 19:13, Rich P <rpng...@aol.co mwrote:

The issue you are experiencing is caused by the limited capabilities of
ODBC.  For basic operations ODBC is an easy way to connect an Access mdb
to a sql server backend.  But once the operations get a little more
sophisticated - as in your case - the limitations of ODBC become
apparent.  The workaround is to switch to ADO - this is why ADO was
developed -  to overcome the limitations of ODBC.
>
you can execute sp_setapprole through an ADODB.Command object very
easily (just make a reference in Tools/References to Microsoft ActiveX
DataObjects Library 2.5 or higher)
>
Sub xyz()
>
Dim cmd As New ADODB.Command
cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourSrvr ;Database=yourS vrDB;Trusted_Co nnection=Yes
cmd.ActiveConne ction.CursorLoc ation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setappr ole"
cmd.Execute
cmd.ActiveConne ction.Close
>
End Sub
Rich
>
*** Sent via Developersdexht tp://www.developersd ex.com***

Thx. But I would realy like to stick to DAO for the application
itsself. I know DAO isn't the nr 1 technology any more, but I know DAO
well and don't do that much programming work as I used to do in the
past. For new stuff I concentrate on the Dotnet framework with it's
Ado.Net.
What would be nice is if the ADO code you send would have its effect
on the connection DAO/ODBC uses. That would be perfect, but as most
things in life 'perfect' isn't often the case. I'll test it anyway....

[adjo]

Re: MsAccess to SqlServer: sp_setapprole problem

On 27 jun, 17:15, adjo <adgn...@gmail. comwrote:

I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstrin g has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.

By the way: is there another way to solve the 'get to the data via
another way than the app' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....

[lyle fairfield]

Re: MsAccess to SqlServer: sp_setapprole problem

On Jun 28, 7:02 am, adjo <adgn...@gmail. comwrote:

On 27 jun, 17:15, adjo <adgn...@gmail. comwrote:
>
>
>

I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstrin g has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.

>
By the way: is there another way to solve the 'get to the data via
another way than the app' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....

One can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.

My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.

In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.

There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.

[Tony Toews [MVP]]

Re: MsAccess to SqlServer: sp_setapprole problem

adjo <adgnews@gmail. comwrote:

>I know Dao/Jet is obsolote, but it is still working like a charm for
>me. I use Ado.net in dotnet projects. But for Access frontends I have
>(had?) no reason to say goodbye. Still hope the best of both worlds
>can be combined.

DAO/Jet is not obsolete despite Aaron's strident postings on this topic. IN A2007
it's been updated and is now called ACE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[lyle fairfield]

Re: MsAccess to SqlServer: sp_setapprole problem

You're right. It's still going strong, the same old unsophisticated ,
ugly, clumsy technology we knew and loved almost,twenty years ago.

Why does MS cling to it? IMO it's this way. MS wants to sell Access.
There are more people who are lazy and/or stupid than there are who
are smart and/or industrious. So their marketing is aimed at the lazy/
stupid majority. Here you go, bozos, a database you can use with no
effort, training, learning caring or education. A new, sophisticated
technology scarcely fits with that, does it?


On Jun 28, 12:52 pm, "Tony Toews [MVP]" <tto...@teluspl anet.net>
wrote:

adjo <adgn...@gmail. comwrote:

I know Dao/Jet is obsolote, but it is still working like a charm for
me. I use Ado.net in dotnet projects. But for Access frontends I have
(had?) no reason to say goodbye. Still hope the best of both worlds
can be combined.

>
DAO/Jet is not obsolete despite Aaron's strident postings on this topic.  IN A2007
it's been updated and is now called ACE.

[Tony Toews [MVP]]

Re: MsAccess to SqlServer: sp_setapprole problem

lyle fairfield <lyle.fairfield @gmail.comwrote :

>You're right. It's still going strong, the same old unsophisticated ,
>ugly, clumsy technology we knew and loved almost,twenty years ago.
>
>Why does MS cling to it? IMO it's this way. MS wants to sell Access.
>There are more people who are lazy and/or stupid than there are who
>are smart and/or industrious. So their marketing is aimed at the lazy/
>stupid majority. Here you go, bozos, a database you can use with no
>effort, training, learning caring or education. A new, sophisticated
>technology scarcely fits with that, does it?

ADO has some new features that I'm aware of but none seemed
particularly useful to me.

From what I've seen ADO and DAO don't have a lot of differences. So
what else or what improvements would you suggest?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[lyle fairfield]

Re: MsAccess to SqlServer: sp_setapprole problem

On Jun 29, 12:09 am, "Tony Toews [MVP]" <tto...@teluspl anet.net>
wrote:

From what I've seen ADO and DAO don't have a lot of differences.  So
what else or what improvements would you suggest?

A good eye Doctor?

[lyle fairfield]

Re: MsAccess to SqlServer: sp_setapprole problem

On Jun 29, 12:09 am, "Tony Toews [MVP]" <tto...@teluspl anet.net>
wrote:

lyle fairfield <lyle.fairfi... @gmail.comwrote :

You're right. It's still going strong, the same old unsophisticated ,
ugly, clumsy technology we knew and loved almost,twenty years ago.

>

Why does MS cling to it? IMO it's this way. MS wants to sell Access.
There are more people who are lazy and/or stupid than there are who
are smart and/or industrious. So their marketing is aimed at the lazy/
stupid majority. Here you go, bozos, a database you can use with no
effort, training, learning caring or education. A new, sophisticated
technology scarcely fits with that, does it?

>
ADO has some new features that I'm aware of but none seemed
particularly useful to me.  
>
From what I've seen ADO and DAO don't have a lot of differences.  So
what else or what improvements would you suggest?
>
Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab. ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

----------------------

GetString Method (ADO)

Returns the Recordset as a string.

Variant = recordset.GetSt ring(StringForm at, NumRows, ColumnDelimiter ,
RowDelimiter, NullExpr)

-----------------

Save Method

Saves the Recordset in a file or Stream object.

recordset.Save Destination, PersistFormat

Parameters

Destination

Optional. A Variant that represents the complete path name of the
file where the Recordset is to be saved, or a reference to a Stream
object.

PersistFormat

Optional. A PersistFormatEn um value that specifies the format in
which the Recordset is to be saved (XML or ADTG). The default value is
adPersistADTG.

--

Open Method (ADO Recordset)

Opens a cursor on a Recordset object.

Parameters

Source

Optional. A Variant that evaluates to a valid Command object, an
SQL statement, a table name, a stored procedure call, a URL, or the
name of a file or Stream object containing a persistently stored
Recordset.

ActiveConnectio n

Optional. Either a Variant that evaluates to a valid Connection
object variable name, or a String that contains ConnectionStrin g
parameters.

CursorType

Optional. A CursorTypeEnum value that determines the type of
cursor that the provider should use when opening the Recordset. The
default value is adOpenForwardOn ly.

LockType

Optional. A LockTypeEnum value that determines what type of
locking (concurrency) the provider should use when opening the
Recordset. The default value is adLockReadOnly.

Options

Optional. A Long value that indicates how the provider should
evaluate the Source argument if it represents something other than a
Command object, or that the Recordset should be restored from a file
where it was previously saved. Can be one or more CommandTypeEnum or
ExecuteOptionEn um values, which can be combined with a bitwise OR
operator

----------

GetChildren Method (ADO)

Returns a Recordset whose rows represent the children of a collection
Record.

Set recordset = record.GetChild ren

Return Value

A Recordset object for which each row represents a child of the
current Record object. For example, the children of a Record that
represents a directory would be the files and subdirectories contained
within the parent directory

[Larry Linson]

Re: MsAccess to SqlServer: sp_setapprole problem

Lyle, do you find those operations useful in the "normal business databases"
for which Access is so well-suited, or just when using an ADP as a front-end
to MS SQL Server? I don't seem to recall, in using Access since its
inception, a _need_ to "get a table/query as a string". Most of my clients,
even those with MS SQL Server, did not want any vital applications created
that would limit their ability to change the server back end if their
company decided to do so. Thus, ADO's were unacceptable to those clients.

Refresh my memory: didn't you write that you, for good reason, stopped using
ADPs?

Or are you talking about using ADO in an MDB/MDE/ACCDB? I'm reluctant to use
a data access technology that has already been superceded in its "natural
environment" (Microsoft's developer tools) by ADO.NET which is little like
"classic ADO" (as I am sure you are aware).

Larry Linson
Microsoft Office Access MVP


"lyle fairfield" <lyle.fairfield @gmail.comwrote in message
news:fdd585d9-7f9a-4a17-9cc9-fc176986d80c@25 g2000hsx.google groups.com...
On Jun 29, 12:09 am, "Tony Toews [MVP]" <tto...@teluspl anet.net>
wrote:

lyle fairfield <lyle.fairfi... @gmail.comwrote :

You're right. It's still going strong, the same old unsophisticated ,
ugly, clumsy technology we knew and loved almost,twenty years ago.

>

Why does MS cling to it? IMO it's this way. MS wants to sell Access.
There are more people who are lazy and/or stupid than there are who
are smart and/or industrious. So their marketing is aimed at the lazy/
stupid majority. Here you go, bozos, a database you can use with no
effort, training, learning caring or education. A new, sophisticated
technology scarcely fits with that, does it?

>
ADO has some new features that I'm aware of but none seemed
particularly useful to me.
>
From what I've seen ADO and DAO don't have a lot of differences. So
what else or what improvements would you suggest?
>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems
athttp://www.granite.ab. ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

----------------------

GetString Method (ADO)

Returns the Recordset as a string.

Variant = recordset.GetSt ring(StringForm at, NumRows, ColumnDelimiter ,
RowDelimiter, NullExpr)

-----------------

Save Method

Saves the Recordset in a file or Stream object.

recordset.Save Destination, PersistFormat

Parameters

Destination

Optional. A Variant that represents the complete path name of the
file where the Recordset is to be saved, or a reference to a Stream
object.

PersistFormat

Optional. A PersistFormatEn um value that specifies the format in
which the Recordset is to be saved (XML or ADTG). The default value is
adPersistADTG.

--

Open Method (ADO Recordset)

Opens a cursor on a Recordset object.

Parameters

Source

Optional. A Variant that evaluates to a valid Command object, an
SQL statement, a table name, a stored procedure call, a URL, or the
name of a file or Stream object containing a persistently stored
Recordset.

ActiveConnectio n

Optional. Either a Variant that evaluates to a valid Connection
object variable name, or a String that contains ConnectionStrin g
parameters.

CursorType

Optional. A CursorTypeEnum value that determines the type of
cursor that the provider should use when opening the Recordset. The
default value is adOpenForwardOn ly.

LockType

Optional. A LockTypeEnum value that determines what type of
locking (concurrency) the provider should use when opening the
Recordset. The default value is adLockReadOnly.

Options

Optional. A Long value that indicates how the provider should
evaluate the Source argument if it represents something other than a
Command object, or that the Recordset should be restored from a file
where it was previously saved. Can be one or more CommandTypeEnum or
ExecuteOptionEn um values, which can be combined with a bitwise OR
operator

----------

GetChildren Method (ADO)

Returns a Recordset whose rows represent the children of a collection
Record.

Set recordset = record.GetChild ren

Return Value

A Recordset object for which each row represents a child of the
current Record object. For example, the children of a Record that
represents a directory would be the files and subdirectories contained
within the parent directory

[adjo]

Re: MsAccess to SqlServer: sp_setapprole problem

On 28 jun, 14:56, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On Jun 28, 7:02 am, adjo <adgn...@gmail. comwrote:
>
>
>
>
>

On 27 jun, 17:15, adjo <adgn...@gmail. comwrote:

>

I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstrin g has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.

>

By the way: is there another way to solve the 'get to the data via
another way than the app' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....

>
One  can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.
>
My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.
>
In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.
>
There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.- Tekst uit oorspronkelijk bericht niet weergeven -
>
- Tekst uit oorspronkelijk bericht weergeven -

Thanks. This is the kind of reaction I need. I already expected that
the 'sp_setapprole' is not realy useful in my situation because Access
is not suited as a stable clientenvironme nt. Your experiences save me
the time and frustration of keep on trying. It's one of the very few
moments I get stuck with Access as a clienttool.

One can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.

Hm. With a lot of users this is not realy attractive. Think I'd rather
stick with one useraccount and hiding the accountdata as good as
possible. This works for almost 20 years now without a problem. But in
the application I'm talking about it would have been so nice to be
able to use int.security so that users have their own account. Would
also have been easier for our Dba in case performance or locking
issues should occur.

[adjo]

Re: MsAccess to SqlServer: sp_setapprole problem

On 28 jun, 14:56, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On Jun 28, 7:02 am, adjo <adgn...@gmail. comwrote:
>
>
>
>
>

On 27 jun, 17:15, adjo <adgn...@gmail. comwrote:

>

I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstrin g has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.

>

By the way: is there another way to solve the 'get to the data via
another way than the app' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....

>
One  can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.
>
My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.
>
In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.
>
There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.- Tekst uit oorspronkelijk bericht niet weergeven -
>
- Tekst uit oorspronkelijk bericht weergeven -

Come on guys stop this everlasting discussion about the quality of
MsAccess. Don't like it then don't use it is my advice.

[lyle fairfield]

Re: MsAccess to SqlServer: sp_setapprole problem

On Jun 30, 1:02 am, "Larry Linson" <boun...@localh ost.notwrote:

Lyle, do you find those operations useful in the "normal business databases"
for which Access is so well-suited,

I confess that the majority of "my" applications are not normal
business databases; I would describe them as "algorithmi c" databases,
where calculations are primary and the collection and entry of data
are secondary. I started in this business when I was member of a team
negotiating the first collective agreement for a educational system.
The other side said, "We won't implement that because its cost CANNOT
be calculated." I said, "I can calculate its cost." (The conversation
may have been a little longer and a little harsher.) That night I did,
using Lotus and its macro language. A few days later I talked this
over with a friend from Stelco and he gave me pirated copies of DBase
III. I was hooked. (But I bought DBase III right away as the pirating
part never appealed to me.)

or just when using an ADP as a front-end
to MS SQL Server?  I don't seem to recall, in using Access since its
inception, a _need_ to "get a table/query as a string".

I find GetString and the Split function especially useful in quickly
translating a recordset into a multi-dimensional array. Arrays are
very much faster than recordsets for recursive, intense calculations.
Another use is mentioned below.

Most of my clients,
even those with MS SQL Server, did not want any vital applications created
that would limit their ability to change the server back end if their
company decided to do so.  Thus, ADO's were unacceptable to those clients.

I don't understand this. After we set the Connection once for each
user (with code or the Connection Dialog) this code grabs each user's
connection from the registry and connects to the db. Thus, each user
can have a different backend, and, of course, there would be no
limiting changing the server backend.
Public Function BaseConnect()
Dim ConnectionStrin g$
Dim ProjectName$
ProjectName = Split(CurrentPr oject.Name, ".")(0)
ConnectionStrin g$ = _
GetSetting(Proj ectName, "Startup", "BaseConnection String")
With CurrentProject
If .IsConnected Then
If .BaseConnection String <ConnectionStri ng Then _
SaveSetting ProjectName, _
"Startup",
"BaseConnection String", .BaseConnection String
Else
If Len(ConnectionS tring) 0 Then _
.OpenConnection ConnectionStrin g
End If
End With
End Function
(This code is great for application delivery. On my development
machine it connects to my server, and on the program machines it
connects to their servers, with no fuss.)

Refresh my memory: didn't you write that you, for good reason, stopped using
ADPs?

Yes, I did. The reason was security and is directly related to the
subject of this thread. If I give you permissions on the server, but
control what you can do through my ADP application, there is a big
security problem, IMO. If you create a new ADP, the Connection dialog
will show, maybe even suggest is not too strong a word, the server and
database for which you have permissions. When you click Connect you
now can use those permissions without any control from my application.
All the tables and other objects show up in the DB window. OUCH! I
think this model is fundamentally flawed. But, I felt better when Rick
Brandt suggested the same pitfall existed with ODBC connections. It
seems that it does. But it may be much more unlikely that an MDB-ODBC
user will create a new ADP than an ADP-OLEDB user.
The solution is application roles as per this thread. The application
connection has permissions, not the user who has only login
permissions for the server, but nothing else. When he creates a new
ADP he can see or use nothing. His db window is blank and he can do
nothing, not even with code. But it's "applicatio n connection" that is
the killer here. It's the Connection that fills the role and has the
permissions, not the application as we might think. Access in general
and ADPs particularly are entirely undisciplined about connections. We
may think that there is just CurrentProject. Connection or
CodeProject.Con nection or CurrentProject. AccessConnectio n etc existing
and we should because that's all we see. But I discovered, much to my
chagrin, that Access opens new connections for many things, including
ListBoxes and ComboBoxes. Once when demo-ing an application for ten
users, the dba who was monitoring the server used some quite
demonstrative language about "more than 100 connections" showing on
his screen, Yes, 100 connections for ten users. Each of those
connections must have application roles enabled properly and enabling
them is a pain. Even that would be OK if these connections, and when
and how they are created were documented. TTBOMK they are not
documented (maybe because they are unknown) and their creation seems
to be erratic. That is, a connection that was not required for a
listbox on Tuesday, may be required on Wednesday. It's difficult to
program around weekdays. I did complete a major application as ADP
with approles. But the hours piled up, maybe twice or even three times
what would have been required normally. For list and combo boxes I
used strings, created with ... guess what ... the GetString function!
Right now I am working on an ADP. Why? because there are only a few
users and the application gives them total control of all their data,
so a new ADP giving them total control of all their data is redundant,
and I hope, harmless. And the users are very sophisticated and know
the responsibility for any bad behavior on their part is theirs alone.
I'll fix it, if asked, but they will pay.

Addendum: ADPs provide a very simple way of interacting with Internet
Enabled SQL Servers. I could send you a less than one meg ADP, and you
and I could both work on an SQL DB in South Africa, anywhere in the
world, WITH all the protections of locking etc, and the beautiful
Access reports available to us. All you need is Access and an Internet
connection. This is amazingly powerful and universally ignored. Oh
well, it's also lucrative, especially when I'm the only one doing it.
Security? It requires a USERID and a Password. They can be encrypted.
The server has super Security software and hardware surrounding it.
Can it be broken? Probably. Will it be? I have a database on my
(rented) server. The challenge for two years has been, break in, and
in the table called Dog, create a new record and enter your name
there. So far, the table is bare.

And so, the poor dog has none.

[Rich P]

Re: MsAccess to SqlServer: sp_setapprole problem

Nothing is completely consistent and reliable. In its prime days -
Access was the most robust/consistent/reliable easy to use product on
the market - far outperforming anything that was DOS based (like
DBase3+, RBase, ...) -- But as corporate environments evolved around
data centric paradigms - Access began to fall short - as you are
experiencing. If you want a significantly more
robust/consistent/reliable system you need to step up to the newer
generation of data management systems which would be the .Net
environment.

Rich

*** Sent via Developersdex http://www.developersdex.com ***