By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,155 Members | 2,563 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,155 IT Pros & Developers. It's quick & easy.

DSN-less ODBC connection doesn't remember PW??

P: n/a
If I connect via MS Access's File|Get External Data|Link Tables and
set RememberPassword=True, the resulting link's .Connect contains UID=
and PWD=
and those values persist even after tha app is closed and reopened.
OTOH if I do something like...

Set myTD = CurrentDB.TableDefs("tblAbc")
With myTD
.Name = curName
.Connect = "ODBC;" & ";UID=" & myUserID & ";PWD=" & myPassword &
";" & (other connectin info)
.SourceTableName = "Whatever"
End With
....the connection gets made and works, but if the app is shut down,
and the link is opened; the DB prompts for UserID/PW.

When I look at the links .Connect string, it does not appear to
the UserID or PW that was put into it.
Some kind of Attribute? Property?
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
Not clear on when/how you are invoking your link, like at App Startup?
If so (or something like that) aren't UID and Password parameters you
need to pass to your connect string? If so, then you need to store
these params. If security is an issue here is an encryption routine I
put together (with various snipets from here and there and some of my
own stuff) in VBA (which runs fine in Access). You encrypt the password
and then convert it to a hex string for storage. To run your link
routine reverse the process - you convert the password back to binary
(unhex it) and then decrypt. To decrypt just reverse the arguments for
the encryp function (original args are password, key - to decryp just
call same encrypt function except args are key, encrypted password). At
the bottom is an example how to encrypt/decrypt password:

Function Encrypt(ByVal strInput As String, ByVal strKey As String) As
Dim iCount As Long, lngPtr As Long
For iCount = 1 To Len(strInput)
Mid(strInput, iCount, 1) = Chr((Asc(Mid(strInput, iCount, 1))) Xor
(Asc(Mid(strKey, lngPtr + 1, 1))))
lngPtr = ((lngPtr + 1) Mod Len(strKey))
Next iCount
Encrypt = strInput
End Function

Function kSet(n As Integer) As String
Dim ar As Variant
Static i As Integer, str1 As String, str2 As String
ar = Array("a", "b", "c")
If str2 <> "" Then
i = 0
str1 = ""
str2 = ""
End If
If n = 0 Then
str2 = str1
kSet = str2
str1 = str1 & ar(i)
i = i + 1
If i > 2 Then i = 0
n = n - 1
kSet = kSet(n) 'recursive call here - this is mine
End If 'I may have gotten a little carried
End Function 'away here

Function ConvertStringToHex(ByVal sText As String) As String
Dim lCount As Long
Dim sHex As String
Dim sResult As String
Dim lK As Long

sResult = String(2 * Len(sText), vbNull)
lK = 1
For lCount = 1 To Len(sText)
sHex = Hex(Asc(Mid(sText, lCount, 1)))
If Len(sHex) = 1 Then
sHex = "0" & sHex
End If
Mid$(sResult, lK, 2) = sHex
lK = lK + 2

ConvertStringToHex = sResult
End Function

Public Function ConvertStringFromHex(ByVal sText As String) As String
Dim lCount As Long
Dim sChar As String
Dim sResult As String
Dim lLength As Long
Dim nJ As Long

lLength = Len(sText)
sResult = String(lLength / 2, vbNull)
nJ = 1
For lCount = 1 To lLength Step 2
Mid$(sResult, nJ, 1) = Chr(Val("&H" & Mid(sText, lCount, 2)))
nJ = nJ + 1

ConvertStringFromHex = sResult
End Function

To Encrypt password place this function in a form module

Function EncryptPwrd()
str1 = Encrypt(txtPassword, kSet(Len(txtPassword)))
txtPassword = str1
str1 = ConvertStringToHex(str1) 'convert to hexString here
End Function

The purpose of hexing is so you can use the encrypted password in a sql
string - sql doesn't support binary text. However, DAO will write
binary text so you would not have to hex if you wanted to use DAO for
writing the encrypted password somewhere.

To decryp, I would just place the Encrypt call in the connect string -
inline - arguments are reversed here. But first need to unHex password

strPassword = ConvertStringFromHex(strPassword)
strConnect = "...
PWD=" & (Encrypt(kSet(Len(strPassword)), strPassword)) & ";"

here strPassword contains the deHexed-binary password, and args are
reversed for call to Encrypt.

*** Sent via Developersdex ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.