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
• ACEODBC.DLL
• 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

Example:

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

Course

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;

Provider=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"
            Try
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            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

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

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)

Guide:

• 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)

Compatibility

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

Pictures:

Microsoft Data Access architecture

Access connection strings - ConnectionStrings.com

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)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\FilesPaths

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

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

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 & _
 mSqlHost
 
 
 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