Update Password Protected Access Table Embedded in Excel

I don't know why it will not accept the password. That's an obstacle you will have to get over yourself.

If you have a connection to the database, it will show up with the Data -> Connections ribbon. Select the connection you want and click on Properties.

Make sure the item that says Refresh data when opening the file on the Usage tab is unchecked.

Go to the Definition tab. Click on the option that says Save Password. There is a problem with this. It will embed the password in clear text in the connection string for all the world to see.

There is a way around this. The connection string is a semicolon-delimited string. You can use the Split Command to split this up into an array. You can change the password component to match the password you want and then recombine the string with the Join Command.

So you store the password in a cell on a very hidden page. You create an open workbook event that first changes the connection string to contain the password, runs the query or connection, and then removes the password.

Make sure you put a password to view the VB project.

This isn't the world's greatest security, but it is enough to thwart the less-able hackers.

I'd offer code, but I deal with Oracle databases. I have no idea what an MS-Access connection string would look like.

This code will at least give you the idea of how to address QueryTables and .Connection

Please Login or Register  to view this content.