My Brain BLOG

When developing an application with visual basic, you may want to store data. So you create a database during runtime, for example with the following piece of code, as many websites show on various forums:

Dim strMDBPath As String = "C:\SomePath\MyTestDB.mdb"
Dim cat As New ADOX.Catalog()
 cat.Create(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & strMDBPath)
 cat = Nothing

Next you wish to protect your data, so only your application can open the database. You google again, and find the following solution:

Dim cn As OleDbConnection = New OleDbConnection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDBPath & ";Mode=Share Deny Read|Share Deny Write;"
cn.Open()
Dim cmdpw As OleDbCommand = New OleDbCommand
cmdpw.Connection = cn
cmdpw.CommandText = "ALTER DATABASE PASSWORD [MYNEWPASSWORD] null"
cmdpw.ExecuteNonQuery()
cn.Close()

Unfortunately, now you are stuck with an error you can not solve:
“You attempted to open a database that is already opened by user ‘Admin’ on machine ‘YOURPCNAME’. Try again when the database is available.”
Creating the database seems to keep the connection open and you cannot close it to execute the password change on an exclusive connection.

The answer and solution is simple: Set the password in the connection string in the first piece of code when creating the database! The attempt to ALTER DATABASE PASSWORD is no longer needed and you can forget about that…
So:

Dim strMDBPath As String = "C:\SomePath\MyTestDB.mdb"
Dim cat As New ADOX.Catalog()
cat.Create(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=MYNEWPASSWORD;Data Source=") & strMDBPath)
cat = Nothing

Enjoy this free advice from My Brain!