Run time error '3706': Provider cannot be found -Visual Basic

Question

I am trying to connect Visual Basic to MS Access using ADODB. But as I execute my code, it prompts: "Provider cannot be found.It may not be installed properly." But when I check on my directory I've got my "msjetoledb40.dll" installed.

Here is my code:

Dim conn As ADODB.Connection, rec As ADODB.Recordset

    Sub sample()

    Set conn = New ADODB.Connection

    conn.Open ("Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:\sample.mdb;Persist Security Info=false;")
End Sub

Answer 1

Confirming the version of your MS Office on which the script is running. If you have installed MS Office 2013 or later, you should revise the connection string from:

Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:\sample.mdb;Persist Security Info=false;

to:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\sample.mdb;Persist Security Info=false;

At least, this sovled my problem.

Answer 2

This would be better:

Sub sample()

  Dim conn As ADODB.Connection, rec As ADODB.Recordset
  Set conn = New ADODB.Connection
  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sample.mdb;"
  conn.Open
End Sub

You missed a point.

Microsoft.Jet.OLEDB 4.0 => Microsoft.Jet.OLEDB.4.0

Ref: http://www.connectionstrings.com/.