I will use Excel 2007 and Access 2007 and want to log events in an
*.accdb file. I also want to password protect the database. However,
when trying to add a new record to the db with password, I get an
error? If I remove the password from my db and my vba code in Excel,
it works perfectly. What do I do wrong?
Note: To protect my db I use the button on the ribbon Tab, Database
Tools/Decrypt db with password.
Here is the code I use with and without password (Commented out one
Sub ExcelToAccess(strSub As String)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Users\Jan\Documents\MyDB.accdb;" & _
"Jet OLEDB:Database Password=UniquePsw;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblLog", cn, adOpenKeyset, adLockOptimistic, adCmdTable
.AddNew ' create a new record
' add values to each field in the record
.Fields("WinLogin") = UserNameWindows
.Fields("Prosedure") = strSub
.Fields("AddinVersion") = ThisWorkbook.Name
.Fields("dtmStamp") = Now
.Update ' stores the new record
Set rs = Nothing
Set cn = Nothing
Does anybody understand why I get an error if I Decrypt the db with a
password? Any other way to password protect the database?