Using ADO To Communicate With SQL Database

ADO stands for ActiveX Data Objects, which is used to communicate with different sorts of databases.

Here in the below examples, we are running a basic SQL Select statement and returning the data back to a Worksheet

We will use the Early Binding Technique in the first example.

If you are keen on knowing the difference between Early Binding & Late Binding Technique, click HERE!

In order for the Early Binding Technique to work, you will first have to set a reference to the Microsoft ActiveX Data Objects Library.

In order to do that, go to the Visual Basic Editor > Tools > Reference > Select Microsoft ActiveX Data Objects Library (Whichever is the latest one available)

Inser a regular code module and use the below code. Make the necessary changes to the connection string and it should work just fine

-------------------------------------------------------------

Sub ADOEarlyBinding() Dim Conn As ADODB.Connection, RS As ADODB.Recordset, SQLQuery As String

Const ConnectionString As String = "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.2.222\SQLInstanceName;INITIAL CATALOG=MainDB; User Id=User1; Password=YourPassword;" Set Conn = New ADODB.Connection Set RS = New ADODB.Recordset SQLQuery = "Select * from MyTable" Conn.Open ConnectionString With RS .ActiveConnection = Conn .Source = SQLQuery .LockType = adLockReadOnly .CursorType = adOpenForwardOnly .Open End With Range("A1").CopyFromRecordset RS RS.Close Conn.Close End Sub

-------------------------------------------------------------

Generally, Early Binding is a better selection as it has some advantages over the Late Binding Technique, but your Early Bound code will fail, if the reference is not set, or set incorrectly.

I prefer to use the Late Binding Technique only If I am going to send the file to multiple users, or to someone who is not well versed with VBA to avoid any problems.

Changing an Early Bound Code to a Late Bound Code is pretty easy.

All you have to do is dimension you Variables using the generic Object Type, and use the CreateObject Function to create new instances of the Object.

You will also have to change the Constants to their Values.

Your Late Bound Code should look like this -

-------------------------------------------------------------

Sub ADOLateBinding() Dim Conn As Object, RS As Object, SQLQuery As String

Const ConnectionString As String = "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.2.222\SQLInstanceName;INITIAL CATALOG=MainDB; User Id=User1; Password=YourPassword;" Set Conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") SQLQuery = "Select * from MyTable" Conn.Open ConnectionString With RS .ActiveConnection = Conn .Source = SQLQuery .LockType = 1 .CursorType = 0 .Open End With Range("A1").CopyFromRecordset RS RS.Close Conn.Close End Sub

-------------------------------------------------------------

This code will happily work without setting a reference to the Microsoft ActiveX Data Objects Library

Hope it helps! :)

#VBA #ADODB #ADO #SQL #StoredProcedureInVBA #DatabaseConnectivityUsingVBA

Follow Us
  • Twitter Basic Square
  • Google+ Basic Square
Recent Posts
Search By Tags
No tags yet.