Access (Jet) caches and pools connections. If Access thinks
that the connection has not changed, it will continue to use the
old connection. I think that the connection information that is
SAVED by Access is taken from the information Access has and is
using, not directly from the connection string that you provide.
You need to ensure that a NEW connection is created.
Firstly, by deleting the old connection and creating a new
connection, (instead of refreshing an old connection).
Secondly (if that does not work), by using a separate workspace
and jet engine object to create a new connection
Thirdly (if that does not work), by ensuring that there are NO
OPEN CONNECTIONS AT ALL when you create the new connection.
I DO NOT KNOW IF IT IS POSSIBLE TO HAVE TWO DIFFERENT CONNECTIONS
WITH different USER CREDENTIALS OPEN AT THE SAME TIME. I do not
know if the SQL Server supports that (from the same machine), I
do not know if the ODBC driver supports that: I do not know if the
Access connection pooling mechanism supports that. (but it should
be fairly easy to test). Access does support multiple user
credentials against an MDB database, so there is no
immediate reason to expect that it won't work against an ODBC
source.
(david)
"Donald Wisch" <dwisch@srobo.com> wrote in message
news:40b4c82f$0$201$75868355@news.frii.net...[color=blue]
> Well I have a little different scenario to this issue. I am trying to
> create SQL linked tables on the fly from code. It works but here is my
> dilema:-
> 1. We have a full access and read only user for each SQL database.
> 2. I am trying to link to certain SQL tables with either the full
> access or read only UID and PWD. FYI - the rights are reflected in the
> name of the linked table I save in Access. When I run through the code
> it uses the UID and PWD from the last time I linked in a SQL table from
> Access Link Table wizard. The code does have the option for SaveLoginIn
> set to true.
>
> I thought this was related to the ODBC registry setting, so I figured
> out how to reset the last user for the particular ODBC I am using and
> that works fine, but Access does not seem to grasp that and it uses the
> last one I used from the wizard. I really need some help in this
> matter.
>
> Thanks
>
> *** Sent via Developersdex
http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>[/color]
Q823718 Security Update for MDAC: TransferDatabase command changed? 
