Welcome to my blog!


January 12, 2022 09:46

Access (edit)

Microsoft Access - Wikipedia

Microsoft Data Access Components - Wikipedia

Open Database Connectivity - Wikipedia

  • Access 2000 | Access 2010
  • 32-bit Office version
  • 64-bit Office version
  • 32-bit Office installed or the 32-bit version of the Access Database Engine installed
  • 64-bit Office installed or the 64-bit version of the Access Database Engine installed
  • Component Object Model (COM)
  • Microsoft Office 12.0 Access Database Engine OLE DB Provider
  • Database Management Systems (DBMS)
  • Access Connectivity Engine (ACE)
  • Open Database Connectivity (ODBC)
  • Object Linking and Embedding Database (OLE DB)
  • Data Access Object (DAO)
  • Microsoft Data Access Components (MDAC)
  • Data Access Components (DAC)
  • Microsoft ActiveX Data Objects (ADO)
  • ADO Programmer's Guide
  • 32-bit version of the Microsoft OLE DB Provider
  • Microsoft.ACE.OLEDB.12.0
  • Microsoft.Jet.OLEDB.4.0
  • ADO RecordSet
  • VB6
  • AccessDatabaseEngine.exe
  • AccessDatabaseEngine_X64.exe
  • System.Data.SqlClient
  • System.Data.OleDb
  • System.Data.Odbc
  • Microsoft ActiveX Data Objects 2.8 Library
  • Microsoft ActiveX Data Objects 6.0 Library
  • Microsoft ActiveX Data Objects Recordset 6.0 Library


Choosing a Data Provider - ADO: ActiveX Data Objects [Book] (oreilly.com)


Access 2010 | Microsoft Docs

Microsoft Access Developer Help and Programming Center to Help You Become a Better Designer and VBA Programmer from FMS in Vienna, Virginia (fmsinc.com)

Tài liệu tiếng Việt

Học lập trình | Kiếm tiền Online | SEO website - Chia sẻ kiến thức IT (diendanhocweb.com)

Khoa Công nghệ Thông tin (hcmute.edu.vn)

Tài liệu tiếng Anh

Access connection strings - ConnectionStrings.com

Connection String Syntax - ADO.NET | Microsoft Docs

Connecting to a database | Microsoft Docs

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\Northwind.mdb;User ID=Admin;Password=;

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Northwind.mdb;Jet OLEDB:System Database=d:\NorthwindSystem.mdw;User ID=*****;Password=*****;

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Northwind.mdb;Jet OLEDB:System Database=|DataDirectory|\System.mdw;

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\myAccessFile.accdb; Persist Security Info=False;

=Microsoft.ACE.OLEDB.12.0;Data Source=C:\yourFolder\yourAccessFile.accdb;Persist Security Info=False;

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\yourFolder\yourAccessFile.accdb; Jet OLEDB:Database Password=YourDbPassword;
Public Sub Demo()
    Dim dt As New DataTable
    Dim Builder As New OleDbConnectionStringBuilder With
        .Provider = "Microsoft.ACE.OLEDB.12.0",
        .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")

    Using cn As New OleDbConnection With
            .ConnectionString = Builder.ConnectionString
        Using cmd As New OleDbCommand With {.Connection = cn}
            cmd.CommandText = "SELECT Identifier, ContactName, CompanyName FROM Customers"
            Catch ex As Exception
            End Try
        End Using
    End Using
End Sub

Unable to establish connection to Access Database Engine OLE DB in Power Automate Desktop (microsoft.com)

Resolve Access and .XLS import issues in Power BI Desktop - Power BI | Microsoft Docs

A PowerShell script that does that is found under finding OLE DB providers.
This example demonstrates how a CSV file can be imported into an Excel table with Visual Basic for Application and an OLE DB provider.

Microsoft Access Developer and VBA Programming Help Center

Resources for Becoming a Better Microsoft Access Developer and Programmer

Here are some of our resources to help you better understand and use Microsoft Access during design, development, deployment, and maintenance.

  • Shows you how to program VBA and macros for Access, including use of error handling, best practices, and helpful tips

  • Provides code examples that demonstrate how to enhance and extend the functionality of Access applications

  • Shows when, why, and how to use DAO and ADO in VBA

  • Demonstrates how to build Access applications that leverage SharePoint®, SQL Server®, Excel®, XML, and other sources

  • Discusses deployment considerations, such as working with mixed platforms and versions, 64-bit Office, Access Runtime, Access and database security, and upgrading and conversions

Access 2010 Programmer's Reference

Coverage Includes:

  • Introduction to Microsoft Access 2010
  • New Features
  • Upgrading and Converting to Access 2010
  • Macros in Access 2010
  • Using the VBA Editor
  • VBA Basics
  • Using VBA in Access
  • Creating Classes in VBA
  • Extending VBA with APIs
  • Working with the Windows Registry
  • Using DAO to Access Data
  • Using ADO to Access Data
  • Using SQL with VBA
  • Using VBA to Enhance Forms
  • Enhancing Reports with VBA
  • Customizing the Ribbon
  • Customizing the Office Backstage
  • Working with Office 2010
  • Working with SharePoint
  • Working with .NET
  • Building Client-Server Applications with Access
  • The Access 2010 Templates
  • Access Runtime Deployment
  • Database Security
  • Access 2010 Security Features

Data types

Supported data types seem to be:
Short (Same is Integer)
Double (Same as Float)
Memo (Same as LongChar)
(ODBC data type) Char same as Text
Float Same as Double
Integer Same as Short
LongChar Same as Memo
Date  Date format


There doesn't really seem to be any documentation for this provider (social.msdn.microsoft.com)

Access ADO.NET Provider:

Access ADO.NET Provider - Visual Studio Marketplace (Solution Template)

DB Access using .NET Data Providers (akadia.com)

Guide - Data Access | The F# Software Foundation (fsharp.org)

SQL Provider for MSAccess (fsprojects.github.io)

Database Access | Mono (mono-project.com)

Getting Information About Your OLE DB Data Provider

Before you begin using VisiconX, find out what you need to know to access an OLE DB data provider. The first thing to determine is which data provider to access. VisiconX supports a number of data providers, such as SQL Server and Oracle, as well as ODBC drivers that map to many different types of relational databases.

The information you need differs depending on the data provider you access. Begin by asking your system administrator. The following table defines the information to acquire for each type of OLE DB data provider VisiconX supports.

Required Connection Information for an OLE DB Data Provider

OLE DB Data Provider

Information Needed

Microsoft Jet 3.51 or 4.0 Provider

Database path and name
System (security) database path and name

ODBC Drivers

ODBC data source name or
ODBC connection string

SQL Server

Server name
Database name


Server name

Simple Provider

Connection string for the data source (server) and initial catalog

NOTE: ODBC data source names are defined through the ODBC Administrator tool. Access this tool by clicking the Start button and pointing to Programs, Administrative Tools, and then Data Sources (ODBC). You can also access the Administrative Tools folder from the Control Panel.

You also need to determine if the OLE DB data provider is secured. If it is, you need a user ID and password to access the data provider. You also need to determine which access privileges you have. For example, do you have access to all or specific databases on the server? Can you access all tables in the database?


  • 1) Visual Basic For Applications
  • 2) Microsoft Access 16.0 Object Library
  • 3) OLE Automation
  • 4) Microsoft Office 16.0 Access database Engine Object Library
  • 5) Microsoft ActiveX Data Objects Recordset 6.0 Library

Microsoft Access 2000 or Access 2002 - 2003 database: *. mdb
Microsoft Access 2010: *. accdb

Access Database Engine:
+ Office Access Connectivity Engine (ACE)
+ Microsoft Jet Database Engine

Access program language:
+ Visual Basic 6.0 (VB6)


Installing ADO also copies the following type libraries of earlier versions:

  • msado27.tlb, ADO 2.7 Type Library

  • msado26.tlb, ADO 2.6 Type Library

  • msado25.tlb, ADO 2.5 Type Library

  • msado21.tlb, ADO 2.1 Type Library

  • msado20.tlb, ADO 2.0 Type Library


Screenshot of a generated connection string.

MDAC Architecture.svg

Microsoft Data Access architecture

Access connection strings - ConnectionStrings.com

OLE DB providers

ODBC drivers

Wrappers and others

Microsoft Access Database Engine

Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center (bộ cài đặt)

Download Microsoft Access Database Engine 2016 Redistributable from Official Microsoft Download Center (bộ cài đặt)

Microsoft.Jet.OLEDB.4.0 Microsoft OLE DB Provider for Microsoft JET
Microsoft.ACE.OLEDB.12.0 Microsoft OLE DB Provider for Microsoft Access database engine



HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine

OLE DB Provider for Jet and ODBC driver are 32-bit versions only - Office | Microsoft Docs

Referencing the ADO Libraries In a Visual Basic 6 Application - ActiveX Data Objects (ADO) | Microsoft Docs

Microsoft OLE DB Provider for Microsoft Access database engine (renenyffenegger.ch)

Create an ODBC DSN for drivers that are provided by Microsoft Access in the Data Sources ODBC Administrator

Screenshot of the ODBC Data Sources Administrator, which shows the error message together with the platform.

Some drivers:

  • Microsoft Access Driver (*.mdb, *.accdb)
  • Microsoft Access Text Driver (*.txt, *.csv)
  • Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

 oledb - what is the difference between OLE DB and ODBC data sources? - Stack Overflow

alt text

Code Samples

Option Explicit

Private Const mSqlProvider As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;"
Private Const mSqlHost As String = "Data Source=C:\Database.sdf;"
Private mCmd As ADODB.Command ' For executing SQL
Private mDbConnection As ADODB.Connection

Private Sub Command1_Click()

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
Dim DbConnectionString As String

DbConnectionString = mSqlProvider & _

Set mDbConnection = New ADODB.Connection
mDbConnection.CursorLocation = adUseServer

Call mDbConnection.Open(DbConnectionString)

If mDbConnection.State = adStateOpen Then
Debug.Print (" Database is open")
' Initialise the command object
Set mCmd = New ADODB.Command
mCmd.ActiveConnection = mDbConnection

mCmd.CommandText = "select * from myTestTable"
mCmd.CommandType = adCmdText

Set rs = mCmd.Execute

Debug.Print rs.RecordCount ' This should now return the right value.
Debug.Print rs.Fields(0) ' returns correct data for first row, first col
Debug.Print rs.Fields(1) ' returns correct data for first row, 2nd col
Debug.Print rs.Fields(2) ' returns correct data for first row, 3rd col

End If

End Sub


Recent posts