Refresh link to password protected database

[Vensia]

Dear all,

I'm using the following code to refresh link :

Set db = CurrentDb
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink
If Err <> 0 Then
RefreshLinks = False
DoCmd.Hourglass False
Exit Function
End If
End If
Next tdf

But the above code doesn't work for password protected database.
How should I modify the code ?
Thanks.

Vensia

 

[Guest]

Hi Vensia,

Try one of the following two functions, depending on whether you want to
hard code the DB password, or pass it into the function as a required
parameter:


Function RefreshLinks(strFileName As String) As Boolean
' Input: Path to password protected DB (uses hardcoded password = nero)

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = "MS Access;PWD=nero;DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink
If Err <> 0 Then
RefreshLinks = False
DoCmd.Hourglass False
Exit Function
End If
End If
Next tdf


End Function


Function RefreshLinks2(strPassword As String, _
strFileName As String)
' Inputs: Password and path to password protected DB
' Example: Call RefreshLinks2("nero","C:\Codescratch\test.mdb")

Dim db As DAO.Database
Dim tdf As DAO.TableDef

strPassword = "MS Access;PWD=" & strPassword & ""

Set db = CurrentDb
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = "" & strPassword & ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink
If Err <> 0 Then
RefreshLinks2 = False
DoCmd.Hourglass False
Exit Function
End If
End If
Next tdf

End Function

*************End Code******************


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Vensia]

Hello Tom,

Thanks for your help.
Can I know whether a database has password protected or not ?
For new installation, I want the database is protected with password.
But for the existing installation, the database has not been protected with
password.
Can I write a refresh link function which can run well on both types of
database (protected and unprotected) ?
Thanks.

Vensia

 

[Guest]

Hi Vensia,

For new installation, I want the database is protected with password.
But for the existing installation,...

For new installation of what? Are you writing a commercial software
application? In any case, I wouldn't bother with a database password,
because these are very easy to crack (there are free tools available on the
internet, if you just know where to look). If security is an issue for you,
then you should be depending on Windows Security, or moving your data to a
true client/server database, such as SQL Server or Oracle. Database passwords
only raise the bar a teensy-weensy bit.

To answer your question, you can try running the following query from the FE
(front-end) application *before* attempting to run your relinking code:

SELECT * FROM MsysObjects

Notice the data in the Connect column when a password is present. The
following query is more succient, but I wanted to give you the first version,
that shows all fields, so that you become familiar with the fields available
in this system table.

SELECT Connect, Database FROM MsysObjects WHERE Connect is not null

or....

SELECT Mid([Connect],15,Len([connect])-InStr([connect],"=")-1) AS [Password],
MsysObjects.Database
FROM MsysObjects
WHERE (((Mid([Connect],15,Len([connect])-InStr([connect],"=")-1)) Is Not
Null));


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Vensia]

Hello Tom,

Thanks for your help.
Can I know whether a database has password protected or not ?
For new installation, I want the database is protected with password.
But for the existing installation, the database has not been protected with
password.
Can I write a refresh link function which can run well on both types of
database (protected and unprotected) ?
Thanks.

Vensia

 

[Guest]

See my previous answer to this question here:

http://www.microsoft.com/office/com...cess&mid=6c1da328-253e-4f65-8ffe-b0f39b9e242d


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________