Few things to notice about BE passorwds (1 Viewer)

smig

Registered User.
Local time
Today, 12:55
Joined
Nov 25, 2009
Messages
2,209
few things you should be aware when you try to use VBA to Link or Connect to a password protected BE db.

1. Password can't be declared as string!!! It should be a variant (Or nothing at all)
2. Code that use password is very sensitive to extra spaces !!! Look carefully for any space before or after the password.

This will work:
Code:
tdf.Connect = "MS Access;PWD=" & FilePassword & ";DATABASE=" & FileName & ""

Set DB = OpenDatabase(FileName, False, False, "MS Access; PWD=" & FilePassword & "")

This will not (Notice the extra spaces before or after the password):
Code:
tdf.Connect = "MS Access;PWD=" & FilePassword & " ;DATABASE=" & FileName & ""

Set DB = OpenDatabase(FileName, False, False, "MS Access; PWD= " & FilePassword & " ")
 

isladogs

MVP / VIP
Local time
Today, 10:55
Joined
Jan 14, 2017
Messages
18,186
In reply to the above

few things you should be aware when you try to use VBA to Link or Connect to a password protected BE db.

1. Password can't be declared as string!!! It should be a variant (Or nothing at all)
2. Code that use password is very sensitive to extra spaces. Look carefully for any space before or after the password.

1. Not true. It should be a string.
Using a variant is a poor choice as it forces Access to determine the appropriate choice.
Not declaring it as anything means Access will default to variant.
Not declaring it at all is even worse

See the code in my example database List all tables in external databases

2. True. Any Access syntax needs to be followed accurately. It should be obvious why it won't work in the case of passwords

3. Also there shouldn't be a space after the semicolon in ;PWD=
 

smig

Registered User.
Local time
Today, 12:55
Joined
Nov 25, 2009
Messages
2,209
In reply to the above



1. Not true. It should be a string.
Using a variant is a poor choice as it forces Access to determine the appropriate choice.
Not declaring it as anything means Access will default to variant.
Not declaring it at all is even worse

See the code in my example database List all tables in external databases

2. True. Any Access syntax needs to be followed accurately. It should be obvious why it won't work in the case of passwords

3. Also there shouldn't be a space after the semicolon in ;PWD=

1. From my tests declaring the password as String didn't work.
Sure I declare it, but leave Access to default it as variant.

3. There can be or not be a space before the PWD=... It will work either.
 

isladogs

MVP / VIP
Local time
Today, 10:55
Joined
Jan 14, 2017
Messages
18,186
1. That may well have been the case for you. It will clearly work as a variant for the reasons I said before

However, I've been connecting to external databases by both methods for years & always declare passwords as strings.
For example

Code:
Dim STR_PASSWORD As String
If STR_PASSWORD <> "" Then
   tdf.Connect = "MS Access;PWD=" & STR_PASSWORD & ";DATABASE=" & CurrentDirectory & CurrentDatabase & "_be.accdb"
Else
   tdf.Connect = ";DATABASE=" & CurrentDirectory & CurrentDatabase & "_be.accdb"
End If

Code:
Dim strPw As String
If strPw <> "" Then 'password protected database
   Set db = OpenDatabase(strPath, False, False, ";pwd=" & strPw)
Else 'no password needed
   Set db = OpenDatabase(strPath, False, False)
End If

If you wanted to insert the full connect 'string' as a variable, help files will tell you to use a variant because that covers all bases. However the database password itself can only be a string of up to 20 characters

3. That may well be true but you were talking about being precise with syntax :rolleyes:
 
Last edited:

smig

Registered User.
Local time
Today, 12:55
Joined
Nov 25, 2009
Messages
2,209
1. That may well have been the case for you. It will clearly work as a variant for the reasons I said before

However, I've been connecting to external databases by both methods for years & always declare passwords as strings.
For example

Code:
Dim STR_PASSWORD As String
If STR_PASSWORD <> "" Then
   tdf.Connect = "MS Access;PWD=" & STR_PASSWORD & ";DATABASE=" & CurrentDirectory & CurrentDatabase & "_be.accdb"
Else
   tdf.Connect = ";DATABASE=" & CurrentDirectory & CurrentDatabase & "_be.accdb"
End If

Code:
Dim strPw As String
If strPw <> "" Then 'password protected database
   Set db = OpenDatabase(strPath, False, False, ";pwd=" & strPw)
Else 'no password needed
   Set db = OpenDatabase(strPath, False, False)
End If

If you wanted to insert the full connect 'string' as a variable, help files will tell you to use a variant because that covers all bases. However the database password itself can only be a string of up to 20 characters

3. That may well be true but you were talking about being precise with syntax :rolleyes:

3. :D
I just talked about the password thing :)
 

Users who are viewing this thread

Top Bottom