ADODB Connection

Using the ADODB connection, we connect our VBA application with the databases e.g. SQL, MS Access etc.

To use the ADODB connection you need add Microsoft ActiveX Data Objects reference.

  • Go to the Visual basic Editor (Press Alt+F11)
  • Go to Tools >> References>> Microsoft ActiveX Data Objects 2.8 Library
Add Reference
Add Reference

No you can define ADODB.Connection and ADODB.Recordset variable in your procedure or function.

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

To open the connection, you need to enter the connection string. Connection string will be changed according to the database. Here we are connecting with MS Access database

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database.accdb"

After opening the connection, you need to open the recordset-

rst.Open Source:="SELECT * FROM TBL_Customer", ActiveConnection:=cnn, CursorType:=adOpenKeyset, LockType:=adLockOptimistic

Here we are providing the different Cursor Type and Lock type-

Cursor Type:

adOpenForwardOnly:

  • This is the default. It is the fastest, and uses the least amount of memory and network traffic.
  • You can only use .MoveNext to move thru the records (not .MoveFirst etc).
  • You will not see changes to the data by other users since you opened the recordset.

adOpenStatic

  • You can use any of the .Move methods (.MoveFirst/.MovePrev/..).
  • You will not see changes to the data by other users since you opened the recordset.

adOpenKeyset

  • You can use any of the .Move methods (.MoveFirst/.MovePrev/..).
  • You will see Some changes to the data (edited/deleted records only) by other users since you opened the recordset.

adOpenDynamic

  • You can use any of the .Move methods (.MoveFirst/.MovePrev/..).
  • You will see All changes to the data (added/edited/deleted records) by other users since you opened the recordset.

Lock Type

The lock type also determines two things:

  1. Whether you can make changes to the data (add/edit/delete)
  2. How other users will be stopped from editing the same record as you.

adLockReadOnly

  • This is the default. It has the least amount of effect on other users, and is better in terms of speed/memory/network usage.
  • You cannot add/edit/delete data, you can only read it.
  • You do not block other users from editing records in the table.

adLockOptimistic

  • You can add/edit/delete data.
  • When you edit a record, another user can edit it at the same time – but if you both try to write the data, one of you will get an error when the .Update occurs.

adLockPessimistic

  • You can add/edit/delete data.
  • When you edit a record, it is immediately blocked from other users. An error will occur as soon as any values are altered in the.

Below is the sample code the get the record count from Customer table-

Sub Get_Record_Count()

    Dim cnn As New ADODB.Connection

    Dim rst As New ADODB.Recordset

    Dim qry As String

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Database\Database.accdb"
  
    qry = "SELECT * FROM Customers"

    rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

    MsgBox rst.RecordCount

    rst.Close

    cnn.Close
 

End Sub