Very Computer

by **Guy Horto** » Sat, 15 Jun 2002 13:06:23

Hi Support,

Ideally I would like use an application role, however, the limitations
documented in Q308312 prevent me from doing so. To get around this I allow
the users to connect to the database using their individual accounts, but
with minimal permissions, and then behind the scenes programmatically
reconnect to the database using an account that does have permission to view
and modify data.

This all works fine except that the Login dialog, presented at application
startup, remembers the credentials of last connected database user which
isn't the individual account the user logged on with, but the privileged
account.

Can anyone advise on how to prevent the user name connection information
from being retained, or alternatively, suggest how the information might be
amended, perhaps on application exit, as the application maintains details
of the original individual account used to connect.

TIA
Guy

by **Brian M. Sockey [M** » Sun, 16 Jun 2002 12:56:58

Hi Guy,

It's unfortunate that SQL Server application roles have so many limitations in ADP files because I think
ADPs would be a great environment for using them. I've been requesting some changes here from the
"powers that be" so hopefully they will improve this area in the future.

Anyway, regarding your immediate question, just run the following line of code before closing the ADP (from
a hidden form, if needed):

Application.CurrentProject.OpenConnection ""

This effectively causes Access to close the connection and remove the existing connection info so it opens
in a disconnected state the next time around.

I hope this helps! If you have additional questions on this topic, please reply to this posting.

Brian M. Sockey
Microsoft Access Developer Support

---------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

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

| Subject: Simple question
| Date: Fri, 14 Jun 2002 12:06:23 +0800
|
| Hi Support,
|
| Ideally I would like use an application role, however, the limitations
| documented in Q308312 prevent me from doing so. To get around this I allow
| the users to connect to the database using their individual accounts, but
| with minimal permissions, and then behind the scenes programmatically
| reconnect to the database using an account that does have permission to view
| and modify data.
|
| This all works fine except that the Login dialog, presented at application
| startup, remembers the credentials of last connected database user which
| isn't the individual account the user logged on with, but the privileged
| account.
|
| Can anyone advise on how to prevent the user name connection information
| from being retained, or alternatively, suggest how the information might be
| amended, perhaps on application exit, as the application maintains details
| of the original individual account used to connect.
|
| TIA
| Guy
|
|
|
|
|
|
|
|

by **Mary Chipma** » Tue, 18 Jun 2002 01:11:25

Why go to all that trouble? I assume you don't want users to directly
modify tables. That is easily remedied by creating views using the
WITH VIEW_METADATA option, denying all permissions on the base tables,
and granting permissions on the views you want the users to have.
Access caches connection information, and any time a user goes into
the connection dialog it gets saved. You can create an "unbound" ADP
by pressing Cancel on the initial connection dialog box when the ADP
is created, writing code to connect and disconnect. However, I'd also
remove the built-in menus and only provide custom menus without the
connection menu item or a user can easily undo all your work.

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

On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

>Hi Support,

>Ideally I would like use an application role, however, the limitations
>documented in Q308312 prevent me from doing so. To get around this I allow
>the users to connect to the database using their individual accounts, but
>with minimal permissions, and then behind the scenes programmatically
>reconnect to the database using an account that does have permission to view
>and modify data.

>This all works fine except that the Login dialog, presented at application
>startup, remembers the credentials of last connected database user which
>isn't the individual account the user logged on with, but the privileged
>account.

>Can anyone advise on how to prevent the user name connection information
>from being retained, or alternatively, suggest how the information might be
>amended, perhaps on application exit, as the application maintains details
>of the original individual account used to connect.

>TIA
>Guy

by **Guy Horto** » Tue, 18 Jun 2002 17:49:55

Hi Mary,

This is fine for views but not so simple when you are using bound forms
based on stored procedures which require direct access to the tables to
allow users to perform updates.

Regards
Guy

> Why go to all that trouble? I assume you don't want users to directly
> modify tables. That is easily remedied by creating views using the
> WITH VIEW_METADATA option, denying all permissions on the base tables,
> and granting permissions on the views you want the users to have.
> Access caches connection information, and any time a user goes into
> the connection dialog it gets saved. You can create an "unbound" ADP
> by pressing Cancel on the initial connection dialog box when the ADP
> is created, writing code to connect and disconnect. However, I'd also
> remove the built-in menus and only provide custom menus without the
> connection menu item or a user can easily undo all your work.

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

> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

> >Hi Support,

> >Ideally I would like use an application role, however, the limitations
> >documented in Q308312 prevent me from doing so. To get around this I
allow
> >the users to connect to the database using their individual accounts, but
> >with minimal permissions, and then behind the scenes programmatically
> >reconnect to the database using an account that does have permission to
view
> >and modify data.

> >This all works fine except that the Login dialog, presented at
application
> >startup, remembers the credentials of last connected database user which
> >isn't the individual account the user logged on with, but the privileged
> >account.

> >Can anyone advise on how to prevent the user name connection information
> >from being retained, or alternatively, suggest how the information might
be
> >amended, perhaps on application exit, as the application maintains
details
> >of the original individual account used to connect.

> >TIA
> >Guy

by **Guy Horto** » Tue, 18 Jun 2002 17:55:11

Hi Brian,

Thanks for your response, Ideally I would like to replace the users
connection info with the details they initially connected with. Is this
possible ie. is the info stored in the registry somewhere?

Regards
Guy

Quote:
> Hi Guy,

> It's unfortunate that SQL Server application roles have so many

limitations in ADP files because I think

Quote:> ADPs would be a great environment for using them. I've been requesting

some changes here from the

Quote:> "powers that be" so hopefully they will improve this area in the future.

> Anyway, regarding your immediate question, just run the following line of

code before closing the ADP (from

Quote:> a hidden form, if needed):

> Application.CurrentProject.OpenConnection ""

> This effectively causes Access to close the connection and remove the

existing connection info so it opens

Quote:> in a disconnected state the next time around.

> I hope this helps! If you have additional questions on this topic, please

reply to this posting.

Quote:
> Brian M. Sockey
> Microsoft Access Developer Support

> ---------------------------------------
> This posting is provided "AS IS" with no warranties, and confers no

rights. You assume all risk for your use.

> --------------------

> | Subject: Simple question
> | Date: Fri, 14 Jun 2002 12:06:23 +0800
> |
> | Hi Support,
> |
> | Ideally I would like use an application role, however, the limitations
> | documented in Q308312 prevent me from doing so. To get around this I
allow
> | the users to connect to the database using their individual accounts,
but
> | with minimal permissions, and then behind the scenes programmatically
> | reconnect to the database using an account that does have permission to
view
> | and modify data.
> |
> | This all works fine except that the Login dialog, presented at
application
> | startup, remembers the credentials of last connected database user which
> | isn't the individual account the user logged on with, but the privileged
> | account.
> |
> | Can anyone advise on how to prevent the user name connection information
> | from being retained, or alternatively, suggest how the information might
be
> | amended, perhaps on application exit, as the application maintains
details
> | of the original individual account used to connect.
> |
> | TIA
> | Guy
> |
> |
> |
> |
> |
> |
> |
> |

by **Geoffrey Barne** » Wed, 19 Jun 2002 13:21:55

Mary,

This problem is similar to one that I posted a few weeks ago, but I never
got a response to it. Yes, you can create an unbound ADP by pressing cancel
in the initial connection dialog, and I have done so. Once and Access XP
ADP gets a connection to a SQL Server, however, it doesn't want to forget
about it. Even if you create it "unbound", it becomes a "bound" project
once somebody gets a SQL Server connection with it.

From that point on, everytime you fire up your ADP, it thows up a
native-Access login dialog. This native dialog is nowhere near as good as
the one that I created myself when I was still using Access 2000. What I
need is someway to keep an XP project unbound forever more so that I can
control the logins myself. From what Brian said, I could (I guess) execute
a ...

Application.CurrentProject.OpenConnection ""

as the application quits every time, but the application could, of course,
quit unexpectedly and that would prevent this unbindng code from running.
Any ideas that you (or Brian) have would be most welcome.

Love your book, by the way...

> Why go to all that trouble? I assume you don't want users to directly
> modify tables. That is easily remedied by creating views using the
> WITH VIEW_METADATA option, denying all permissions on the base tables,
> and granting permissions on the views you want the users to have.
> Access caches connection information, and any time a user goes into
> the connection dialog it gets saved. You can create an "unbound" ADP
> by pressing Cancel on the initial connection dialog box when the ADP
> is created, writing code to connect and disconnect. However, I'd also
> remove the built-in menus and only provide custom menus without the
> connection menu item or a user can easily undo all your work.

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

> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

> >Hi Support,

> >Ideally I would like use an application role, however, the limitations
> >documented in Q308312 prevent me from doing so. To get around this I
allow
> >the users to connect to the database using their individual accounts, but
> >with minimal permissions, and then behind the scenes programmatically
> >reconnect to the database using an account that does have permission to
view
> >and modify data.

> >This all works fine except that the Login dialog, presented at
application
> >startup, remembers the credentials of last connected database user which
> >isn't the individual account the user logged on with, but the privileged
> >account.

> >Can anyone advise on how to prevent the user name connection information
> >from being retained, or alternatively, suggest how the information might
be
> >amended, perhaps on application exit, as the application maintains
details
> >of the original individual account used to connect.

> >TIA
> >Guy

by **Mary Chipma** » Wed, 19 Jun 2002 22:57:46

Yeah, it's a PITA. I've gotten around it by closing any lingering
connections in code at startup and then re-connecting. I think the
code in the book does this (it's been a while), but if not, let me
know and I'll post what I've got.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"

>Mary,

>This problem is similar to one that I posted a few weeks ago, but I never
>got a response to it. Yes, you can create an unbound ADP by pressing cancel
>in the initial connection dialog, and I have done so. Once and Access XP
>ADP gets a connection to a SQL Server, however, it doesn't want to forget
>about it. Even if you create it "unbound", it becomes a "bound" project
>once somebody gets a SQL Server connection with it.

>From that point on, everytime you fire up your ADP, it thows up a
>native-Access login dialog. This native dialog is nowhere near as good as
>the one that I created myself when I was still using Access 2000. What I
>need is someway to keep an XP project unbound forever more so that I can
>control the logins myself. From what Brian said, I could (I guess) execute
>a ...

>Application.CurrentProject.OpenConnection ""

>as the application quits every time, but the application could, of course,
>quit unexpectedly and that would prevent this unbindng code from running.
>Any ideas that you (or Brian) have would be most welcome.

>Love your book, by the way...

>> Why go to all that trouble? I assume you don't want users to directly
>> modify tables. That is easily remedied by creating views using the
>> WITH VIEW_METADATA option, denying all permissions on the base tables,
>> and granting permissions on the views you want the users to have.
>> Access caches connection information, and any time a user goes into
>> the connection dialog it gets saved. You can create an "unbound" ADP
>> by pressing Cancel on the initial connection dialog box when the ADP
>> is created, writing code to connect and disconnect. However, I'd also
>> remove the built-in menus and only provide custom menus without the
>> connection menu item or a user can easily undo all your work.

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

>> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

>> >Hi Support,

>> >Ideally I would like use an application role, however, the limitations
>> >documented in Q308312 prevent me from doing so. To get around this I
>allow
>> >the users to connect to the database using their individual accounts, but
>> >with minimal permissions, and then behind the scenes programmatically
>> >reconnect to the database using an account that does have permission to
>view
>> >and modify data.

>> >This all works fine except that the Login dialog, presented at
>application
>> >startup, remembers the credentials of last connected database user which
>> >isn't the individual account the user logged on with, but the privileged
>> >account.

>> >Can anyone advise on how to prevent the user name connection information
>> >from being retained, or alternatively, suggest how the information might
>be
>> >amended, perhaps on application exit, as the application maintains
>details
>> >of the original individual account used to connect.

>> >TIA
>> >Guy

by **Mary Chipma** » Wed, 19 Jun 2002 23:00:20

Well, yeah -- stored procedures are NEVER updateable. If you want to
deny users direct access to tables while using stored procedures, then
you need to create stored procedures to do the updating, which in
Access is not that easy to do.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Mon, 17 Jun 2002 16:49:55 +0800, "Guy Horton"

>Hi Mary,

>This is fine for views but not so simple when you are using bound forms
>based on stored procedures which require direct access to the tables to
>allow users to perform updates.

>Regards
>Guy

>> Why go to all that trouble? I assume you don't want users to directly
>> modify tables. That is easily remedied by creating views using the
>> WITH VIEW_METADATA option, denying all permissions on the base tables,
>> and granting permissions on the views you want the users to have.
>> Access caches connection information, and any time a user goes into
>> the connection dialog it gets saved. You can create an "unbound" ADP
>> by pressing Cancel on the initial connection dialog box when the ADP
>> is created, writing code to connect and disconnect. However, I'd also
>> remove the built-in menus and only provide custom menus without the
>> connection menu item or a user can easily undo all your work.

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

>> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

>> >Hi Support,

>> >Ideally I would like use an application role, however, the limitations
>> >documented in Q308312 prevent me from doing so. To get around this I
>allow
>> >the users to connect to the database using their individual accounts, but
>> >with minimal permissions, and then behind the scenes programmatically
>> >reconnect to the database using an account that does have permission to
>view
>> >and modify data.

>> >This all works fine except that the Login dialog, presented at
>application
>> >startup, remembers the credentials of last connected database user which
>> >isn't the individual account the user logged on with, but the privileged
>> >account.

>> >Can anyone advise on how to prevent the user name connection information
>> >from being retained, or alternatively, suggest how the information might
>be
>> >amended, perhaps on application exit, as the application maintains
>details
>> >of the original individual account used to connect.

>> >TIA
>> >Guy

by **Geoffrey Barne** » Thu, 20 Jun 2002 00:31:22

Your book focused on Access 2000, but did you ever update it for Access
2002/XP? The reason I ask is that the ADP startup behavior has changed in
Access XP. It now throws up that native login dialog immediately upon ADP
startup, well before the AutoExec macro could initiate a RunCode procedure.
So I can't think of anyway to close any connections at startup, becuase
Access XP gets there first, before any VBA code can run.

Any ideas?

> Yeah, it's a PITA. I've gotten around it by closing any lingering
> connections in code at startup and then re-connecting. I think the
> code in the book does this (it's been a while), but if not, let me
> know and I'll post what I've got.

> -- Mary
> MCW Technologies
> http://www.mcwtech.com

> On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"

> >Mary,

> >This problem is similar to one that I posted a few weeks ago, but I never
> >got a response to it. Yes, you can create an unbound ADP by pressing
cancel
> >in the initial connection dialog, and I have done so. Once and Access XP
> >ADP gets a connection to a SQL Server, however, it doesn't want to forget
> >about it. Even if you create it "unbound", it becomes a "bound" project
> >once somebody gets a SQL Server connection with it.

> >From that point on, everytime you fire up your ADP, it thows up a
> >native-Access login dialog. This native dialog is nowhere near as good
as
> >the one that I created myself when I was still using Access 2000. What I
> >need is someway to keep an XP project unbound forever more so that I can
> >control the logins myself. From what Brian said, I could (I guess)
execute
> >a ...

> >Application.CurrentProject.OpenConnection ""

> >as the application quits every time, but the application could, of
course,
> >quit unexpectedly and that would prevent this unbindng code from running.
> >Any ideas that you (or Brian) have would be most welcome.

> >Love your book, by the way...

> >> Why go to all that trouble? I assume you don't want users to directly
> >> modify tables. That is easily remedied by creating views using the
> >> WITH VIEW_METADATA option, denying all permissions on the base tables,
> >> and granting permissions on the views you want the users to have.
> >> Access caches connection information, and any time a user goes into
> >> the connection dialog it gets saved. You can create an "unbound" ADP
> >> by pressing Cancel on the initial connection dialog box when the ADP
> >> is created, writing code to connect and disconnect. However, I'd also
> >> remove the built-in menus and only provide custom menus without the
> >> connection menu item or a user can easily undo all your work.

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

> >> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

> >> >Hi Support,

> >> >Ideally I would like use an application role, however, the limitations
> >> >documented in Q308312 prevent me from doing so. To get around this I
> >allow
> >> >the users to connect to the database using their individual accounts,
but
> >> >with minimal permissions, and then behind the scenes programmatically
> >> >reconnect to the database using an account that does have permission
to
> >view
> >> >and modify data.

> >> >This all works fine except that the Login dialog, presented at
> >application
> >> >startup, remembers the credentials of last connected database user
which
> >> >isn't the individual account the user logged on with, but the
privileged
> >> >account.

> >> >Can anyone advise on how to prevent the user name connection
information
> >> >from being retained, or alternatively, suggest how the information
might
> >be
> >> >amended, perhaps on application exit, as the application maintains
> >details
> >> >of the original individual account used to connect.

> >> >TIA
> >> >Guy

by **Mary Chipma** » Thu, 20 Jun 2002 05:11:13

No, never did update the darn book. IMO they totally messed up the UI
for ADP's in 2002 and didn't deliver on unbound ADO recordsets, so we
had little interest in updating the book. Maybe the next release will
be better. That stupid dialog is just a case in point of the messed-up
UI. The user basically is forced to press cancel or OK, and then your
code can run. FWIW, don't use an autoexec macro--just put your code in
your startup form.

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

On Tue, 18 Jun 2002 15:31:22 GMT, "Geoffrey Barnes"

>Your book focused on Access 2000, but did you ever update it for Access
>2002/XP? The reason I ask is that the ADP startup behavior has changed in
>Access XP. It now throws up that native login dialog immediately upon ADP
>startup, well before the AutoExec macro could initiate a RunCode procedure.
>So I can't think of anyway to close any connections at startup, becuase
>Access XP gets there first, before any VBA code can run.

>Any ideas?

>> Yeah, it's a PITA. I've gotten around it by closing any lingering
>> connections in code at startup and then re-connecting. I think the
>> code in the book does this (it's been a while), but if not, let me
>> know and I'll post what I've got.

>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com

>> On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"

>> >Mary,

>> >This problem is similar to one that I posted a few weeks ago, but I never
>> >got a response to it. Yes, you can create an unbound ADP by pressing
>cancel
>> >in the initial connection dialog, and I have done so. Once and Access XP
>> >ADP gets a connection to a SQL Server, however, it doesn't want to forget
>> >about it. Even if you create it "unbound", it becomes a "bound" project
>> >once somebody gets a SQL Server connection with it.

>> >From that point on, everytime you fire up your ADP, it thows up a
>> >native-Access login dialog. This native dialog is nowhere near as good
>as
>> >the one that I created myself when I was still using Access 2000. What I
>> >need is someway to keep an XP project unbound forever more so that I can
>> >control the logins myself. From what Brian said, I could (I guess)
>execute
>> >a ...

>> >Application.CurrentProject.OpenConnection ""

>> >as the application quits every time, but the application could, of
>course,
>> >quit unexpectedly and that would prevent this unbindng code from running.
>> >Any ideas that you (or Brian) have would be most welcome.

>> >Love your book, by the way...

>> >> Why go to all that trouble? I assume you don't want users to directly
>> >> modify tables. That is easily remedied by creating views using the
>> >> WITH VIEW_METADATA option, denying all permissions on the base tables,
>> >> and granting permissions on the views you want the users to have.
>> >> Access caches connection information, and any time a user goes into
>> >> the connection dialog it gets saved. You can create an "unbound" ADP
>> >> by pressing Cancel on the initial connection dialog box when the ADP
>> >> is created, writing code to connect and disconnect. However, I'd also
>> >> remove the built-in menus and only provide custom menus without the
>> >> connection menu item or a user can easily undo all your work.

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

>> >> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

>> >> >Hi Support,

>> >> >Ideally I would like use an application role, however, the limitations
>> >> >documented in Q308312 prevent me from doing so. To get around this I
>> >allow
>> >> >the users to connect to the database using their individual accounts,
>but
>> >> >with minimal permissions, and then behind the scenes programmatically
>> >> >reconnect to the database using an account that does have permission
>to
>> >view
>> >> >and modify data.

>> >> >This all works fine except that the Login dialog, presented at
>> >application
>> >> >startup, remembers the credentials of last connected database user
>which
>> >> >isn't the individual account the user logged on with, but the
>privileged
>> >> >account.

>> >> >Can anyone advise on how to prevent the user name connection
>information
>> >> >from being retained, or alternatively, suggest how the information
>might
>> >be
>> >> >amended, perhaps on application exit, as the application maintains
>> >details
>> >> >of the original individual account used to connect.

>> >> >TIA
>> >> >Guy

by **Geoffrey Barne** » Thu, 20 Jun 2002 05:33:47

Well, there's a difference between us, my friend. While I hate macros in
general, and wish there was a way to start directly from a VBA procedure (as
you can with Sub Main() in straight VB), I hate using startup forms more. I
would rather kick off the code, and then let the code control which form
opens first. After all, it may not be the same form every time!

Since there is no way to define a startup procedure, I am forced to use the
decrepit, old AutoExec marco, with only a single RunCode command in it. And
I have to call it Function Main() instead of Sub. But that's how Access
developers get by in this world... we find ways around things to get what we
want.

Thanks for your help...

> No, never did update the darn book. IMO they totally messed up the UI
> for ADP's in 2002 and didn't deliver on unbound ADO recordsets, so we
> had little interest in updating the book. Maybe the next release will
> be better. That stupid dialog is just a case in point of the messed-up
> UI. The user basically is forced to press cancel or OK, and then your
> code can run. FWIW, don't use an autoexec macro--just put your code in
> your startup form.

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

> On Tue, 18 Jun 2002 15:31:22 GMT, "Geoffrey Barnes"

> >Your book focused on Access 2000, but did you ever update it for Access
> >2002/XP? The reason I ask is that the ADP startup behavior has changed
in
> >Access XP. It now throws up that native login dialog immediately upon
ADP
> >startup, well before the AutoExec macro could initiate a RunCode
procedure.
> >So I can't think of anyway to close any connections at startup, becuase
> >Access XP gets there first, before any VBA code can run.

> >Any ideas?

> >> Yeah, it's a PITA. I've gotten around it by closing any lingering
> >> connections in code at startup and then re-connecting. I think the
> >> code in the book does this (it's been a while), but if not, let me
> >> know and I'll post what I've got.

> >> -- Mary
> >> MCW Technologies
> >> http://www.mcwtech.com

> >> On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"

> >> >Mary,

> >> >This problem is similar to one that I posted a few weeks ago, but I
never
> >> >got a response to it. Yes, you can create an unbound ADP by pressing
> >cancel
> >> >in the initial connection dialog, and I have done so. Once and Access
XP
> >> >ADP gets a connection to a SQL Server, however, it doesn't want to
forget
> >> >about it. Even if you create it "unbound", it becomes a "bound"
project
> >> >once somebody gets a SQL Server connection with it.

> >> >From that point on, everytime you fire up your ADP, it thows up a
> >> >native-Access login dialog. This native dialog is nowhere near as
good
> >as
> >> >the one that I created myself when I was still using Access 2000.
What I
> >> >need is someway to keep an XP project unbound forever more so that I
can
> >> >control the logins myself. From what Brian said, I could (I guess)
> >execute
> >> >a ...

> >> >Application.CurrentProject.OpenConnection ""

> >> >as the application quits every time, but the application could, of
> >course,
> >> >quit unexpectedly and that would prevent this unbindng code from
running.
> >> >Any ideas that you (or Brian) have would be most welcome.

> >> >Love your book, by the way...

> >> >> Why go to all that trouble? I assume you don't want users to
directly
> >> >> modify tables. That is easily remedied by creating views using the
> >> >> WITH VIEW_METADATA option, denying all permissions on the base
tables,
> >> >> and granting permissions on the views you want the users to have.
> >> >> Access caches connection information, and any time a user goes into
> >> >> the connection dialog it gets saved. You can create an "unbound" ADP
> >> >> by pressing Cancel on the initial connection dialog box when the ADP
> >> >> is created, writing code to connect and disconnect. However, I'd
also
> >> >> remove the built-in menus and only provide custom menus without the
> >> >> connection menu item or a user can easily undo all your work.

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

> >> >> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

> >> >> >Hi Support,

> >> >> >Ideally I would like use an application role, however, the
limitations
> >> >> >documented in Q308312 prevent me from doing so. To get around this
I
> >> >allow
> >> >> >the users to connect to the database using their individual
accounts,
> >but
> >> >> >with minimal permissions, and then behind the scenes
programmatically
> >> >> >reconnect to the database using an account that does have
permission
> >to
> >> >view
> >> >> >and modify data.

> >> >> >This all works fine except that the Login dialog, presented at
> >> >application
> >> >> >startup, remembers the credentials of last connected database user
> >which
> >> >> >isn't the individual account the user logged on with, but the
> >privileged
> >> >> >account.

> >> >> >Can anyone advise on how to prevent the user name connection
> >information
> >> >> >from being retained, or alternatively, suggest how the information
> >might
> >> >be
> >> >> >amended, perhaps on application exit, as the application maintains
> >> >details
> >> >> >of the original individual account used to connect.

> >> >> >TIA
> >> >> >Guy

by **Mary Chipma** » Thu, 20 Jun 2002 06:00:56

A friend at microsoft pointed out how to open the unbound adp
disconnected, which is kind of a neat trick. When the app opens and
when it closes, write a (function or sub) that consists of the
following:

CurrentProject.OpenConnection "Provider="

This kills that stupid dialog box next time you open. Apparently this
is part of the sample code in NorthwindCS.adp that i never looked at
in the StartSQLServer function.

Re: no Sub Main in Access -- you could always name your startup form
SubMain and open it hidden to achieve the same effect <g>.

--Mary

On Tue, 18 Jun 2002 20:33:47 GMT, "Geoffrey Barnes"

>Well, there's a difference between us, my friend. While I hate macros in
>general, and wish there was a way to start directly from a VBA procedure (as
>you can with Sub Main() in straight VB), I hate using startup forms more. I
>would rather kick off the code, and then let the code control which form
>opens first. After all, it may not be the same form every time!

>Since there is no way to define a startup procedure, I am forced to use the
>decrepit, old AutoExec marco, with only a single RunCode command in it. And
>I have to call it Function Main() instead of Sub. But that's how Access
>developers get by in this world... we find ways around things to get what we
>want.

>Thanks for your help...

>> No, never did update the darn book. IMO they totally messed up the UI
>> for ADP's in 2002 and didn't deliver on unbound ADO recordsets, so we
>> had little interest in updating the book. Maybe the next release will
>> be better. That stupid dialog is just a case in point of the messed-up
>> UI. The user basically is forced to press cancel or OK, and then your
>> code can run. FWIW, don't use an autoexec macro--just put your code in
>> your startup form.

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

>> On Tue, 18 Jun 2002 15:31:22 GMT, "Geoffrey Barnes"

>> >Your book focused on Access 2000, but did you ever update it for Access
>> >2002/XP? The reason I ask is that the ADP startup behavior has changed
>in
>> >Access XP. It now throws up that native login dialog immediately upon
>ADP
>> >startup, well before the AutoExec macro could initiate a RunCode
>procedure.
>> >So I can't think of anyway to close any connections at startup, becuase
>> >Access XP gets there first, before any VBA code can run.

>> >Any ideas?

>> >> Yeah, it's a PITA. I've gotten around it by closing any lingering
>> >> connections in code at startup and then re-connecting. I think the
>> >> code in the book does this (it's been a while), but if not, let me
>> >> know and I'll post what I've got.

>> >> -- Mary
>> >> MCW Technologies
>> >> http://www.mcwtech.com

>> >> On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"

>> >> >Mary,

>> >> >This problem is similar to one that I posted a few weeks ago, but I
>never
>> >> >got a response to it. Yes, you can create an unbound ADP by pressing
>> >cancel
>> >> >in the initial connection dialog, and I have done so. Once and Access
>XP
>> >> >ADP gets a connection to a SQL Server, however, it doesn't want to
>forget
>> >> >about it. Even if you create it "unbound", it becomes a "bound"
>project
>> >> >once somebody gets a SQL Server connection with it.

>> >> >From that point on, everytime you fire up your ADP, it thows up a
>> >> >native-Access login dialog. This native dialog is nowhere near as
>good
>> >as
>> >> >the one that I created myself when I was still using Access 2000.
>What I
>> >> >need is someway to keep an XP project unbound forever more so that I
>can
>> >> >control the logins myself. From what Brian said, I could (I guess)
>> >execute
>> >> >a ...

>> >> >Application.CurrentProject.OpenConnection ""

>> >> >as the application quits every time, but the application could, of
>> >course,
>> >> >quit unexpectedly and that would prevent this unbindng code from
>running.
>> >> >Any ideas that you (or Brian) have would be most welcome.

>> >> >Love your book, by the way...

>> >> >> Why go to all that trouble? I assume you don't want users to
>directly
>> >> >> modify tables. That is easily remedied by creating views using the
>> >> >> WITH VIEW_METADATA option, denying all permissions on the base
>tables,
>> >> >> and granting permissions on the views you want the users to have.
>> >> >> Access caches connection information, and any time a user goes into
>> >> >> the connection dialog it gets saved. You can create an "unbound" ADP
>> >> >> by pressing Cancel on the initial connection dialog box when the ADP
>> >> >> is created, writing code to connect and disconnect. However, I'd
>also
>> >> >> remove the built-in menus and only provide custom menus without the
>> >> >> connection menu item or a user can easily undo all your work.

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

>> >> >> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

>> >> >> >Hi Support,

>> >> >> >Ideally I would like use an application role, however, the
>limitations
>> >> >> >documented in Q308312 prevent me from doing so. To get around this
>I
>> >> >allow
>> >> >> >the users to connect to the database using their individual
>accounts,
>> >but
>> >> >> >with minimal permissions, and then behind the scenes
>programmatically
>> >> >> >reconnect to the database using an account that does have
>permission
>> >to
>> >> >view
>> >> >> >and modify data.

>> >> >> >This all works fine except that the Login dialog, presented at
>> >> >application
>> >> >> >startup, remembers the credentials of last connected database user
>> >which
>> >> >> >isn't the individual account the user logged on with, but the
>> >privileged
>> >> >> >account.

>> >> >> >Can anyone advise on how to prevent the user name connection
>> >information
>> >> >> >from being retained, or alternatively, suggest how the information
>> >might
>> >> >be
>> >> >> >amended, perhaps on application exit, as the application maintains
>> >> >details
>> >> >> >of the original individual account used to connect.

>> >> >> >TIA
>> >> >> >Guy

by **Guy Horto** » Thu, 20 Jun 2002 09:46:31

Mary,

Agree with you on the messed up Access 2002 UI.

Regards
Guy

> No, never did update the darn book. IMO they totally messed up the UI
> for ADP's in 2002 and didn't deliver on unbound ADO recordsets, so we
> had little interest in updating the book. Maybe the next release will
> be better. That stupid dialog is just a case in point of the messed-up
> UI. The user basically is forced to press cancel or OK, and then your
> code can run. FWIW, don't use an autoexec macro--just put your code in
> your startup form.

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

> On Tue, 18 Jun 2002 15:31:22 GMT, "Geoffrey Barnes"

> >Your book focused on Access 2000, but did you ever update it for Access
> >2002/XP? The reason I ask is that the ADP startup behavior has changed
in
> >Access XP. It now throws up that native login dialog immediately upon
ADP
> >startup, well before the AutoExec macro could initiate a RunCode
procedure.
> >So I can't think of anyway to close any connections at startup, becuase
> >Access XP gets there first, before any VBA code can run.

> >Any ideas?

> >> Yeah, it's a PITA. I've gotten around it by closing any lingering
> >> connections in code at startup and then re-connecting. I think the
> >> code in the book does this (it's been a while), but if not, let me
> >> know and I'll post what I've got.

> >> -- Mary
> >> MCW Technologies
> >> http://www.mcwtech.com

> >> On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"

> >> >Mary,

> >> >This problem is similar to one that I posted a few weeks ago, but I
never
> >> >got a response to it. Yes, you can create an unbound ADP by pressing
> >cancel
> >> >in the initial connection dialog, and I have done so. Once and Access
XP
> >> >ADP gets a connection to a SQL Server, however, it doesn't want to
forget
> >> >about it. Even if you create it "unbound", it becomes a "bound"
project
> >> >once somebody gets a SQL Server connection with it.

> >> >From that point on, everytime you fire up your ADP, it thows up a
> >> >native-Access login dialog. This native dialog is nowhere near as
good
> >as
> >> >the one that I created myself when I was still using Access 2000.
What I
> >> >need is someway to keep an XP project unbound forever more so that I
can
> >> >control the logins myself. From what Brian said, I could (I guess)
> >execute
> >> >a ...

> >> >Application.CurrentProject.OpenConnection ""

> >> >as the application quits every time, but the application could, of
> >course,
> >> >quit unexpectedly and that would prevent this unbindng code from
running.
> >> >Any ideas that you (or Brian) have would be most welcome.

> >> >Love your book, by the way...

> >> >> Why go to all that trouble? I assume you don't want users to
directly
> >> >> modify tables. That is easily remedied by creating views using the
> >> >> WITH VIEW_METADATA option, denying all permissions on the base
tables,
> >> >> and granting permissions on the views you want the users to have.
> >> >> Access caches connection information, and any time a user goes into
> >> >> the connection dialog it gets saved. You can create an "unbound" ADP
> >> >> by pressing Cancel on the initial connection dialog box when the ADP
> >> >> is created, writing code to connect and disconnect. However, I'd
also
> >> >> remove the built-in menus and only provide custom menus without the
> >> >> connection menu item or a user can easily undo all your work.

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

> >> >> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"

> >> >> >Hi Support,

> >> >> >Ideally I would like use an application role, however, the
limitations
> >> >> >documented in Q308312 prevent me from doing so. To get around this
I
> >> >allow
> >> >> >the users to connect to the database using their individual
accounts,
> >but
> >> >> >with minimal permissions, and then behind the scenes
programmatically
> >> >> >reconnect to the database using an account that does have
permission
> >to
> >> >view
> >> >> >and modify data.

> >> >> >This all works fine except that the Login dialog, presented at
> >> >application
> >> >> >startup, remembers the credentials of last connected database user
> >which
> >> >> >isn't the individual account the user logged on with, but the
> >privileged
> >> >> >account.

> >> >> >Can anyone advise on how to prevent the user name connection
> >information
> >> >> >from being retained, or alternatively, suggest how the information
> >might
> >> >be
> >> >> >amended, perhaps on application exit, as the application maintains
> >> >details
> >> >> >of the original individual account used to connect.

> >> >> >TIA
> >> >> >Guy

by **Guy Horto** » Thu, 20 Jun 2002 09:56:51

Mary, Geoffrey

Thanks for your help... hopefully the powers that be will make it easier for
us in the next release.

Regards
Guy

"Mary Chipman" <mc...@nomail.please> wrote in message

news:6h7vguoh2kucbe93fk8ikqj56kcr3julbp@4ax.com...

> A friend at microsoft pointed out how to open the unbound adp
> disconnected, which is kind of a neat trick. When the app opens and
> when it closes, write a (function or sub) that consists of the
> following:

> CurrentProject.OpenConnection "Provider="

> This kills that stupid dialog box next time you open. Apparently this
> is part of the sample code in NorthwindCS.adp that i never looked at
> in the StartSQLServer function.

> Re: no Sub Main in Access -- you could always name your startup form
> SubMain and open it hidden to achieve the same effect <g>.

> --Mary

> On Tue, 18 Jun 2002 20:33:47 GMT, "Geoffrey Barnes"
> <spambus...@earthlink.net> wrote:

> >Well, there's a difference between us, my friend. While I hate macros in
> >general, and wish there was a way to start directly from a VBA procedure
(as
> >you can with Sub Main() in straight VB), I hate using startup forms more.
I
> >would rather kick off the code, and then let the code control which form
> >opens first. After all, it may not be the same form every time!

> >Since there is no way to define a startup procedure, I am forced to use
the
> >decrepit, old AutoExec marco, with only a single RunCode command in it.
And
> >I have to call it Function Main() instead of Sub. But that's how Access
> >developers get by in this world... we find ways around things to get what
we
> >want.

> >Thanks for your help...

> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >news:4f4vgusdvl1r1fs5c79d4vo7e725hfkakm@4ax.com...
> >> No, never did update the darn book. IMO they totally messed up the UI
> >> for ADP's in 2002 and didn't deliver on unbound ADO recordsets, so we
> >> had little interest in updating the book. Maybe the next release will
> >> be better. That stupid dialog is just a case in point of the messed-up
> >> UI. The user basically is forced to press cancel or OK, and then your
> >> code can run. FWIW, don't use an autoexec macro--just put your code in
> >> your startup form.

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

> >> On Tue, 18 Jun 2002 15:31:22 GMT, "Geoffrey Barnes"
> >> <spambus...@earthlink.net> wrote:

> >> >Your book focused on Access 2000, but did you ever update it for
Access
> >> >2002/XP? The reason I ask is that the ADP startup behavior has
changed
> >in
> >> >Access XP. It now throws up that native login dialog immediately upon
> >ADP
> >> >startup, well before the AutoExec macro could initiate a RunCode
> >procedure.
> >> >So I can't think of anyway to close any connections at startup,
becuase
> >> >Access XP gets there first, before any VBA code can run.

> >> >Any ideas?

> >> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >> >news:rveuguskopdqcdq1sig998s2k0e9gv4bpb@4ax.com...
> >> >> Yeah, it's a PITA. I've gotten around it by closing any lingering
> >> >> connections in code at startup and then re-connecting. I think the
> >> >> code in the book does this (it's been a while), but if not, let me
> >> >> know and I'll post what I've got.

> >> >> -- Mary
> >> >> MCW Technologies
> >> >> http://www.mcwtech.com

> >> >> On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"
> >> >> <spambus...@earthlink.net> wrote:

> >> >> >Mary,

> >> >> >This problem is similar to one that I posted a few weeks ago, but I
> >never
> >> >> >got a response to it. Yes, you can create an unbound ADP by
pressing
> >> >cancel
> >> >> >in the initial connection dialog, and I have done so. Once and
Access
> >XP
> >> >> >ADP gets a connection to a SQL Server, however, it doesn't want to
> >forget
> >> >> >about it. Even if you create it "unbound", it becomes a "bound"
> >project
> >> >> >once somebody gets a SQL Server connection with it.

> >> >> >From that point on, everytime you fire up your ADP, it thows up a
> >> >> >native-Access login dialog. This native dialog is nowhere near as
> >good
> >> >as
> >> >> >the one that I created myself when I was still using Access 2000.
> >What I
> >> >> >need is someway to keep an XP project unbound forever more so that
I
> >can
> >> >> >control the logins myself. From what Brian said, I could (I guess)
> >> >execute
> >> >> >a ...

> >> >> >Application.CurrentProject.OpenConnection ""

> >> >> >as the application quits every time, but the application could, of
> >> >course,
> >> >> >quit unexpectedly and that would prevent this unbindng code from
> >running.
> >> >> >Any ideas that you (or Brian) have would be most welcome.

> >> >> >Love your book, by the way...

> >> >> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >> >> >news:q3cpguctavth6en0acegu9raps99v76jhb@4ax.com...
> >> >> >> Why go to all that trouble? I assume you don't want users to
> >directly
> >> >> >> modify tables. That is easily remedied by creating views using
the
> >> >> >> WITH VIEW_METADATA option, denying all permissions on the base
> >tables,
> >> >> >> and granting permissions on the views you want the users to have.
> >> >> >> Access caches connection information, and any time a user goes
into
> >> >> >> the connection dialog it gets saved. You can create an "unbound"
ADP
> >> >> >> by pressing Cancel on the initial connection dialog box when the
ADP
> >> >> >> is created, writing code to connect and disconnect. However, I'd
> >also
> >> >> >> remove the built-in menus and only provide custom menus without
the
> >> >> >> connection menu item or a user can easily undo all your work.

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

> >> >> >> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"
> >> >> >> <guy.hor...@microscienta.com> wrote:

> >> >> >> >Hi Support,

> >> >> >> >Ideally I would like use an application role, however, the
> >limitations
> >> >> >> >documented in Q308312 prevent me from doing so. To get around
this
> >I
> >> >> >allow
> >> >> >> >the users to connect to the database using their individual
> >accounts,
> >> >but
> >> >> >> >with minimal permissions, and then behind the scenes
> >programmatically
> >> >> >> >reconnect to the database using an account that does have
> >permission
> >> >to
> >> >> >view
> >> >> >> >and modify data.

> >> >> >> >This all works fine except that the Login dialog, presented at
> >> >> >application
> >> >> >> >startup, remembers the credentials of last connected database
user
> >> >which
> >> >> >> >isn't the individual account the user logged on with, but the
> >> >privileged
> >> >> >> >account.

> >> >> >> >Can anyone advise on how to prevent the user name connection
> >> >information
> >> >> >> >from being retained, or alternatively, suggest how the
information
> >> >might
> >> >> >be
> >> >> >> >amended, perhaps on application exit, as the application
maintains
> >> >> >details
> >> >> >> >of the original individual account used to connect.

> >> >> >> >TIA
> >> >> >> >Guy

by **Guy Horto** » Thu, 20 Jun 2002 12:04:53

Mary, Geoffery,

Thinking a little further ahead, and trying to decide how I want to manage
the application installation and subsequent logon process...

Currently I am upsizing the application from an .mdb to an .adp. Generally
the application executes in single user environment, although, there are a
few low usage multi-user sites, but I believe the SQL Server Desktop engine
(MSDE) will be adequate for our needs.

If the .adp is in a permanently "disconnected" state then I assume I must
provide my own logon/connection process. Ideally I would like the process to
imitate the current .mdb version which just prompts for a username and
password on startup. To do this I must have either 1) prior knowledge of the
data source (server name) or 2) be able to search for a list of available
servers which the user can choose from. Do you know how to achieve 2)?

Is it possible to control how the SQL Server Desktop engine is installed on
the clients machine? Can you set the sa password as part of the install.
Could I use a hardcoded servername?

My initial thoughts on the migration process are to obtain the clients
"data" .mdb, correct any data anomolies, and export the data using DTS to an
.mdf. Is it then possible to load the database programatically onto the
clients server?

Your assistance appreciated

Regards
Guy

"Mary Chipman" <mc...@nomail.please> wrote in message

news:6h7vguoh2kucbe93fk8ikqj56kcr3julbp@4ax.com...

> A friend at microsoft pointed out how to open the unbound adp
> disconnected, which is kind of a neat trick. When the app opens and
> when it closes, write a (function or sub) that consists of the
> following:

> CurrentProject.OpenConnection "Provider="

> This kills that stupid dialog box next time you open. Apparently this
> is part of the sample code in NorthwindCS.adp that i never looked at
> in the StartSQLServer function.

> Re: no Sub Main in Access -- you could always name your startup form
> SubMain and open it hidden to achieve the same effect <g>.

> --Mary

> On Tue, 18 Jun 2002 20:33:47 GMT, "Geoffrey Barnes"
> <spambus...@earthlink.net> wrote:

> >Well, there's a difference between us, my friend. While I hate macros in
> >general, and wish there was a way to start directly from a VBA procedure
(as
> >you can with Sub Main() in straight VB), I hate using startup forms more.
I
> >would rather kick off the code, and then let the code control which form
> >opens first. After all, it may not be the same form every time!

> >Since there is no way to define a startup procedure, I am forced to use
the
> >decrepit, old AutoExec marco, with only a single RunCode command in it.
And
> >I have to call it Function Main() instead of Sub. But that's how Access
> >developers get by in this world... we find ways around things to get what
we
> >want.

> >Thanks for your help...

> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >news:4f4vgusdvl1r1fs5c79d4vo7e725hfkakm@4ax.com...
> >> No, never did update the darn book. IMO they totally messed up the UI
> >> for ADP's in 2002 and didn't deliver on unbound ADO recordsets, so we
> >> had little interest in updating the book. Maybe the next release will
> >> be better. That stupid dialog is just a case in point of the messed-up
> >> UI. The user basically is forced to press cancel or OK, and then your
> >> code can run. FWIW, don't use an autoexec macro--just put your code in
> >> your startup form.

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

> >> On Tue, 18 Jun 2002 15:31:22 GMT, "Geoffrey Barnes"
> >> <spambus...@earthlink.net> wrote:

> >> >Your book focused on Access 2000, but did you ever update it for
Access
> >> >2002/XP? The reason I ask is that the ADP startup behavior has
changed
> >in
> >> >Access XP. It now throws up that native login dialog immediately upon
> >ADP
> >> >startup, well before the AutoExec macro could initiate a RunCode
> >procedure.
> >> >So I can't think of anyway to close any connections at startup,
becuase
> >> >Access XP gets there first, before any VBA code can run.

> >> >Any ideas?

> >> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >> >news:rveuguskopdqcdq1sig998s2k0e9gv4bpb@4ax.com...
> >> >> Yeah, it's a PITA. I've gotten around it by closing any lingering
> >> >> connections in code at startup and then re-connecting. I think the
> >> >> code in the book does this (it's been a while), but if not, let me
> >> >> know and I'll post what I've got.

> >> >> -- Mary
> >> >> MCW Technologies
> >> >> http://www.mcwtech.com

> >> >> On Tue, 18 Jun 2002 04:21:55 GMT, "Geoffrey Barnes"
> >> >> <spambus...@earthlink.net> wrote:

> >> >> >Mary,

> >> >> >This problem is similar to one that I posted a few weeks ago, but I
> >never
> >> >> >got a response to it. Yes, you can create an unbound ADP by
pressing
> >> >cancel
> >> >> >in the initial connection dialog, and I have done so. Once and
Access
> >XP
> >> >> >ADP gets a connection to a SQL Server, however, it doesn't want to
> >forget
> >> >> >about it. Even if you create it "unbound", it becomes a "bound"
> >project
> >> >> >once somebody gets a SQL Server connection with it.

> >> >> >From that point on, everytime you fire up your ADP, it thows up a
> >> >> >native-Access login dialog. This native dialog is nowhere near as
> >good
> >> >as
> >> >> >the one that I created myself when I was still using Access 2000.
> >What I
> >> >> >need is someway to keep an XP project unbound forever more so that
I
> >can
> >> >> >control the logins myself. From what Brian said, I could (I guess)
> >> >execute
> >> >> >a ...

> >> >> >Application.CurrentProject.OpenConnection ""

> >> >> >as the application quits every time, but the application could, of
> >> >course,
> >> >> >quit unexpectedly and that would prevent this unbindng code from
> >running.
> >> >> >Any ideas that you (or Brian) have would be most welcome.

> >> >> >Love your book, by the way...

> >> >> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >> >> >news:q3cpguctavth6en0acegu9raps99v76jhb@4ax.com...
> >> >> >> Why go to all that trouble? I assume you don't want users to
> >directly
> >> >> >> modify tables. That is easily remedied by creating views using
the
> >> >> >> WITH VIEW_METADATA option, denying all permissions on the base
> >tables,
> >> >> >> and granting permissions on the views you want the users to have.
> >> >> >> Access caches connection information, and any time a user goes
into
> >> >> >> the connection dialog it gets saved. You can create an "unbound"
ADP
> >> >> >> by pressing Cancel on the initial connection dialog box when the
ADP
> >> >> >> is created, writing code to connect and disconnect. However, I'd
> >also
> >> >> >> remove the built-in menus and only provide custom menus without
the
> >> >> >> connection menu item or a user can easily undo all your work.

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

> >> >> >> On Fri, 14 Jun 2002 12:06:23 +0800, "Guy Horton"
> >> >> >> <guy.hor...@microscienta.com> wrote:

> >> >> >> >Hi Support,

> >> >> >> >Ideally I would like use an application role, however, the
> >limitations
> >> >> >> >documented in Q308312 prevent me from doing so. To get around
this
> >I
> >> >> >allow
> >> >> >> >the users to connect to the database using their individual
> >accounts,
> >> >but
> >> >> >> >with minimal permissions, and then behind the scenes
> >programmatically
> >> >> >> >reconnect to the database using an account that does have
> >permission
> >> >to
> >> >> >view
> >> >> >> >and modify data.

> >> >> >> >This all works fine except that the Login dialog, presented at
> >> >> >application
> >> >> >> >startup, remembers the credentials of last connected database
user
> >> >which
> >> >> >> >isn't the individual account the user logged on with, but the
> >> >privileged
> >> >> >> >account.

> >> >> >> >Can anyone advise on how to prevent the user name connection
> >> >information
> >> >> >> >from being retained, or alternatively, suggest how the
information
> >> >might
> >> >> >be
> >> >> >> >amended, perhaps on application exit, as the application
maintains
> >> >> >details
> >> >> >> >of the original individual account used to connect.

> >> >> >> >TIA
> >> >> >> >Guy