system is not installed with MS Access but need to run vba to connect access

[prabhu murugan]

We know that we can run query from excel vba either using DAO or ADODB connection to MS Access. I wanted to know how to run the vba from excel to retrieve and update MS Access tables but the system is not installed with MS Access. What is the way to connect an MS Access file without having it installed.

I have tried the following code but i get Authentication failed error on line conn.open "Provider=Microsoft.ACE.OLEDB.12.0;\\path.accdb"

Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
'Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;\\path.accdb"

Set objRecordset = Server.CreateObject("ADODB.recordset")
objRecordset.activeconnection = conn
objRecordset.Source = "select * from associate;"
objRecordset.Open

'do you work here

objRecordset.Close
conn.Close

 

[Tieval]

Microsoft.ACE.OLEDB.12.0 is not installed unless you install Access or the Run Time Library and this therefore cannot work.

The warning states that it is connecting to the file using Microsoft.ACE.OLEDB.12.0.

 

[Simon_MT]

I don't know if this helps. It is required for web sites using Accdb files.

Microsoft Access Database Engine 2010 Redistributable

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Simon