ActiveX Data Objects (ADO)

Active X Data Objects (ADO)

Add Below Library to use ADO

Option Explicit

Sub copyfromdb()
Dim salesconn As ADODB.Connection
Dim rst As ADODB.Recordset
Set salesconn = New ADODB.Connection
Set rst = New ADODB.Recordset
salesconn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\sid\Desktop\Movies.accdb;Persist Security Info=False"

salesconn.Open

With rst
.ActiveConnection = salesconn
.Source = "select region from sales"
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

Worksheets.Add
Range("A1").CopyFromRecordset rst.Fields
Range("A1").CopyFromRecordset rst
rst.Close

salesconn.Close

End Sub