VBA connection to Access using ADODB, to include password

[baitmaster]

Good morning all, I have the following piece of code that I use to connect to Access Databases:

Sub connectionOpen()
Set adConn = New ADODB.Connection
With adConn
   .CursorLocation = adUseServer
   .ConnectionTimeout = 500
   .Provider = "Microsoft.ACE.OLEDB.12.0"
   .ConnectionString = "Data Source=" & strPathToDB & ";"
   .Open
   .CommandTimeout = 500
End With
End Sub

I want to apply password protection to the database, but am unsure how to include the password when updating my code. I've tried various things with mixed error messages:

   .ConnectionString = "Data Source=" & strPathToDB & "; password = myPassword"

run-time error '-2147217843 (80040e4d)': Cannot start your application. The workgroup information file is missing or opened exclusively by another user

   .ConnectionString = "Data Source=" & strPathToDB & "; database password = myPassword"

run-time error '-2147467259 (80004005)': could not find installable ISAM

   .ConnectionString = "Data Source=" & strPathToDB & "; Jet OLEDB:Database Password = myPassword"

run-time error '-2147217843 (80040e4d)': not a valid password

note, "myPassword" represents the actual password and not e.g. a variable. I've been using Access connection strings - ConnectionStrings.com as a general guide for this update

 

[RoryA]

I assume you've read the notes on that page about password problems and made sure to follow them?

 

[baitmaster]

I think so. I'm using MS Office Pro Plus 2010, but the header states "Database (Access 2007-2010)" and I can't confirm what level of encryption it's got. The password is a simple text string with no special characters.

As that page is not about the link between Excel and Access I'm trying to guess what the correct syntax is for this task, so I'm not sure exactly where to look for the problem. My suspicion is that my 1st and 3rd attempts are nearest the mark

Thanks

 

[baitmaster]

update

I was about to write this:
"Still stuck. Reckon the 3rd approach above is correct so unable to explain why it's not working...

I missed the ";" at the end of the connection string but that hasn't changed anything (for any of the options).

It's a .accdb file created in Access 2010; Options > General > default file format for blank document = Access 2007. Client Settings > encryption method = "use legacy encryption"
"

then had a brainwave... although I'd adjusted the encryption method, I already had encryption on at the time, which wasn't changed by just changing the setting. Switching off and on again finally fixed the problem - who'd a thought that works for encryption too?!

Thanks for the pointer Rory, got there in the end