In ADPs multiple connections cause problems when application roles are
used as per this quote from
http://support.microsoft.com/kb/308312.
"Unlike with other database objects, Access does not always use the
same connection to retrieve the data source of a subform. Access
frequently (but not always) creates a new connection to SQL Server
just to handle the subform recordset, or to retrieve the linking field
data that connects the subform to the main form. Because this new
connection does not have the application role applied, a permissions
error may be generated if you do not have explicit permissions to the
database object. Unfortunately, this means that there is no reliable
way to use bound subforms when application roles are applied. The only
effective workaround is to have completely unbound subforms, with the
data manipulation handled programmatically. This is the most serious
limitation when using application roles in Access."
I believe that "Access frequently (but not always) creates a new
connection to SQL Server just to handle the subform recordset" is not
the whole story. In my experience Access frequently (but not always)
creates a new connection to SQL Server to handle any implicit Select,
Insert, Update or Delete procedure, including record-based combo-boxes
and list-boxes. It would be helpful if "not always" could be
documented as to when. I have found that "not always" is
unpredictable, varying from day-to-day and server to server.
I have never used application roles with ODBC and can't comment on
that combination.
A different kind of problem caused by multiple connections is the
effect they may have on the application's credibility. When a DBA/SA
sees one hundred five connections from the application to his server
when she knows only seventeen users have the application open he may
be alarmed. And she may communicate that alarm and concern for
efficiency, merited or not, to the people who are making decisions
about buying Access applications.
On May 31, 10:00*pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
Why are you worried about it? *Have you had a single problem that you would
have noticed had you NOT used SQL trace?