CODEWITHSUNDEEP
ms-accessvb6escaping
Carl Onager
Carl Onager

Reputation: 4122

How to escape new Microsoft Access database password when programatically changing it?

I need to change an Access database password programmatically and I'm using this code (simplified for example):

Dim adoCn As ADODB.Connection
Set adoCn = New ADODB.Connection
adoCn.Mode = adModeShareExclusive
adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb";JET OLEDB:Database Password=TEST;"
adoCn.Execute "ALTER DATABASE PASSWORD p@ssword TEST;"
adoCn.Close

This is falling over on the Execute line with an Automation error - Unspecified error.

It appears, after a bit of testing, that only the £ and _ symbols can be used in the password as any other symbol - $%^&*()+?!@ - throws an error.

Is there a way to escape the password so that it can accept other characters? I've tried both 'p@ssword' and [p@ssword] but neither works.

Upvotes: 1

Views: 235

Answers (1)

Bob77
Bob77

Reputation: 13267

In Jet SQL the grave accent character can be used as an identifier escape or quote around names of tables, fields, probably even users and groups, but it also works for passwords in ALTER DATABASE PASSWORD SQL statements.

ALTER DATABASE PASSWORD `!@#$%^&*(` NULL

We get used to the bracket [] quoting but quite a few SQL dialects are happy with, prefer, or even require the grave accent. I think Installer database SQL is one that requires it and won't accept brackets.

Upvotes: 3

Related Questions