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

Editing saved UID and PWD through relinking ODBC tables

P: n/a
I have a number of databases in MS Access 97. Some tables in these
databases are linked to ODBC tables in Oracle9/10. We would like to
change the user name and password on the Oracle tables so each
database in MS Access linking to the tables in Oracle will need to be
relinked with the new credentials.

We would also like to automate this process of dropping the old links
and making a new one through a module that can be run in every
database. Piecing things together from various places on the internet,
this is what we have so far:

Option Compare Database
Option Explicit

'*********** Code Start ************
Const IntAttachedTableType As Integer = 4
Const ALLFILES = "All Files"

Function Replace(ByVal Valuein As String, ByVal WhatToReplace As _
String, ByVal Replacevalue As String) As String
Dim Temp As String, P As Long
Temp = Valuein
P = InStr(Temp, WhatToReplace)
Do While P 0
Temp = Left(Temp, P - 1) & Replacevalue & _
Mid(Temp, P + Len(WhatToReplace))
P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop
Replace = Temp
End Function
Function fRefreshLinks() As Boolean

Dim dbs As Database
Dim rst As Recordset, rstTry As Recordset
Dim tdf As TableDef
Dim strOldConnect As String, strNewConnect As String
Dim strFullLocation As String, strDatabase As String, strMsg As String
Dim objTableDef As TableDef
Dim strTableName As String
Dim strSourceTableName As String
Dim strNewConString As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT MSysObjects.Connect,
MsysObjects.Database, " & _
"MSysObjects.Name from MSysObjects " & _
"WHERE MSysObjects.Type = " & IntAttachedTableType)
If rst.RecordCount <0 Then
rst.MoveFirst
Do
On Error Resume Next
Set rstTry = dbs.OpenRecordset(rst![Name].Value)
If Err = 1 Then
rstTry.Close
Set rstTry = Nothing
Else
On Error GoTo fRefreshLinks_Err
strFullLocation = rst.Name
strDatabase = FileName(strFullLocation)
Set tdf = dbs.TableDefs(rst![Name].Value)
strOldConnect = tdf.Connect
strNewConnect = strOldConnect
'strNewConnect = findConnect(strDatabase, tdf.Name, strOldConnect)
'If strNewConnect = "" Then
'Err.Raise
'Else
strTableName = tdf.Name
strSourceTableName = tdf.SourceTableName
strNewConString = rst("Connect")
strNewConString = Replace(strNewStr, "UID=XXX", "UID=XXX")
strNewConString = Replace(strNewStr, "PWD=XXX", "PWD=XXX")

Set objTableDef = New TableDef
With objTableDef
..Name = strTableName & "Temp"
..SourceTableName = strSourceTableName
..Connect = strNewConString
End With
CurrentDb.TableDefs.Append objTableDef
For Each tdf In dbs.TableDefs
If tdf.Connect = strOldConnect Then
tdf.Connect = strNewConnect
tdf.RefreshLink
End If
Next tdf
dbs.TableDefs.Refresh
'End If
End If
Err = 0
rst.MoveNext
If rst.EOF Then
Exit Do
End If
Loop
End If

fRefreshLinks_End:
Set tdf = Nothing
Set rst = Nothing
Set rstTry = Nothing
fRefreshLinks = True
Exit Function

fRefreshLinks_Err:
fRefreshLinks = False
Select Case Err
Case 3024:

Case Else:
strMsg = "Error Information..." & vbCrLf & vbCrLf
strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
strMsg = strMsg & "Description: " & Err.Description & vbCrLf
strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
MsgBox strMsg, vbOKOnly + vbCritical, "Error"
End Select
Exit Function
End Function

This will create a new link with the same name as the old one with
'temp' appended to the end of it. It should be identical in every
other way to the old link except with the updated username and
password in the connection string.

The trouble we are having is that it will not accept the new
connection string. In the line

strNewConString = rst("Connect")

we pull the old connection string from the recordset created from the
MSysObjects table. We could pull the old string from the tabledefs by
saying

strNewConString = tdf.Connect

instead and the code works flawlessly creating the new link, however
tdf.connect does not contain the username and password information
that MSysObjects does.

Is there something we have done wrong, or is there another way to
write the new connection string into the new tdf?

Jul 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Wow!

What a complicated bit of code!

If there is only one database to connect to you might try something
simpler...

Public Function FixODBCConnection()
Dim sConnection1 As String

sConnection1 = ' set full connection string...

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If Left(tdf.Connect, 4) = "ODBC" Or Left(tdf.Connect, 3) = "DSN"
Then
If InStr(tdf.Connect, "Datase Name") Then
tdf.Attributes = DAO.dbAttachSavePWD
tdf.Connect = sConnection1
tdf.RefreshLink
End If
End If
Next
End Function

Regards,
Tom Bizannes
Microsoft Access Development
Sydney,Australia

Jul 22 '07 #2

P: n/a
Thanks for your help Tom,
that indeed looks like a much simpler solution.

I have a few questions regarding that bit of code though.

The first being what does DAO.dbAttachSavePWD do exactly?
We're looking to specify a new password and user name, so where here
do we tell it what these things are? Does this open a prompt for new
credentials?

Also, in the line "If InStr(tdf.Connect, "Datase Name") Then " Where
you say "Datase Name", I am accurate in assuming that this is the name
of the ORACLE database that we're trying to link to?

The ultimate goal of this module is for it to be placed in any of our
existing Access databases and simply run where it will copy every link
to our ODBC database only altering the PWD and UID for it, and then
dropping the old link.

Jul 23 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.