Unanswered: close ODBC connection and relink SQL Svr7 tables with new user acct
I've been all over the place but haven't found a discussion that matches what I'm exactly looking for, any help would be greatly appreciated!
I have an Access 2K mdb front-end with a SQL Server 7.0 backend, and am using Mixed Mode authetication. The application runs in a Win Nt/2k environment (no Win9X). I originally linked the SQL Svr tables manually using a DSN that was setup to use SQL Svr authentication and named pipes for the Client Config.
When the application is started the user is presented with a list of DBs to connect to, once selected the linked tables' connect strings are reset with the SERVER, DATABASE, APP, and Trusted_Connection(=Yes), etc. settings that are stored in a local table with the list of DBs. Checking the Current Activity-> Process Info in Enterprise Mngr verifies the user is connected under their NT network account and to the appropriate DB. Allowing the user to select another DB and refreshing the linked tables connection strings works fine. However, if I accept a login name and password so that the user can connect using a SQL Srv account (not a NT account) and try to refresh the table links with the UID, PWD set to the input values, the connection string for the tables in the front-end show as being updated but on the backend it still shows that the user is connected with their NT acct rather than the SQL Svr acct. The only way I can force this "reconnect" is by restarting Access and using the SQL Server acct infomation in the connection string at start up.
Is there a way to reset the ODBC connection so that I can dynamically toggle between a SQL Srv acct and the logged in user's NT acct with out closing out Access. If not, is there an easy way to restart Access so that the desired SQL Svr authentication is used? Thought about storing flag in a local table that gets read at startup but that's not a desireable approach.