vba code to add record in access which is protected

[ajaar]

Dear Friends,

I am really stuck here, any help would be appreciated.

I have below code in excel to add record in access data base which is working perfect. now i wanted to do the same job in protected access data base . i tried a lot to add the code with this, not succeeded yet. password is ssss55

Private Sub CommandButton1_Click()
 Dim oTable As Object, oRow As Object
 Dim cnn As New ADODB.Connection 'dim the ADO collection class
 Dim rst As New ADODB.Recordset 'dim the ADO recordset class
 Dim dbPath As String
 Dim x As Long, i As Long
 
On Error Resume Next
 
dbPath = Sheets("Home").Range("Z1").Value
 
Set cnn = New ADODB.Connection ' Initialise the collection class variable
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
rst.Open Source:="Data", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
  With rst
 .AddNew
 
.Fields("RquestDate").Value = range("a3").value
.Fields("CustomerName").Value = range("a4").value
.Fields("Deduction").Value =  range("a5").value
.Fields("Payable").Value =  range("a6").value
.update
 MsgBox "Updated successfully", vbOKOnly
 End With
 
 rst.Close
 cnn.Close
 Set rst = Nothing
 Set cnn = Nothing
 
End Sub

 

[Surendran Setty]

Hi Ajaar,

Connection String for Access 2007 - 2013
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword;
In your code 'Jet OLEDB:Database Password=MyDbPassword;' is missing.
For additional info, visit this link.
https://www.connectionstrings.com/access-2007/

Surendran

 

[ajaar]

Hi Surendran,

Thank you, to get it done i have to do the below change too.
Access-Fine-Option-Clieant Setting-Advanced-Click on Legacy Encryption.

Regards
Ajaar