Originally Posted by
June7
Thanks, that was helpful. I realize now I needed to be thinking about another question entirely!
1. I was able to use Access VBA to create a DSN-less connection to Sybase, including the encrypted password. It looks something like this (sample data used)
Code:
Option Compare Database
Sub Connect_ToSybaseASE()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
With conn
' DSN-less connection using the ODBC driver
.Open "Driver={Adaptive Server Enterprise};" & _
"Server=my-dbserver;" & _
"Port=0000;" & _
"DB=mydb;" & _
"EncryptPassword=1;" & _
"UID=myuser;" & _
"PWD=mypwd;"
If conn.State = adStateOpen Then
MsgBox "Connection was established."
End If
End With
Set conn = Nothing
End Sub
2. The current client's database is set up to use an ODBC DSN, so I don't think the DSN-less connection approach will work unless the application is rewritten, which is not a viable option at this point.
So I think the better question is - What is the best way to re-link the existing Sybase linked tables now that they require an encrypted password that the ODBC DSN does not allow to be saved?
I need something secure, so I don't know if that involves upgrading this mdb to accdb. The database as a whole is currently password-protected, but I don't know whether it is better to (a) create a login form that gets the user id and password from the client, which I would then pass to a VBA function to loop over the Sybase tables to re-link them and provide the encrypted password to the Sybase server or (b) hard-code the uid and password for this particular user (the only one using the application) since it is the same user id/password used to connect to all of the Sybase tables. Also this is a read-only connection so no updating is concerned.
Thanks again for any help. I appreciate your patience as well - I realize this is a simple concept in principle but it seems there are several tricky variables that are tripping me up.
- Back2Access