Connect Excel to Access - VBA

Question

I get a "User-defined type not defined" error when I execute the below code, and the

"objCon As ADODB.Connection"

is highlighted on the first line. I am trying to set a connection from Excel to Access via VBA code. Thank you for any advice!

Private objCon As ADODB.Connection
Private rstRec As ADODB.Recordset
Private strQry

Sub Connect()
   Dim strConn As String


   Set objCon = New ADODB.Connection
   objCon.Mode = adModeReadWrite

      If objCon.State = adStateClosed Then
         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\DB\Db.accdb;Persist Security Info=False;"
         objCon.ConnectionString = strConn
         objCon.Open
      End If
End Sub

Hi, have you add ActiveX inside Tools->reference? support.microsoft.com/kb/257819 — Larry
Oh my, that was it, it worked! Fabulous. Please add as an answer so I can add points for you. — Sam
I suggest closing the question, this question has been asked many time before, you can search for VBA ADODB REFERENCE on SO or Google to find the solution. :) — Larry
can you see share|edit|close|delete|flag above the comments? the delete If not I can make it as an answer.. — Larry

Larry Larry · Accepted Answer · 2012-12-17T08:36:42

You can do one of the following

set objCon  = CreateObject("ADODB.Connection")
set rstRec = CreateObject("ADODB.Recordset")

Or in VBA Editor Tools-->Reference--> Add Microsoft ActiveX Data Object X.Y Library

Connect Excel to Access - VBA

1 Answers