|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I am using ADOX to link SQL server tables to my MDB front end. I have been
trying to find a way to get Jet to cache the user/password info for the
connection *only* for the current Access session. In other words, what I'd
like to have happen is
(1) App starts up, prompts user for uid/pwd
(2) App modifies the connection string property for the links to pass the
UID and PWD along to the linked tables, setting "Jet OLEDB:Cache Link
Name/Password" to false to prevent Jet from keeping the UID/PWD in the
connections string.
(3) The linked tables are accessed using that username and password for the
rest of the time that Access is open.
(4) Next time Access is opened, the tables cannot be opened unless I
explicitly refresh the uid/pwd.
Steps 1-3 are no problem. However, I can't find a way to get Access to
forget the cached username and password info between sessions. I could
programmatically remove it when shutting down Access, but this doesn't help
if Access terminates abnormally.
The following is the code I'm using to build the links. Can anybody point
me towards a solution?
Many thanks!
Randy
------
'Sub links all tables in the given SQL Server database. Throws errors.
Sub BuildLinks(strServerName As String, strDBName As String, bUseNTAuth As
Boolean, _
strUsername As String, strPW As String)
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim cnndata As New ADODB.Connection
Dim rsRemoteTables As ADODB.Recordset
Dim lngProcessedTables As Long
Dim strConnect As String
Dim strRemoteConnect As String
Dim strTableName As String
'Prepare a catalog of the current database
cat.ActiveConnection = CurrentProject.Connection
'Use cnndata to get a list of tables in the remote DB
strConnect = "driver={SQL Server};server=" & strServerName
If Not bUseNTAuth Then strConnect = strConnect &
";Trusted_Connection=No;uid=" & strUsername & ";pwd=" & strPW
strConnect = strConnect & ";database=" & strDBName
With cnndata
.ConnectionString = strConnect
.ConnectionTimeout = 30
.Properties("Persist Security Info") = False
.Open
End With
'Open a tables schema recordset to list the tables
Set rsRemoteTables = cnndata.OpenSchema(adSchemaTables)
lngProcessedTables = 0
'Loop through them
With rsRemoteTables
Do While Not .EOF
lngProcessedTables = lngProcessedTables + 1
If .Fields("TABLE_TYPE") = "TABLE" Or .Fields("TABLE_TYPE") =
"VIEW" Then
'Here's one to link
strRemoteConnect = "ODBC;driver={SQL Server};server=" &
strServerName & _
";DATABASE=" & strDBName
If bUseNTAuth Then
strRemoteConnect = strRemoteConnect &
";Trusted_Connection=Yes"
Else
strRemoteConnect = strRemoteConnect &
";Trusted_Connection=No;uid=" & strUsername & ";pwd=" & strPW
End If
Set tbl = New ADOX.Table
strTableName = .Fields("TABLE_NAME")
If Left(strTableName, 1) = " " Then strTableName = "_" &
Mid(strTableName, 2, 99)
tbl.Name = strTableName
Set tbl.ParentCatalog = cat
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") =
strRemoteConnect
tbl.Properties("Jet OLEDB:Remote Table Name") =
..Fields("TABLE_NAME")
tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False
cat.Tables.Append tbl
'Refresh the link (per KB276035)
'tbl.Properties("Jet OLEDB:Link Provider String") =
strRemoteConnect
End If
.MoveNext
Loop
End With
cnndata.Close
Set cnndata = Nothing
Exit Sub
End Sub
|
|
|