Using the Large Number data type

Applies to: Access for Microsoft 365, Access 2019, Access 2016.

The Large Number data type stores a non-monetary, numeric value and is compatible with the SQL_BIGINT data type in ODBC. Use this data type to efficiently calculate large numbers.Large Number data type range

You can add it as a field to an Access table. You can also link to or import from databases with a corresponding data type, such as the SQL Server bigint data type. To add the Large Number data type, you need Access 2016 (16.0.7812 or later).

In this article

Adding a Large Number field to a tableLinking to or importing from an external database with SQL_BIGINT data type supportUnderstanding the impact of Large Number data type supportBackward Compatibility Considerations

Adding a Large Number field to a table

The Large Number data type (eight bytes) offers you a substantially wider range for calculation than the Number data type (four bytes). For example, the Number data type has a range of -2^31 to 2^31-1 but the Large Number data type has a range of -2^63 to 2^63-1. For more information, see Introduction to data types and field properties.

Once you insert a Large Number field to a table and save the table design, you have practically permitted the Large Number data type, and the database has no functionality with former Access versions. Before you save your table design, you are alerted with a warning message in the event you require preserving the database compatibility. For more information, see Backward Compatibility Considerations.

Top of Page

Linking to or importing from an external database with SQL_BIGINT data type support

You can even use the Large Number data type to work quickly with linked or imported data, such as an SQL Server database that uses the bigint data type. Before the Large Number data type support, Access transformed the applicable data type to the Short Text data type.

With the Large Number data type activated for linking and importing operations, you can link to and import from external data sources by applying an ODBC driver that supports the SQL_BIGINT data type, comprising a primary key according to that data type. For example, you can employ the ODBC driver installed with Windows to connect to these external data sources, or you can connect to other databases in Access 2016 file format (.accdb) with Large Number fields.

Enabling the Large Number data type

Automatically, the Large Number data type is disabled for linking and importing operations. But you can explicitly enable support by setting the Support Bigint Data Type for Linked/Imported Tables Access option. After you attempt to set this option, you are alerted with a warning message in case you need to keep the database compatible. When the option is set, the database stops being backward compatible with previous Access versions.

For more information, see Set user options for the current database and Backward Compatibility Considerations.

Existing tables are not automatically converted

Activating Large Number data type support does not promptly edit the data type of current tables. You might have already linked to or imported from a data source using an ODBC driver that supports the SQL_BIGINT data type (such as the SQL Server bigint data type). In both cases, Access changed the field to the Short Text data type. To edit the data type to Large Number, do the following:

Linked table – Enable the BigInt support option and then Refresh the linked tables by selecting the Linked Table Manager (Choose External Data > Linked Table Manager, pick the relevant tables, and then press OK). This updates the column from the Short Text to the Large Number data type.

Imported Table  –  Alter the field including the Short Text data type to the Large Number data type (Access the table in Design view, click the field in the Data Type column, select Large Number from the list of data types, and then save your changes).

Top of Page

Understanding the impact of Large Number data type support

Before you start employing the Large Number data type, it’s crucial that you understand the effect may have on your Access databases.

Stamping of Access 2007-2016 file format (.accdb) to Access 2016

To summarise, there are two ways you can allow support of the Large Number data type: after you append a field to a local table with the Large Number data type and when you set the Support Bigint Data Type for Linked/Imported Tables Access option. However you permit support of the Large Number data type, it is a perpetual revision to the database that cannot be reversed. In each case, Access presents a warning message before you apply the change.

Beneath the surface, enabling the Large Number data type marks the Access 2007-2016 file format (.accdb) to Access 2016. Stamping a database file format means you have set a unique change to the file format, the database version is raised, but nevertheless, the file format stays consistent. Once you endeavour to enter the database in Access 2013 or Access 2016, Access scans the stamp as follows:

  • If the database version is under than 16.7, the Large Number data type is disabled, and you can access the database.
  • If the database version 16.7 or above, the Large Number data type is activated, you can only enter the database in Access 2016.

Summarising Large Number data type support for product versions

The following table emphasises the potential situations you could experience once using the Large Number data type for various product versions. Pay attention to the fact that enabling happens after you import the Large Number data type to a table and save the design or format the option to enable linking and importing.

Tip: For more information on viewing the product version number, see What version of Office am I using?

Status of the Large Number Data TypeProduct version: Access 2013 and Access 2016 version number lower than 16.0.7812Product version: Access 2016 version number 16.0.7812 or higher
EnabledYou cannot open the database and you receive an error message with a help link.
To work around this situation, see “Remove Large Number data type support from a database in Access 2007-2016 file format”
You can open the database.Local table fields defined with the Large Number data type are treated as large numbers.
You can refresh linked tables. If the columns were previously treated as the Short Text data type, they are automatically converted to the Large Number data type.
Not enabledYou can open the database.You can refresh linked tables. If columns in the linked table are based on the BigInt data type and were converted to the Short Text data type, they remain so.The database file version remains the same.You can open the database.You can refresh linked tables. If the columns in the linked table are based on the BigInt data type and were converted to the Short Text data type, they remain so.The database file version remains the same.

Note: The Large Number data type is not supported under any circumstances with the Access file formats (.mdb) prior to the Access 2007-2016 file format.

Determine whether an Access database has the Large Number data type enabled

You might adopt an Access database and want to ascertain if the Large Number data type is activated. You can do the following:

Examine the title bar  – If the database has been stamped, the title bar mentions <database name> (Access 2016). On rhe other hand, the title bar says <database name> (Access 2007-2016).

Examine programmatically – See the existing database version number. Press CTRL+G to present the Visual Basic Immediate Window, type ?CurrentDb().Version and then press Enter. If the return value is under 16.7, support is disabled. If the return value is 16.7 or greater, then support is enabled.

Top of Page

Backward Compatibility Considerations

It’s vital that you understand your database environment if you seek to preserve compatibility with dynamic file formats. For more information about all Access file formats, see Which Access file format should I use?

Here are three alternatives to consider.

Maintain your current shared database file format environment

To keep a shared database file format environment with databases in Access 2007-2016 file format (.accdb) and prior file formats (.mdb), do the following:

  • Avoid importing the Large Number data type as a column to any table.
  • Refrain from enabling the Large Number data type for linking and importing operations. For more information, see Set user options for the current database.

Add Large Number data type support to a database in Access 2007-2016 file format

To append Large Number data type support to a database in Access 2007-2016 file format (.accdb), do the following:

  1. Enter the database in Access 2016 (16.0.7812 or higher).
  2. Activate the Large Number data type for linking and importing operations. For more information, see Select settings for how data is displayed and stored in your database.
  3. Refresh the linked tables by applying the Linked Table Manager (Select External Data Linked Table Manager, pick the relevant tables, and then press OK). OR Create a field with the Large Number data type to a table and save the table design.

Consequently, fields that contained the Short Text data type are modified to the Large Number data type.

Remove Large Number data type support from a database in Access 2016 file format

You are unable to enter a database in Access 2016 file format from versions of Access prior to Access 2016. To overcome this problem, do the following:

  1. Design a new database in the 2007-2016 file format and check the Large Number data type support for linking and importing operations is disabled. For more information, see Set user options for the current database.
  2. In the new database, import your desired objects from the initial database.
  3. In the new database, link to the tables from the first database.

As a result, fields that included the Large Number data type are transformed to the Short Text data type, and the new database is backward compatible with all Access 2007-2016 file formats (.accdb).

Top of Page

See Also

Choose between the 64-bit or 32-bit version of Office

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: