Connect to password protected Access 2010 database in Excel

[lilp32]

I need to connect to a password protected Access database through Excel.

Everything I've found while searching seems to imply that this is a bug that Microsoft has not fixed. Is this true? The string I am using is

[noparse]Dim cnt as New ADODB.Connection
Dim rst As New ADODB.Recordset
'
cnt.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
"Jet OLEDB:Database Password=pw;"Data Source='MyDB.db'"[/noparse]

The error I get is "Not a valid password".

Any suggestions?

[zmbd]

I'm going to leave this string as is so as not to bugger it:

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\OpenToNetwork\testDB.accdb;Persist Security Info=False;Jet OLEDB:Database Password=123"

Now I haven't tried this out in every situation and I'm using WINXP(MCE)-SP3; however, it seems that the order and the path are importaint. There are also some issues with WIN7/Vista from what I've heard.

[lilp32]

Thanks, but this is the same as what I am using. It still gives me the "invalid password" message. I am using Win7.

[NeoPa]

It may give you the same message Lilp, but it's certainly not the same code - even allowing for the fact the the code you posted could never run as it has an extraneous (") in the middle of what looks like a string value. Presumably that's a posting error rather than anything else otherwise the error you reported would be a VBA compilation error rather than a runtime one.

I would guess from what I see here that the issue is an operability one with Win7/Vista (as Z says). I suggest you search online (using the parameters that define your setup) for any published issues. That's not to say that no-one here necessarily knows the answer, but for such a specific issue I wouldn't hold my breath (especially when the alternative is so straightforward and presumably quick).

[lilp32]

Thanks, and yes I am not sure why there is an extra quotation mark in the middle. I see that the suggestion by zmbd is in a different order and has an additional parameter.

You mentioned a quick and straightforward alternative - do you mean removing the password or is there something else I could do? I don't really consider this a specific issue? This seems like an issue many people would have as many databases are password protected.

[NeoPa]

I was referring to doing a search using parameters specific to your situation. The version of Windows already appears to show as a potential issue, from comments already entered. You're in the best position to do that as you have your system to hand and know which versions of everything you're running. If, as seems likely, this is an interoperationa l issue then you're in the best position to find information about the problem - assuming no-one here has direct experience of your problem (which position I would estimate as highly likely).

This is all assuming that this actually is a specific issue - which is how it appears to me from what information is available here.