Need help with split databases and passwords please

[carriolan]

Hi

I have got split databases and use the coding below to auto reconnect
them on install. How do I amend this coding to allow for the backend
database being password protected?

Regards
Carriolan


Option Compare Database

Function Reconnect()
'**************************************************************
'* START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'* AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'* WHEN THE 'DATA.MDB' AND THE 'PRG.MDB'
'* ARE IN THE SAME DIRECTORY!!!
'* PROGRAMMING BY PETER VUKOVIC, Germany
'* (e-mail address removed)
'* ************************************************************
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
'*************************************************************
'* RECOGNIZE THE PATH *
'*************************************************************
For i = Len(db.Name) To 1 Step -1
If Mid(db.Name, i, 1) = Chr(92) Then
path = Mid(db.Name, 1, i)
'MsgBox (path)
Exit For
End If
Next
'*************************************************************
'* CHANGE THE PATH AND CONNECT AGAIN *
'*************************************************************
For i = 0 To db.tabledefs.Count - 1
If db.tabledefs(i).connect <> " " Then
source = Mid(db.tabledefs(i).connect, 11)
'Debug.Print source
For j = Len(source) To 1 Step -1
If Mid(source, j, 1) = Chr(92) Then
dbsource = Mid(source, j + 1, Len(source))
source = Mid(source, 1, j)
If source <> path Then
db.tabledefs(i).connect = ";Database=" + path + dbsource
db.tabledefs(i).RefreshLink
'Debug.Print ";Database=" + path + dbsource
End If
Exit For
End If
Next
End If
Next
End Function

 

[George Nicholson]

strPassword = "Shazam!"
db.tabledefs(i).connect = ";DATABASE=" & path & ";Pwd=" & strPassword

Here is what I use to try and retrieve password info from existing links
(file location is more likely to have changed than password).

Private Function ExtractPasswordFromConnectString(strTestTable As String) As
String
' Purpose: parse the password from the connect string of a specified
table
' In: Name of linked table, i.e. where Len(TableDef.Connect)>0.
' Out: Return Value - the password, or an empty string if no password

On Error GoTo ErrHandler
Dim iStart As Integer
Dim iEnd As Integer
Dim strSource As String
Dim strPwd As String

strSource = CurrentDb.TableDefs(strTestTable).Connect
iStart = Nz(InStr(1, strSource, "PWD"), 0)
If iStart > 0 Then
' istart = the P in PWD. The actual password starts 4 characters
later.
iStart = iStart + 4
iEnd = Nz(InStr(iStart, strSource, ";"), 0)
If iEnd = 0 Then iEnd = Len(strSource)
strPwd = Mid$(strSource, iStart, iEnd - iStart)
End If

ExtractPasswordFromConnectString = strPwd
ExitHere:
On Error GoTo 0
Exit Function
ErrHandler:
Select Case Err
Case Else
Call ErrorLog(mModuleName & ":
ExtractPasswordFromConnectString")
Resume ExitHere
End Select
End Function

HTH,

 

[carriolan]

Hi George
Great! I'll give it a go thanks.