OLEDB Structure

     

  • From VBA if we want to connect to any database we have to use either DAO or ADO

    •  

     

     

    DAO:

  • Stands for DATA ACCESSING OBJECTS

    •  

  • It allows the user to communicate external with external Databases

    •  

    ADO:

     

  • It stands for ACTIVEX DATA OBJECTS

    •  

  • Designing structure of excel sheet consits of ROWS & COLUMNS, having resemble with DATABSE tables, hence we can consider excel sheet as database

    •  

  • ADO allows to connect various databases like, Access,SQL,Excel....

    •  

  • Through ADO it doesn't require to open source document

    •  

    ADO connects Databases with the help of OLE DB(A connecting string to communicate with Database)

     

  • ADO Connects to excel sheet, either of below mentioned OLEDB providers
  • It is used to connect Large Databases, it is very effective to provide connections with external databases

    •  

    Microsoft JET OLEDB Provider

    (or)

    Microsoft OLE DB Provider for ODBC Drivers

     

     

  • Micorsoft JET OLEDB Provider
  • Excel File Version

    • Sub connect_to_Excel_Databases()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "data source=D:\Test.xlsx;" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
    .Open
    End With
    End Sub

     

     

    Ways to write Queries:

     

    Dim Query As String
    Query = "Select * from [Data$]"

     

  • This is SHEET LEVEL Query, where sheet name = DATA

    •  

  • It retrives all the records\tables exists in a worksheet having the name of DATA

    •  

     

    Introduction video about this Templates

     

  • This video enunciates about the features of this tempate
  • It describes about how this template works

    •  

     

     

    Retrieve all the records - STAR

     

  • It returns all the columns from DATABASE

    •  

     

     

    Download Database

     

    Retrieve required columns

     

  • We need to mention the column names individually

    •  

     

     

    Retrieve records using AND operator

     

     

     

    Excel as Database - WHERE Condition

     

     

     

    Excel as Database - OR Operator

     

     

     

    Excel as Database - IN Operator

     

     

     

     

    Excel as Database - BETWEEN Operator

     

     

     

     

    Excel as Database - LEFT Function

     

     

     

     

    Excel as Database - Date Range Report

     

     

     

     

     

    Download consolidated Queries template

     

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli