DAO ODBC connetion to sql server with access logon

[Linn K B]

Hi,
I have an access front end, and I got one sql server tabel that I
connect to using a linked connection using ODBC. I have created
simelar user logins on sql server and access, so the users only need
to logon once (since TryJetAuth takes care of it).

I now want to execute a stored procedure on sql server from access,
but with the same connection (DSN/Database/user/passwd) as the linked
table(same login as access). What I have come up with is getting the
connect from the TableDefs of the linked tabel, and using the current
user as user, but I cannot get the current password from anywhere
(except input form user). Is there any way to take advantage of the
TryJetAuth when connecting to the sql server from code or to get the
password some other way? I don't want to store the password or
username anywhere, and I don't want to hardcode it, and i need the
connection to be with the current user...

Linn

Some code:

Dim dbs As DAO.database
Dim SQLws As DAO.workspace
Dim SQLQry As DAO.QueryDef
Dim SQLcn As DAO.Connection
Dim SQLpasswd as string
Dim strSQL as string

Set dbs = CurrentDb
dbs = currentDatabase()

strSQL = "Exec SP_SQLTabel"

SQLpasswd = InputBox("Password:")
ConnectionString = dbs.TableDefs("SQLTabel").Properties(4) & ";UID=" &
CurrentUser & ";PWD=" & SQLpasswd

Set SQLws = CreateWorkspace("", CurrentUser, SQLpassord, dbUseODBC)
Set SQLcn = SQLws.OpenConnection("access", dbDriverNoPrompt, ,
ConnectionString)
Set SQLQry = SQLcn.CreateQueryDef("")

SQLQry.ODBCTimeout = 300
SQLQry.Prepare = dbQPrepare
SQLQry.SQL = strSQL
CTime = Now
SQLQry.Execute