MS Access

Introduction

Nowadays, very few people will choose to use this type of database, as the problems it generates outweigh the benefits it provides. Over the years, the expectations placed on MS Access have declined alarmingly, making it an almost residual product widely rejected by the programming community.

However, the appeal of MS Access lies in the integration of a user interface that allows the development of fully functional small applications quickly and easily. These types of solutions have a much lower cost and complexity compared to solutions based on the installation of database engines, database design, and the development of user-friendly application interfaces that connect to these databases.

A bit of history (Microsoft Access - Wikipedia and Access Database Engine - Wikipedia), from the Jet 3.0 engine of Access 7.0 (Office 95) to the Jet 4.0 engine of Access 2003, there were relative advancements that made one think of a good evolution of the environment. However, with the introduction of the new ACE engine for Access 2007, expectations disappeared overnight. Again, with the 2010 version, the dark future of this type of application became evident: the imposing mess caused by the new 32-bit and 64-bit drivers, which no longer came with the operating system, made it impossible to deliver solutions without installers. Needless to say, these new drivers did not even solve the problems of the previous ones but introduced new ones. To top it all, starting from version 1703 (update to Office 2016), a new data type (Large Number: Use the Large Number data type (microsoft.com)) was introduced, which was not supported by the drivers. In the latest version of Office (2021), another new data type has been introduced: Date/Time Extended (Use the Date/Time Extended data type (microsoft.com)). Although this new data type has not been tested, it is assumed that the behavior will be the same as with the Large Number type: driver incompatibility.
Finally, as an example, the article Incorrect sorting with Decimal fields can be cited, which shows how Microsoft has no intention of fixing the problems of this database engine, not even when they create a new engine.

It should be noted that, in 1995, the possibility of having a desktop database that could hold 1 GB of data was something astonishing. However, in 2024, that capacity (2 GB in 64-bit versions) is clearly insufficient for a large number of simple applications that store a large amount of data, such as historical series. This also leads to a loss of interest in developing applications based on this engine.

Lastly, it should be pointed out that the online information about MS Access that Microsoft provides to users is scarce, frankly very poor, and contains errors. For example, the data types of MS Access are very poorly described in: Introduction to data types and field properties (microsoft.com) and Data types for Access desktop databases (microsoft.com).

This chapter shows the results of the behaviors of different MS Access data providers and drivers in various situations not covered in previous chapters. This is intended to help resolve issues and questions regarding the behavior of Excel To Database.

MS Access Drivers

It should be noted that the driver specifications were not found anywhere. Thus, during testing, the limits of these drivers for MS Access will be attempted to be found. These limits, as will be seen later, are, in some cases, the most absurd and arbitrary that can be found.

• Installation

  There are several drivers available for connecting to an MS Access database. They depend on the MS Access engine (Jet 4.0 for “.mdb” and/or ACE for “.mdb” and “.accdb”) and also depend on the application that uses them. For 32-bit applications, 32-bit Jet or ACE drivers must be used, while for 64-bit applications, only 64-bit ACE drivers can be used.

  Installing these drivers is also not straightforward. While the 32-bit Jet drivers come pre-installed with the operating system, installing the ACE drivers is a real pain: One must decide whether to install the 32-bit or 64-bit drivers and also consider if there is an Office installation and what version it is.

  The drivers can be downloaded from: Download Microsoft Access Database Engine 2016 Redistributable from Official Microsoft Download Center (ACE 16). For Office 2013, a version of the MS Access engine can be downloaded from Download Microsoft Access 2013 Runtime from Official Microsoft Download Center (ACE 15), although it is not highly recommended.
  The download for "Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center" (ACE 12) is no longer available.

  Although new versions of MS Access (2019, 2021 and 2024) exist, Microsoft does not provide any new drivers. Lastly, it should be mentioned that installing drivers from Microsoft 365 Access Runtime is such a nightmare that it is not even worth attempting ( Download and install Microsoft 365 Access Runtime, Cannot use ODBC, OLEDB, or DAO interfaces from Access outside Office Click-to-Run applications).

  In our case, we start with a 64-bit Windows 10 operating system with 64-bit Office 2019 installed and the 64-bit ACE 12 and ACE 16 drivers installed. When attempting to install the 32-bit ACE 16 driver, the error shown in the following figure appears:

  Error installing ACE 16 x32

  

  The error message displayed is completely absurd, as it is supposed to be independent of the installation of MS Access or Office. However, the 32-bit ACE 12 installation does not produce any unexpected errors, which is as expected.
  This error can be bypassed by performing a command line installation and adding the parameter "/passive" (https://stackoverflow.com/a/56095027) or "/quiet" ( How to install 64-bit Microsoft Access database engine alongside 32-bit Microsoft Office?).

• 32-bit Application

  Although the publication of the 32-bit version of Excel To Database is not planned, a version has been built for testing purposes. If any user needs this version, they can contact us via "Contact" and we will consider the possibility of publishing the 32-bit version.

  However, to clarify things and for demonstration purposes, some screenshots for this solution are shown.

  The main difference from the 64-bit version is the ability to use the Jet 4.0 driver, integrated into the operating system, which allows connections to “.mdb” databases. It is possible that this driver can connect to versions of Access prior to 2002, although the appropriate tests have not been conducted.

  Jet and ACE Drivers - Excel To Database 32 bits

  

• 64-bit Application

  For 64-bit applications, it is necessary to install the 64-bit drivers. As shown in the figure, the ACE 12 and ACE 16 64-bit drivers are installed. Excel To Database is delivered as a 64-bit application.

  Drivers ACE - Excel To Database 64 bits

  

• Big Int/Date Time Extended

  Currently, there are several MS Access database formats. Within the databases with the “.accdb” extension, there is one format for versions ranging from 2007 to 2016, and another format from 2016, version 1703, onwards. This new format supports two new data types: Big Int (Large Number) and Date Time Extended (Extended Date and Time).
  These data types are designed for linking MS Access tables to SQL Server tables, as described here: Use the Date and Time Extended data type.

  You can create a database of this type by checking the option "Support BigInt data types for linked or imported tables" and creating a table that contains this data type. The following figure shows this table and the option checked.

  MS Access with BigInt data type

  

  These two factors will make the database format incompatible with any of the drivers published by Microsoft. This is another indication of the neglected state of MS Access. Thus, when attempting to establish a connection to this new database format, we get the following error:

  MS Access Error New Format with ACE Driver

  

  As shown in the figure, the driver used is the latest version (ACE 16), and the error message observed (completely absurd) is interpreted as indicating that the new database format is not compatible with this driver. This error appears in both OLE DB and ODBC data providers.

Books, Databases

It starts with a 64-bit Windows 10 Operating System with a regional configuration of Spanish (Spain). This means that it defines the "Decimal symbol" as the comma "," and the "Thousand separator symbol" as the period ".". Precisely the opposite of what is defined in Anglo-Saxon cultures. On this Operating System, a 64-bit Microsoft Office Professional Plus 2019 with a Spanish (Spain) configuration has been installed.

• Databases

  To demonstrate the behavior of the different options in the application, a series of tables for MS Access, SQL Server, and Oracle have been prepared, along with several Excel Workbooks. The table creation scripts, the workbooks, the MS Access databases, and the Configuration file for all chapters can be downloaded from: "Example Downloads".
  The table creation scripts are also shown in the documentation, as well as the Excel Sheets used at each moment (as images).

  Code

      • MS Access MDB.mdb: Database with Access 2000 – 2003 format corresponding to the Jet 4.0 engine
      • MS Access ACCDB.accdb: Database with Access 2007-2016 format corresponding to the ACE 16 engine
      • MS Access ACCDB Con BigInt.accdb: Database with Access 2016 Format (version 1703 or higher) corresponding to the ACE 16 engine.
  
  4 Configurations are prepared with the following connections to the Databases:
  
  • Connection with Driver ACE 16 a “MS Access ACCDB.accdb”: Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\"G:\\MSAccess\\MS Access ACCDB.accdb\"
  • Connection with Driver ACE 16 a “MS Access MDB.mdb”: Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\"G:\\MSAccess\\MS Access MDB.mdb\"
  • Connection with ODBC a “MS Access ACCDB.accdb”: Driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=G:\\MSAccess\\MS Access ACCDB.accdb;defaultdir=G:\\MSAccess;driverid=25;fil=MS Access;filedsn=G:\\MSAccess\\MSAccess_ODBC_ACCDB_X64.dsn;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;safetransactions=0;threads=3;uid=admin;usercommitsync=Yes;app=Excel To Database
  • Connection with ODBC a “MS Access MDB.mdb”: Driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=G:\\MSAccess\\MS Access MDB.mdb;defaultdir=G:\\MSAccess;driverid=25;fil=MS Access;filedsn=G:\\MSAccess\\MSAccess_ODBC_MDB_X64.dsn;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;safetransactions=0;threads=3;uid=admin;usercommitsync=Yes;app=Excel To Database
  
  ODBC connections use the following DSN files:
    MSAccess_ODBC_ACCDB_X64.dsn
  [ODBC]
  DRIVER=Microsoft Access Driver (*.mdb, *.accdb)
  UID=admin
  UserCommitSync=Yes
  Threads=3
  SafeTransactions=0
  PageTimeout=5
  MaxScanRows=8
  MaxBufferSize=2048
  FIL=MS Access
  DriverId=25
  DefaultDir=G:\MSAccess
  DBQ=G:\MSAccess\MS Access ACCDB.accdb
      MSAccess_ODBC_MDB_X64.dsn
  [ODBC]
  DRIVER=Microsoft Access Driver (*.mdb, *.accdb)
  UID=admin
  UserCommitSync=Yes
  Threads=3
  SafeTransactions=0
  PageTimeout=5
  MaxScanRows=8
  MaxBufferSize=2048
  FIL=MS Access
  DriverId=25
  DefaultDir=G:\MSAccess
  DBQ=G:\MSAccess\MS Access MDB.mdb
  

  In both ODBC connections, the {Microsoft Access Driver (*.mdb, *.accdb)} 64-bit driver version 16.00.4999.1000 (ACEODBC.DLL) dated 14/07/2020 is used.
  In the OLE DB connections, the Microsoft.ACE.OLEDB.16.0 64-bit driver version 16.0.5011.1000 dated 14/07/2020 is used.

  Although this document is not a tutorial on creating connection strings, it is mentioned that you may have problems with directory names if they contain spaces.

• Excel Workbook

  For the tests performed, an Excel Workbook has been used, whose Sheets will be shown in images throughout the chapter.

  Of course, for these tests, Excel To Database 64-bit has been used.

  Note that for the tests, no cell with errors has been included, so the option No Error Values will not be used.

  The data has been prepared to allow checking the functionality of the data load. They are not an exhaustive test suite, so it can be inferred that the data load does not guarantee that it will occur as the user might expect nor as described on this page.

  You can download the test files from here: Download Examples

MS Access Tests: Numbers

• The strange case of OLEDB with Decimal and Currency

  Since time immemorial, this data type has always had problems with OLE DB connections to MS Access. It seems to be irremediably linked to the regional settings established in Windows. Thus, if it has been indicated that the decimal separator is something other than a period, the data will not be properly inserted into these types of fields. This confirms once again that MS Access has been completely abandoned for a long time.

  The tests were performed on a Windows 10 with a regional configuration of Spanish (Spain). This means that it defines the "Decimal symbol" as the comma "," and the "Thousand separator symbol" as the period ".". Precisely the opposite of what is defined in Anglo-Saxon cultures.

  Regional Settings

  

  With this regional configuration, when attempting to load data into "Decimal" or "Currency" fields, the data provider replaces the decimal separator used internally by Excel (the period) with the regional configuration's decimal separator (the comma), causing an error during data insertion.

  Using the data mentioned in MS Access: Decimal Rounding, inserting the first row results in the error shown below because the data provider is actually trying to insert the values 0,0000000000000000000000000010 for Decimal_0_28, 0,1234 for Decimal_19_4 and Moneda and 0,12 for Decimal_4_2
  Please note that the error message presents the values exactly as Excel To Database hands them over to the driver.

  Code

  Row: 1
  Command: INSERT INTO [Tabla Redondeo Decimal] ([Decimal_0_28], [Decimal_28_0], [Decimal_19_4], [Moneda], [Decimal_4_2], [Driver], [Proveedor Datos]) VALUES (?, ?, ?, ?, ?, ?, ?)
  Select Command: SELECT [Decimal_0_28], [Decimal_28_0], [Decimal_19_4], [Moneda], [Decimal_4_2], [Driver], [Proveedor Datos] FROM [Tabla Redondeo Decimal]
  Exception:
  {
    "$id": "1",
    "$type": "System.Data.OleDb.OleDbException, System.Data",
    "ErrorCode": -2147217913,
    "Errors": {
      "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
      "$values": [
        {
          "$id": "2",
          "$type": "System.Data.OleDb.OleDbError, System.Data",
          "Message": "Data type mismatch in criteria expression.",
          "NativeError": -539888598,
          "Source": "Microsoft Access Database Engine",
          "SQLState": "3464"
        }
      ]
    },
    "Message": "Data type mismatch in criteria expression.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "Microsoft Access Database Engine",
    "HResult": -2147217913
  }
  
      Column: Decimal_0_28    Value: 0.0000000000000000000000000010
      Column: Decimal_28_0    Value: 9999999999999999999999999
      Column: Decimal_19_4    Value: 0.1234
      Column: Moneda    Value: 0.1234
      Column: Decimal_4_2    Value: 0.12
      Column: Driver    Value: Microsoft.ACE.OLEDB.16.0
      Column: Proveedor Datos    Value: OLE DB
  An exception occurred attempting to load Table 'Tabla Redondeo Decimal'. Row Error: 1
       Query that indicates the used columns of the Table:
       SELECT [Decimal_0_28], [Decimal_28_0], [Decimal_19_4], [Moneda], [Decimal_4_2], [Driver], [Proveedor Datos] FROM [Tabla Redondeo Decimal]
  {
    "$id": "1",
    "$type": "System.Data.OleDb.OleDbException, System.Data",
    "ErrorCode": -2147217913,
    "Errors": {
      "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
      "$values": [
        {
          "$id": "2",
          "$type": "System.Data.OleDb.OleDbError, System.Data",
          "Message": "Data type mismatch in criteria expression.",
          "NativeError": -539888598,
          "Source": "Microsoft Access Database Engine",
          "SQLState": "3464"
        }
      ]
    },
    "Message": "Data type mismatch in criteria expression.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "Microsoft Access Database Engine",
    "HResult": -2147217913
  }
  

  Not only does this situation exist, but in this StackOverflow answer other types of cases are discussed.

  However, if in the regional configuration the "Decimal symbol" is replaced by the period "." and the "Thousand separator symbol" by the comma ",", the data load works correctly.
  Excel To Database displays a message when it detects this problem. At that time, you can change the Windows Regional Settings, continue loading data, and when finished, restore the previous settings.

  Note that this error does not exist with ODBC drivers.

• "Floating Point" Numbers

  To demonstrate the behavior of the drivers with this type of MS Access data, a table is used whose creation script is shown below. The Excel Sheet used is shown in the following figure.

  Note that for the OLE DB data provider to work correctly, it is NOT necessary to specify the period "." as the "Decimal symbol" in the Windows Regional Settings. This is only necessary for the "Decimal" and "Currency" data types.

  Code

  Public Sub CreaTablaNumeros()
  On Error Resume Next
  
    CurrentProject.Connection.Execute "DROP TABLE [Tabla Numeros]"
  
  On Error GoTo Sub_Err
  
    CurrentProject.Connection.Execute "CREATE TABLE [Tabla Numeros](" & _
      "[Id] COUNTER NOT NULL, " & _
      "[Proveedor Datos] TEXT (255) NOT NULL, " & _
      "[Driver] TEXT (255) NOT NULL, " & _
      "[Simple_3_Fijo] SINGLE, " & _
      "[Simple_3_General] SINGLE, " & _
      "[Doble_3] DOUBLE" & _
    ")"
  
    Dim db As Object, tbl As Object, fld As DAO.Field
    Set db = CurrentDb
    db.TableDefs.Refresh
    Set tbl = db.TableDefs("Tabla Numeros")
    tbl.Fields.Refresh
    Set fld = tbl.Fields("Proveedor Datos")
    fld.AllowZeroLength = False
    fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)
    Set fld = tbl.Fields("Driver")
    fld.AllowZeroLength = False
    fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)
  
    Set fld = tbl.Fields("Simple_3_Fijo")
    fld.Properties.Append fld.CreateProperty("DecimalPlaces", 2, 3)
    fld.Properties.Append fld.CreateProperty("Format", 10, "Fixed")
    fld.Properties.Append fld.CreateProperty("Description", 10, "Ver: https://www.uvm.edu/~bnelson/computer/access2003/howaccesstreatsdecimalplaces.html. 4 Bytes")
    Set fld = tbl.Fields("Simple_3_General")
    fld.Properties.Append fld.CreateProperty("DecimalPlaces", 2, 3)
    fld.Properties.Append fld.CreateProperty("Format", 10, "General Number")
    fld.Properties.Append fld.CreateProperty("Description", 10, "Ver: https://www.uvm.edu/~bnelson/computer/access2003/howaccesstreatsdecimalplaces.html. 4 Bytes")
    Set fld = tbl.Fields("Doble_3")
    fld.Properties.Append fld.CreateProperty("DecimalPlaces", 2, 3)
    fld.Properties.Append fld.CreateProperty("Description", 10, "Ver: https://www.uvm.edu/~bnelson/computer/access2003/howaccesstreatsdecimalplaces.html. 8 Bytes")
  
    CurrentProject.Connection.Execute "CREATE UNIQUE INDEX PrimaryKey ON [Tabla Numeros] (Id ASC) WITH PRIMARY"
  
  Sub_Exit:
    Set fld = Nothing
    Set tbl = Nothing
    If Not db Is Nothing Then db.Close
    Set db = Nothing
    Exit Sub
  
  Sub_Err:
    MsgBox "Se ha producido un error en 'CreaTablaNumeros'." & vbNewLine & vbNewLine & vbTab & Err.Description, , "Error Nº: " & Err.Number
    Resume Sub_Exit
    Resume
  End Sub
  

  MS Access: Excel Sheet for "Floating Point" Numbers

  

  It should be noted that the cell format in the Excel Sheet is "Text", "Number", or "Scientific". It is "Text" for cases where Excel's precision is exceeded. In these cases, the period "." is used as the "Decimal symbol", as it is the separator used by Excel To Database to convert text to numbers.

  The fields "Simple_3_Fixed" and "Simple_3_General" are "Single" type fields; floating-point numeric fields with up to 7 significant digits. The "3" in the name refers to specifying that MS Access should display only 3 decimals of the data stored in the field ("Decimal places" = 3). The first field has a defined "Fixed" format, which causes the data to be displayed rounded to 3 decimals, and the second "General number", which displays the data as stored in the database.
  In How Access Treats Decimal Places, you can find a brief description of how MS Access handles decimals based on the field type ("Data type" and "Field size"), the format for displaying the field ("Format"), and the number of decimals you want to display ("Decimal places").
  The field "Double_3" is a floating-point numeric field with up to 15 significant digits. The behavior of this field and the "Single" type fields is the same, except for precision.

  Both data providers (OLE DB and ODBC) round the data when it exceeds the precision of the field. The rounding used is of the type MidpointRounding.AwayFromZero. See MidpointRounding Enumeration.

  The maximum capabilities of Excel, MS Access, and .NET have been tested, obtaining the expected results with both data providers. It should be noted that both MS Access and .NET have a precision of 7 significant digits for Single and 15 for Double. Thus, data with greater precision may be rounded to this precision: .NET does so in cells "C6", "A15", "B15", "B16", "C20", "A21", "B21" and "C21", and MS Access does so in cells "A12:A14", "B12:B14", "C4", "C7:C9" and "C19".

  Finally, note that cells "A22:B22" are inserted as NULL by Excel To Database, however, MS Access can store the values 1.#INF (with OLE DB) or inf,000 (with ODBC) for this data.

MS Access Tests: Dates

To demonstrate the behavior of this option with MS Access, the Database from the previous section is used, which contains the following table and the Excel Workbook from the previous section. The Sheet from the Excel Workbook used is shown in the following figure:

Note that for the OLE DB data provider to work correctly, it is NOT necessary to specify the period "." as the "Decimal symbol" in the Windows Regional Settings. This is only necessary for the "Decimal" and "Currency" data types.

Option Compare Database
Option Explicit

Public Sub CreaTablaFechas()
On Error Resume Next

  CurrentProject.Connection.Execute "DROP TABLE [Tabla Fechas]"

On Error GoTo Sub_Err

  CurrentProject.Connection.Execute "CREATE TABLE [Tabla Fechas](" & _
  "[Id] COUNTER NOT NULL, " & _
  "[Proveedor Datos] TEXT (255) NOT NULL, " & _
  "[Driver] TEXT (255) NOT NULL, " & _
  "[Fecha/Hora 1] DATETIME, " & _
  "[Fecha/Hora 2] DATETIME, " & _
  "[Fecha/Hora 3] DATETIME, " & _
  "[Fecha/Hora 4] DATETIME" & _
  ")"

  Dim db As Object, tbl As Object, fld As Object
  Set db = CurrentDb
  db.TableDefs.Refresh
  Set tbl = db.TableDefs("Tabla Fechas")
  tbl.Fields.Refresh
  Set fld = tbl.Fields("Proveedor Datos")
  fld.AllowZeroLength = False
  fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)
  Set fld = tbl.Fields("Driver")
  fld.AllowZeroLength = False
  fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)

  Set fld = tbl.Fields("Fecha/Hora 1")
  fld.Properties.Append fld.CreateProperty("Description", 10, "Entre los años 100 y 9999. No es Fecha/Hora Extendida")
  fld.Properties.Append fld.CreateProperty("Format", 10, "General Date")
  Set fld = tbl.Fields("Fecha/Hora 2")
  fld.Properties.Append fld.CreateProperty("Description", 10, "Entre los años 100 y 9999. No es Fecha/Hora Extendida")
  fld.Properties.Append fld.CreateProperty("Format", 10, "General Date")
  Set fld = tbl.Fields("Fecha/Hora 3")
  fld.Properties.Append fld.CreateProperty("Description", 10, "Entre los años 100 y 9999. No es Fecha/Hora Extendida")
  fld.Properties.Append fld.CreateProperty("Format", 10, "General Date")
  Set fld = tbl.Fields("Fecha/Hora 4")
  fld.Properties.Append fld.CreateProperty("Description", 10, "Entre los años 100 y 9999. No es Fecha/Hora Extendida")
  fld.Properties.Append fld.CreateProperty("Format", 10, "General Date")

  CurrentProject.Connection.Execute "CREATE UNIQUE INDEX PrimaryKey ON [Tabla Fechas] (Id ASC) WITH PRIMARY"

Sub_Exit:
    Set fld = Nothing
    Set tbl = Nothing
    If Not db Is Nothing Then db.Close
    Set db = Nothing
    Exit Sub

Sub_Err:
    MsgBox "Se ha producido un error en 'CreaTablaFechas'." & vbNewLine & vbNewLine & vbTab & Err.Description, , "Error Nº: " & Err.Number
    Resume Sub_Exit
    Resume
End Sub

MS Access: Excel Sheet Dates

To correctly understand how dates work in Excel, it is recommended to read and understand the section: Excel Dates.

First of all, it should be noted that MS Access does not have data types to store only dates or only times. The same data type serves for both. Like Excel, MS Access uses a reference date to display only times: 12/30/1899 (the same as in Excel).
The cell range "A12:D12" has a Custom Cell Format "hh:mm:ss,000" and Excel stores it as a Date (12/30/1899) with Time.
However, the cell range "A13:D13" has a Cell Format "Text". Excel To Database is able to interpret it correctly, add the reference date, and store it correctly in MS Access.

MS Access only accepts dates between the years 100 and 9999. Only "Extended Date/Time" type fields accept a larger range and precision.
MS Access has a precision of seconds, so the drivers (ODBC and OLE DB) truncate data that contains greater precision. It is possible that in future versions of Excel To Database an option will be available to perform rounding in such cases.

• Date Conversion Error

  In the Excel Sheet, the data contained in the ranges "B2:D2" and "B7:D7" correspond to dates from years that were not leap years. The data in the ranges "B6:D6" and "B11:D11" contain errors.
  Excel does NOT recognize the data as a Date and fails at step 2. Excel To Database indicates this with the following messages (in the first error, the formats used to convert the data are indicated).
  With the option "No Conversion Errors", those data would have been replaced by the Default Value indicated in the Mappings Panel, and the data loading phase could have been launched.

  A special case is the data in cell "D2" (02/29/1900). Excel recognizes it as a valid date (Cell Format "Date") and Excel To Database will load it as 02/28/1900, as that date never existed.
  However, the data in cell "D7" ("02/29/1900") is not recognized as a date by Excel (Cell Format "Text") and when Excel To Database attempts to convert this data, a "Type Conversion Error" will occur because that date never existed.

  Code

  Cell: B2, Value: 29/02/1700, Table: Tabla Fechas, Column: Fecha/Hora 2
      Cell content could not be converted to data type DateTime
  
  For dates formatted as text, these formats are supported:
  dd/MM/yyyy
  dd/MM/yy
  d/MM/yy
  d/M/yy
  d-M-yy
  dd-MM-yy
  dd.MM.yy
  yyyy-MM-dd
  dddd, d' de 'MMMM' de 'yyyy
  dddd d' de 'MMMM' de 'yyyy
  d' de 'MMMM' de 'yyyy
  dddd, d' de 'MMMM' de 'yyyy H:mm
  dddd, d' de 'MMMM' de 'yyyy HH:mm
  dddd, d' de 'MMMM' de 'yyyy H.mm
  dddd, d' de 'MMMM' de 'yyyy HH.mm
  dddd, d' de 'MMMM' de 'yyyy HH'H'mm
  dddd d' de 'MMMM' de 'yyyy H:mm
  dddd d' de 'MMMM' de 'yyyy HH:mm
  dddd d' de 'MMMM' de 'yyyy H.mm
  dddd d' de 'MMMM' de 'yyyy HH.mm
  dddd d' de 'MMMM' de 'yyyy HH'H'mm
  d' de 'MMMM' de 'yyyy H:mm
  d' de 'MMMM' de 'yyyy HH:mm
  d' de 'MMMM' de 'yyyy H.mm
  d' de 'MMMM' de 'yyyy HH.mm
  d' de 'MMMM' de 'yyyy HH'H'mm
  dddd, d' de 'MMMM' de 'yyyy H:mm:ss
  dddd, d' de 'MMMM' de 'yyyy HH:mm:ss
  dddd, d' de 'MMMM' de 'yyyy H.mm.ss
  dddd, d' de 'MMMM' de 'yyyy HH.mm.ss
  dddd, d' de 'MMMM' de 'yyyy HH'H'mm.ss
  dddd d' de 'MMMM' de 'yyyy H:mm:ss
  dddd d' de 'MMMM' de 'yyyy HH:mm:ss
  dddd d' de 'MMMM' de 'yyyy H.mm.ss
  dddd d' de 'MMMM' de 'yyyy HH.mm.ss
  dddd d' de 'MMMM' de 'yyyy HH'H'mm.ss
  d' de 'MMMM' de 'yyyy H:mm:ss
  d' de 'MMMM' de 'yyyy HH:mm:ss
  d' de 'MMMM' de 'yyyy H.mm.ss
  d' de 'MMMM' de 'yyyy HH.mm.ss
  d' de 'MMMM' de 'yyyy HH'H'mm.ss
  dd/MM/yyyy H:mm
  dd/MM/yyyy HH:mm
  dd/MM/yyyy H.mm
  dd/MM/yyyy HH.mm
  dd/MM/yyyy HH'H'mm
  dd/MM/yy H:mm
  dd/MM/yy HH:mm
  dd/MM/yy H.mm
  dd/MM/yy HH.mm
  dd/MM/yy HH'H'mm
  d/MM/yy H:mm
  d/MM/yy HH:mm
  d/MM/yy H.mm
  d/MM/yy HH.mm
  d/MM/yy HH'H'mm
  d/M/yy H:mm
  d/M/yy HH:mm
  d/M/yy H.mm
  d/M/yy HH.mm
  d/M/yy HH'H'mm
  d-M-yy H:mm
  d-M-yy HH:mm
  d-M-yy H.mm
  d-M-yy HH.mm
  d-M-yy HH'H'mm
  dd-MM-yy H:mm
  dd-MM-yy HH:mm
  dd-MM-yy H.mm
  dd-MM-yy HH.mm
  dd-MM-yy HH'H'mm
  dd.MM.yy H:mm
  dd.MM.yy HH:mm
  dd.MM.yy H.mm
  dd.MM.yy HH.mm
  dd.MM.yy HH'H'mm
  yyyy-MM-dd H:mm
  yyyy-MM-dd HH:mm
  yyyy-MM-dd H.mm
  yyyy-MM-dd HH.mm
  yyyy-MM-dd HH'H'mm
  dd/MM/yyyy H:mm:ss
  dd/MM/yyyy HH:mm:ss
  dd/MM/yyyy H.mm.ss
  dd/MM/yyyy HH.mm.ss
  dd/MM/yyyy HH'H'mm.ss
  dd/MM/yy H:mm:ss
  dd/MM/yy HH:mm:ss
  dd/MM/yy H.mm.ss
  dd/MM/yy HH.mm.ss
  dd/MM/yy HH'H'mm.ss
  d/MM/yy H:mm:ss
  d/MM/yy HH:mm:ss
  d/MM/yy H.mm.ss
  d/MM/yy HH.mm.ss
  d/MM/yy HH'H'mm.ss
  d/M/yy H:mm:ss
  d/M/yy HH:mm:ss
  d/M/yy H.mm.ss
  d/M/yy HH.mm.ss
  d/M/yy HH'H'mm.ss
  d-M-yy H:mm:ss
  d-M-yy HH:mm:ss
  d-M-yy H.mm.ss
  d-M-yy HH.mm.ss
  d-M-yy HH'H'mm.ss
  dd-MM-yy H:mm:ss
  dd-MM-yy HH:mm:ss
  dd-MM-yy H.mm.ss
  dd-MM-yy HH.mm.ss
  dd-MM-yy HH'H'mm.ss
  dd.MM.yy H:mm:ss
  dd.MM.yy HH:mm:ss
  dd.MM.yy H.mm.ss
  dd.MM.yy HH.mm.ss
  dd.MM.yy HH'H'mm.ss
  yyyy-MM-dd H:mm:ss
  yyyy-MM-dd HH:mm:ss
  yyyy-MM-dd H.mm.ss
  yyyy-MM-dd HH.mm.ss
  yyyy-MM-dd HH'H'mm.ss
  d 'de' MMMM
  yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK
  ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
  yyyy'-'MM'-'dd'T'HH':'mm':'ss
  H:mm
  HH:mm
  H.mm
  HH.mm
  HH'H'mm
  H:mm:ss
  HH:mm:ss
  H.mm.ss
  HH.mm.ss
  HH'H'mm.ss
  yyyy'-'MM'-'dd HH':'mm':'ss'Z'
  MMMM' de 'yyyy
  MM/dd/yyyy
  dddd, dd MMMM yyyy
  dddd, dd MMMM yyyy HH:mm
  dddd, dd MMMM yyyy hh:mm tt
  dddd, dd MMMM yyyy H:mm
  dddd, dd MMMM yyyy h:mm tt
  dddd, dd MMMM yyyy HH:mm:ss
  MM/dd/yyyy HH:mm
  MM/dd/yyyy hh:mm tt
  MM/dd/yyyy H:mm
  MM/dd/yyyy h:mm tt
  yyyy-MM-dd hh:mm tt
  yyyy-MM-dd h:mm tt
  MM/dd/yyyy HH:mm:ss
  MMMM dd
  hh:mm tt
  h:mm tt
  yyyy MMMM
  
  Cell: B2, Value: 29/02/1700, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <29/02/1700> in Fecha/Hora 2 Column.  Expected type is DateTime.
  
  Cell: C2, Value: 29/02/1800, Table: Tabla Fechas, Column: Fecha/Hora 3
      Cell content could not be converted to data type DateTime
  Cell: C2, Value: 29/02/1800, Table: Tabla Fechas, Column: Fecha/Hora 3
       String was not recognized as a valid DateTime.Couldn't store <29/02/1800> in Fecha/Hora 3 Column.  Expected type is DateTime.
  
  Cell: B6, Value: 00/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 2
      Cell content could not be converted to data type DateTime
  Cell: B6, Value: 00/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <00/00/1900> in Fecha/Hora 2 Column.  Expected type is DateTime.
  
  Cell: C6, Value: 01/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 3
      Cell content could not be converted to data type DateTime
  Cell: C6, Value: 01/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 3
       String was not recognized as a valid DateTime.Couldn't store <01/00/1900> in Fecha/Hora 3 Column.  Expected type is DateTime.
  
  Cell: D6, Value: 00/01/1900, Table: Tabla Fechas, Column: Fecha/Hora 4
      Cell content could not be converted to data type DateTime
  Cell: D6, Value: 00/01/1900, Table: Tabla Fechas, Column: Fecha/Hora 4
       String was not recognized as a valid DateTime.Couldn't store <00/01/1900> in Fecha/Hora 4 Column.  Expected type is DateTime.
  
  Cell: B7, Value: 29/02/1700, Table: Tabla Fechas, Column: Fecha/Hora 2
      Cell content could not be converted to data type DateTime
  Cell: B7, Value: 29/02/1700, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <29/02/1700> in Fecha/Hora 2 Column.  Expected type is DateTime.
  
  Cell: C7, Value: 29/02/1800, Table: Tabla Fechas, Column: Fecha/Hora 3
      Cell content could not be converted to data type DateTime
  Cell: C7, Value: 29/02/1800, Table: Tabla Fechas, Column: Fecha/Hora 3
       String was not recognized as a valid DateTime.Couldn't store <29/02/1800> in Fecha/Hora 3 Column.  Expected type is DateTime.
  
  Cell: D7, Value: 29/02/1900, Table: Tabla Fechas, Column: Fecha/Hora 4
      Cell content could not be converted to data type DateTime
  Cell: D7, Value: 29/02/1900, Table: Tabla Fechas, Column: Fecha/Hora 4
       String was not recognized as a valid DateTime.Couldn't store <29/02/1900> in Fecha/Hora 4 Column.  Expected type is DateTime.
  
  Cell: B11, Value: 00/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 2
      Cell content could not be converted to data type DateTime
  Cell: B11, Value: 00/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <00/00/1900> in Fecha/Hora 2 Column.  Expected type is DateTime.
  
  Cell: C11, Value: 01/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 3
      Cell content could not be converted to data type DateTime
  Cell: C11, Value: 01/00/1900, Table: Tabla Fechas, Column: Fecha/Hora 3
       String was not recognized as a valid DateTime.Couldn't store <01/00/1900> in Fecha/Hora 3 Column.  Expected type is DateTime.
  
  Cell: D11, Value: 00/01/1900, Table: Tabla Fechas, Column: Fecha/Hora 4
      Cell content could not be converted to data type DateTime
  Cell: D11, Value: 00/01/1900, Table: Tabla Fechas, Column: Fecha/Hora 4
       String was not recognized as a valid DateTime.Couldn't store <00/01/1900> in Fecha/Hora 4 Column.  Expected type is DateTime.
  

  Cells D3 and D8 do not match any of the formats that Excel To Database uses to interpret them correctly (yyyy/dd/MM (year/day/month)). Thus, these data also result in Type Conversion Errors. Excel does not interpret them as dates either but does not show any warning. However, the data contained in cells "A4", "D4", "A5", "A9", "D9", and "A10" are interpreted by Excel To Database as "yyyy/MM/dd" (year/month/day), while Excel does not interpret them as dates.

  Código

  Cell: D3, Value: 1899/30/12, Table: Tabla Fechas, Column: Fecha/Hora 4
      Cell content could not be converted to data type DateTime
  Cell: D3, Value: 1899/30/12, Table: Tabla Fechas, Column: Fecha/Hora 4
       String was not recognized as a valid DateTime.Couldn't store <1899/30/12> in Fecha/Hora 4 Column.  Expected type is DateTime.
  
  Cell: D8, Value: 1899/30/12, Table: Tabla Fechas, Column: Fecha/Hora 4
      Cell content could not be converted to data type DateTime
  Cell: D8, Value: 1899/30/12, Table: Tabla Fechas, Column: Fecha/Hora 4
       String was not recognized as a valid DateTime.Couldn't store <1899/30/12> in Fecha/Hora 4 Column.  Expected type is DateTime.
  

• ODBC and Anglo-Saxon Chauvinism: 1753

  As already indicated, MS Access does not accept dates earlier than January 1, 100. Excel To Database indicates this with a message and does not launch the data loading phase.

  Código

  Cell: B5, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
      Value less than the minimum date 01/01/0100 00:00:00 supported by the Column or Data Provider.
  Cell: B5, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <31/12/0099> in Fecha/Hora 2 Column.  Expected type is DateTime.
  
  Cell: B10, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
      Value less than the minimum date 01/01/0100 00:00:00 supported by the Column or Data Provider.
  Cell: B10, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <31/12/0099> in Fecha/Hora 2 Column.  Expected type is DateTime.
  

  However, when using the ODBC Data Provider, this date limit is pushed back to January 1, 1753. Excel To Database is also aware of this and indicates it with the relevant message.

  Código

  Cell: B5, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
      Value less than the minimum date 01/01/1753 00:00:00 supported by the Column or Data Provider.
  Cell: B5, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <31/12/0099> in Fecha/Hora 2 Column.  Expected type is DateTime.
  
  Cell: C5, Value: 01/01/0100, Table: Tabla Fechas, Column: Fecha/Hora 3
      Value less than the minimum date 01/01/1753 00:00:00 supported by the Column or Data Provider.
  
  Cell: D5, Value: 31/12/1752, Table: Tabla Fechas, Column: Fecha/Hora 4
      Value less than the minimum date 01/01/1753 00:00:00 supported by the Column or Data Provider.
  Cell: D5, Value: 31/12/1752, Table: Tabla Fechas, Column: Fecha/Hora 4
       String was not recognized as a valid DateTime.Couldn't store <31/12/1752> in Fecha/Hora 4 Column.  Expected type is DateTime.
  
  Cell: B10, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
      Value less than the minimum date 01/01/1753 00:00:00 supported by the Column or Data Provider.
  Cell: B10, Value: 31/12/0099, Table: Tabla Fechas, Column: Fecha/Hora 2
       String was not recognized as a valid DateTime.Couldn't store <31/12/0099> in Fecha/Hora 2 Column.  Expected type is DateTime.
  
  Cell: C10, Value: 01/01/0100, Table: Tabla Fechas, Column: Fecha/Hora 3
      Value less than the minimum date 01/01/1753 00:00:00 supported by the Column or Data Provider.
  
  Cell: D10, Value: 31/12/1752, Table: Tabla Fechas, Column: Fecha/Hora 4
      Value less than the minimum date 01/01/1753 00:00:00 supported by the Column or Data Provider.
  Cell: D10, Value: 31/12/1752, Table: Tabla Fechas, Column: Fecha/Hora 4
       String was not recognized as a valid DateTime.Couldn't store <31/12/1752> in Fecha/Hora 4 Column.  Expected type is DateTime.
  

  If the checks performed by Excel To Database are not used, the error message that would appear in the .log files would be similar to the following:

  Código

  Command: INSERT INTO [Tabla Fechas] ([Fecha/Hora 1] [Driver], [Proveedor Datos]) VALUES (?, ?, ?)
  RowError: 
  Data of the row to insert into the table:
      Column: Fecha/Hora 1    Value: 01/01/0100 0:00:00
      Column: Driver    Value: Microsoft Access Driver (*.mdb, *.accdb)
      Column: Proveedor Datos    Value: ODBC
  An exception occurred while trying to load table 'Date Table'.
      Query indicating the columns used in the table:
      SELECT [Fecha/Hora 1], [Driver], [Proveedor Datos] FROM [Tabla Fechas]
  {
    "$id": "1",
    "$type": "System.Data.Odbc.OdbcException, System.Data",
    "Errors": {
      "$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
      "$values": [
        {
          "$id": "2",
          "$type": "System.Data.Odbc.OdbcError, System.Data",
          "Message": "[Microsoft][ODBC Microsoft Access Driver]Datetime field overflow ",
          "SQLState": "22008",
          "NativeError": 36,
          "Source": "ACEODBC.DLL"
        }
      ]
    },
    "Source": "ACEODBC.DLL",
    "ErrorCode": -2146232009,
    "Message": "ERROR [22008] [Microsoft][ODBC Microsoft Access Driver]Datetime field overflow ",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "HResult": -2146232009
  }
  

  What is the cause of this error? As usual, historical reasons: 1515. THE UNIVERSITY OF SALAMANCA ISSUES A REPORT FOR THE CALENDAR REFORM:
  Back in 1515, the scientists and mathematicians of the University of Salamanca wrote a report highlighting the inconsistencies of the calendar in use at the time (established by Julius Caesar 46 years before the birth of Christ). In 1578, they wrote a second report, which was then considered by Pope Gregory XIII, who in the bull "Inter Gravissimas" promoted the change of calendar. His Catholic Majesty, Philip II, through a pragmatism, on September 29, 1582, implemented this change throughout his Empire. However, England did not adopt the Gregorian calendar until 1752.
  Thus, due to the chauvinism of that culture, during the development of SQL Server and other technologies, they set the minimum date as January 1, 1753. Microsoft has removed articles referring to this issue, but references to them can still be found on other pages, such as: Access ODBC returning incorrect dates prior to the year 1753 or on What is the significance of 1/1/1753 in SQL Server?.
  

  This does not happen with the OLE DB Data Provider.

  However, with dates prior to January 1 of year 100, if the checks that Excel To Database performs are not made, messages similar to the following would appear in the .log file:

  Código

  Command: INSERT INTO [Tabla Fechas] ([Fecha/Hora 1] [Driver], [Proveedor Datos]) VALUES (?, ?, ?)
  RowError: 
  Data of the row to insert into the table:
      Column: Fecha/Hora 1    Valor: 01/01/0099 0:00:00
      Column: Driver    Valor: Microsoft.ACE.OLEDB.16.0
      Column: Proveedor Datos    Valor: OLE DB
  An exception occurred while trying to load table 'Date Table'.
      Query indicating the columns used in the table:
      SELECT [Fecha/Hora 1], [Driver], [Proveedor Datos] FROM [Tabla Fechas]
  {
    "$id": "1",
    "$type": "System.OverflowException, mscorlib",
    "Message": "OleAut is not a valid date.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "System.Data",
    "HResult": -2146233066
  }
  

  If, on the other hand, the option "No Conversion Errors" is selected, these values will be replaced by the default value included in the Mappings Panel, and the data load can be performed correctly.

MS Access: OLE DB – ODBC

The only possible recommendation is for the user to perform the necessary tests before putting their data loads into production. This is nothing new, as they should perform the same operation regardless of the loading system they choose, as in the end, the data providers and drivers will be the same.

Highlighting the issues for the OLE DB Data Provider: decimal separator, strange rounding, date limits.
With ODBC: data truncation and date limits.
In general, for MS Access, the use of the Decimal data type is not recommended due to the issues it causes in connections as well as internal problems within MS Access.