Exporting data with vba from Excel to an Access 2007 db with password

February 12th, 2012 - 04:01 pm ET by Jan T | Report spam
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
alternativ):

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;"

' cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
' "Data Source=C:\Users\Jan\Documents\MyDB.accdb; " & _
' "Persist Security Info=False;"


' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblLog", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.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
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Does anybody understand why I get an error if I Decrypt the db with a
password? Any other way to password protect the database?

Thank you for any suggestion.

Regards Jan T.
email Follow the discussionReplies 8 repliesReplies Make a reply

Replies

#1 GS
February 12th, 2012 - 04:36 pm ET | Report spam
The first thing I see here is that you are using JET to provide a
password to ACE. Not sure if this IS the cause for the error, but I
tend to keep all things JET or all things ACE respective to Excel
version.

Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Similar topics