How do I escape all special characters in Access Jet SQL?

I'm trying to change a password with a DDL statement like:

CurrentProject.Connection.Execute "ALTER USER barney PASSWORD "[]!@#$%^ oldpassword"

Yes, that's a nasty password, but someone tried something like that. Notice the beginning quote of the password is not part of the sql syntax here. I need something like mysql_real_escape_string() but for VBA. Any hints?

Solution

You seem to have hit upon something: you cannot create a (usable) password containing any of those characters using SQL DDL (note Wikipedia considers this to be SQL DCL).

Below is some code to reproduce a test scenario:

creates a new workgroup (in temp folder)
creates a new database using the workgroup
creates a new table with data
creates a new user with name, password and PID using alphanumeric characters throughout
grants privileges on the table to the user
opens a test connection using the new user's credentials
tests that the user can query the table

The code as posted works fine. However, editing the password in both places (when the user is created and when the test connection is opened) to add a non-alpha character (e.g. a quote) raises an error in one of those places.

On Error Resume Next
Kill Environ$("temp") & "\MyDatabase.mdb"
Kill Environ$("temp") & "\MyWorkgroup.mdw"
On Error GoTo 0

' Create workgroup and db
Dim cat 
Set cat = CreateObject("ADOX.Catalog")
With cat
  .Create _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Jet OLEDB:Engine Type=4;" & _
      "Data Source=" & _
      Environ$("temp") & "\MyWorkgroup.mdw;" & _
      "Jet OLEDB:Create System Database=-1"
  .Create _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Jet OLEDB:Engine Type=4;" & _
    "Data Source=" & _
    Environ$("temp") & "\MyDatabase.mdb;" & _
    "Jet OLEDB:System Database=" & _
    Environ$("temp") & "\MyWorkgroup.mdw;"

  ' Add table with data and user with privileges
  With .ActiveConnection
    .Execute "CREATE TABLE Test (data_col INTEGER);"
    .Execute "INSERT INTO Test VALUES (55);"
    .Execute "CREATE USER onedaywhen pwd H3sJaZ9k2m;"  ' <-- edit pwd
    .Execute "GRANT ALL PRIVILEGES ON Test TO onedaywhen;"
  End With
End With

' Test user can connect and SELECT
Dim con 
Set con = CreateObject("ADODB.Connection")
With con
  .ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Jet OLEDB:Engine Type=4;" & _
    "Data Source=" & _
    Environ$("temp") & "\MyDatabase.mdb;" & _
    "Jet OLEDB:System Database=" & _
    Environ$("temp") & "\MyWorkgroup.mdw;" & _
    "User ID=onedaywhen;Password=pwd;"  ' <-- edit pwd
  .Open
  MsgBox .Execute("SELECT * FROM Test;")(0)
End With

With no means to escape 'special' characters then it seems this can't be done using SQL DDL/DCL, which I think means it cannot be done using ADO at all.

So, anyone got an alternative e.g. DAO?