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

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.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSrvr;Database=yourSvrDB;Trusted_Connect ion=Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close

End Sub
Rich

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

[adjo]

On 27 jun, 19:13, Rich P <[e-mail]:

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.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSrvr;Database=yourSvrDB;Trusted_Connect ion=Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close

End Sub
Rich

*** Sent via Developersdexhttp://www.developersdex.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]

On 27 jun, 17:15, adjo <[e-mail]:

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

On Jun 28, 7:02*am, adjo <[e-mail]:

On 27 jun, 17:15, adjo <[e-mail]:

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

adjo <ad*****@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]

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]" <[e-mail]>
wrote:

adjo <[e-mail]:

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

lyle fairfield <ly************@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]

On Jun 29, 12:09*am, "Tony Toews [MVP]" <[e-mail]>
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]

On Jun 29, 12:09*am, "Tony Toews [MVP]" <[e-mail]>
wrote:

lyle fairfield <[e-mail]:

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.GetString(StringFormat, 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 PersistFormatEnum 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.

ActiveConnection

Optional. Either a Variant that evaluates to a valid Connection
object variable name, or a String that contains ConnectionString
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 adOpenForwardOnly.

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
ExecuteOptionEnum 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.GetChildren

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]

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" <ly************@gmail.comwrote in message
news:fd**********************************@25g2000h sx.googlegroups.com...
On Jun 29, 12:09 am, "Tony Toews [MVP]" <[e-mail]>
wrote:

lyle fairfield <[e-mail]:

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.GetString(StringFormat, 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 PersistFormatEnum 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.

ActiveConnection

Optional. Either a Variant that evaluates to a valid Connection
object variable name, or a String that contains ConnectionString
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 adOpenForwardOnly.

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
ExecuteOptionEnum 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.GetChildren

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]

On 28 jun, 14:56, lyle fairfield <[e-mail]:

On Jun 28, 7:02*am, adjo <[e-mail]:

On 27 jun, 17:15, adjo <[e-mail]:

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 connectionstring 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 clientenvironment. 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]

On 28 jun, 14:56, lyle fairfield <[e-mail]:

On Jun 28, 7:02*am, adjo <[e-mail]:

On 27 jun, 17:15, adjo <[e-mail]:

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

On Jun 30, 1:02*am, "Larry Linson" <[e-mail]:

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 "algorithmic" 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 ConnectionString$
Dim ProjectName$
ProjectName = Split(CurrentProject.Name, ".")(0)
ConnectionString$ = _
GetSetting(ProjectName, "Startup", "BaseConnectionString")
With CurrentProject
If .IsConnected Then
If .BaseConnectionString <ConnectionString Then _
SaveSetting ProjectName, _
"Startup",
"BaseConnectionString", .BaseConnectionString
Else
If Len(ConnectionString) 0 Then _
.OpenConnection ConnectionString
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 "application 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.Connection or CurrentProject.AccessConnection 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]

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

[lyle fairfield]

On Jun 30, 11:22*am, Rich P <[e-mail]:

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.

I don't think Access ever outperformed DOS programs like FoxPro and
Clipper, nor ever could. A program that cares about the interface can
scarcely outperform a program that does not.
I have always maintained that I could deal with all the data needs of
the world with Clipper, DOS 2.12? and a twenty gig hard drive.
But Access gave us the Windows UI. And we liked that. And so Access
won us over. The fact that MS cajoled or bullied our clients into
using it may have had some influence as well.
But Access is NOT X-Base. For instance it lacks conditional indexes.
And to be efficient, it had to use the Rushmore technology, purchased
with FoxPro.
Today Access does not fall short; it is still a wonderful vehicle for
simple Database applications, especially if we can rid ourselves of
the nonsense found in sample dbs such as Northwinds, and verbose code
posted by MS and by many (but not all) its MVPs. (I never understand
about MVPs. Some are brilliant. Many are ordinary. And a few are drunk
[when they write or speak, at least]. I suppose they are just a cross-
section of the world.)
In the past few months I have been doing more and more .Net. I am not
sure that it is superior to COM applications. But it is newer, and is
something new to sell; selling is MS's chief job. Maybe I'll get to be
convinced as I use it more and more. But right now I'm skeptical. It
does too many things for me, things that I want to do, and control,
myself.
Ninety per cent of the quality of any application rests on the skill
and industry of the Developer. If one is having trouble with Access,
it's unlikely that changing to .Net will cure anything. But the OP is
not having difficulty. He/She asked a very legitimate question about
application roles which reflects both knowledge of Access and
capability. I'm not sure how .Net will help with Application Roles; I
suppose it's easier to control connections with .Net than with Access,
but I've never examined this and so can't be sure.
I don't think the .Net environment is a Database Manigement System. of
course, it may interface with Database Management Systems.

[Rich P]

I started out in this business as a younster fresh out of college with
Dbase3+. When Access came out - it was revolutionary! It still is a
great product, but the sphere of influence has diminished - depending on
the usage. For example, I am a member of a flying club which uses an
Access2000 DB to manage stuff. I was approached about stepping the
system up to sql server/.Net. But since our system is basically single
user with no web interaction (we have a website but fairly static) I
decided that sql server/.net would be overkill and not worth the hassel
of maintaining. But for large scale operations of a corporate business
with 1000s or more clients and a lot of data manipulation/web ops,
scheduled stuff, multiple users, Reporting... the Access paradigm is not
robust enough, not consistent enough, lacks flexibility... Thus, the new
(newer) generation of data management came out -- .Net.

Granted that Access is still the big money maker because it can be used
by a significantly wider range of users than .Net since you don't have
to be a programmer to use Access where .Net requires not only a
programming background but an OOP programming background. This narrows
the market considerably. But like Access, .Net is still much easier to
use than Java. So, for Corporate level Rapid Application Development
(RAD), .Net is pretty hard to beat.

Rich

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

[Tony Toews [MVP]]

"James A. Fortune" <MP*******@FortuneJames.comwrote:

>In my experience Lyle has always been extremely fair -- he criticizes
everything :-).

<chuckle>

Lyle's opinions regarding Access and ADP's are based on

>hard-won firsthand knowledge and I respect and value such opinions
highly. He'll try new technologies and let you know exactly what he
thinks about them and why.

<agreed His postings are frequently thought provoking and open up my vision a bit
more.

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/

[Larry Linson]

Thanks, Lyle, for a very thorough explanation. Most of my work has been
relatively light on calculation and heavy on entry, edit, keeping, and
retrieving the data -- what I call "bookkeeping", though it's not classic
bookkeeping chores, just "keeping books on data".

Larry
"lyle fairfield" <ly************@gmail.comwrote in message
news:1b**********************************@m44g2000 hsc.googlegroups.com...
On Jun 30, 1:02 am, "Larry Linson" <[e-mail]:

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 "algorithmic" 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 ConnectionString$
Dim ProjectName$
ProjectName = Split(CurrentProject.Name, ".")(0)
ConnectionString$ = _
GetSetting(ProjectName, "Startup", "BaseConnectionString")
With CurrentProject
If .IsConnected Then
If .BaseConnectionString <ConnectionString Then _
SaveSetting ProjectName, _
"Startup",
"BaseConnectionString", .BaseConnectionString
Else
If Len(ConnectionString) 0 Then _
.OpenConnection ConnectionString
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 "application 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.Connection or CurrentProject.AccessConnection 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.

[aaron.kempf]

ADP Is consistent and reliable.

JET can't keep it up because it's not designed as a multi-user, ACID-
capable database.

-Aaron
On Jun 30, 8:22*am, Rich P <[e-mail]:

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 Developersdexhttp://www.developersdex.com***

[adjo]

On 30 jun, 20:09, Rich P <[e-mail]:

I started out in this business as a younster fresh out of college with
Dbase3+. *When Access came out - it was revolutionary! *It still is a
great product, but the sphere of influence has diminished - depending on
the usage. *For example, I am a member of a flying club which uses an
Access2000 DB to manage stuff. *I was approached about stepping the
system up to sql server/.Net. *But since our system is basically single
user with no web interaction (we have a website but fairly static) I
decided that sql server/.net would be overkill and not worth the hassel
of maintaining. *But for large scale operations of a corporate business
with 1000s or more clients and a lot of data manipulation/web ops,
scheduled stuff, multiple users, Reporting... the Access paradigm is not
robust enough, not consistent enough, lacks flexibility... Thus, the new
(newer) generation of data management came out -- .Net. *

Granted that Access is still the big money maker because it can be used
by a significantly wider range of users than .Net since you don't have
to be a programmer to use Access where .Net requires not only a
programming background but an OOP programming background. *This narrows
the market considerably. *But like Access, .Net is still much easier to
use than Java. *So, for Corporate level Rapid Application Development
(RAD), .Net is pretty hard to beat.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Although I didn't mean to start the "Access is great vs Access is
garbage" discussion I can't resist to state that I use .Net for a
number of years now and I am a big fan of it; it's a great environment
to build stable applications of all kinds and it's possible to learn
without programming 24x7. But... Access is still my nr 1 as it comes
to prototyping, creating a local small datacentric app or creating a
frontend on a backend like Sqlserver or Oracle when there's not much
time. And that is the case more often as you would like it to be.... A
Belgium firm once told me their price for an app was 35% when they
could use Access versus using VB at the time. Of course it depends on
the circumstances and the skills of the developper, but in my case the
figures are still about the same when I can choose between Access
and .Net tools. So IMO just saying that ''Access is rubbish" says more
about the speaker than about the product. There´s more than just the
beauty of the lines of code that makes a product usable or not.

[adjo]

On 30 jun, 14:21, lyle fairfield <[e-mail]:

On Jun 30, 1:02*am, "Larry Linson" <[e-mail]:

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 "application 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.

...

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.

Okay. I conclude you Access in a way I like to do as well. Although it
´s not a perfect product it may function allright in a number of
situations and is a wonderfull tool for development.
My conclusion is that sp_setapprole is THE mechanism for my problem,
but that it's not possible to use it in a good way from Access. For
that reason I think I will not use the "integrated security" way in
the project I do now, but go back to the wellknown "1 useraccount and
secure the mde" approach. I still have to do some research about the
performance effects when using local account names (instead of
loginnames on the server) but I think this will do the job as well.
And no... I won't get a price for the architecture of the solution but
that's not my goal now.

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

Wow. That's new to me. Quite interesting for certain circumstances.
And good luck with your nameless dog!