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.
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
|
Linking to or importing from an external database with SQL_BIGINT data type support |
Adding a Large Number field to a table
The Large Number data type (eight bytes) gives you a much greater 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.
When you add a Large Number field to a table and save the table design, you have effectively enabled the Large Number data type, and the database is no longer compatible with previous Access versions. Before you save your table design, you are notified with a warning message in case you need to keep the database compatible. For more information, see Backward Compatibility Considerations.
Linking to or importing from an external database with SQL_BIGINT data type support
You can also use the Large Number data type to work efficiently 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 converted the equivalent data type to the Short Text data type.
With the Large Number data type enabled for linking and importing operations, you can link to and import from external data sources by using an ODBC driver that supports the SQL_BIGINT data type, including a primary key based on that data type. For example, you can use 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
By default, the Large Number data type is not enabled for linking and importing operations. But you can explicitly enable support by setting the Support Bigint Data Type for Linked/Imported Tables Access option. When you attempt to set this option, you are notified with a warning message in case you need to keep the database compatible. Once the option is set, the database is no longer 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
Enabling Large Number data type support does not automatically change the data type of existing tables. You may have previously 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 converted the field to the Short Text data type. To change the data type to Large Number, do the following:
Linked table enable the BigInt support option and then Refresh the linked tables by using the Linked Table Manager (Select External Data > Linked Table Manager, select the relevant tables, and then select OK). This converts the column from the Short Text to the Large Number data type.
Imported Table Change the field containing the Short Text data type to the Large Number data type (Open the table in Design view, select the field in the Data Type column, choose Large Number from the list of data types, and then save your changes).
Understanding the impact of Large Number data type support
Before you start using the Large Number data type, It’s very important that you understand the impact it could have on your Access databases.
Stamping of Access 2007-2016 file format (.accdb) to Access 2016
To summarize, there are two ways you can enable support of the Large Number data type: when you add 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 enable support of the Large Number data type, it is a permanent change to the database that cannot be reversed. In each case, Access displays a warning message before you make the change.
Behind the scenes, enabling the Large Number data type stamps the Access 2007-2016 file format (.accdb) to Access 2016. Stamping a database file format means you have made a specific change to the file format, the database version is increased, but otherwise the file format remains the same. When you attempt to open the database in Access 2013 or Access 2016, Access reads the stamp in the following way:
-
If the database version is lower than 16.7, the Large Number data type is not enabled, and you can open the database.
-
If the database version 16.7 or higher, the Large Number data type is enabled, you can only open the database in Access 2016.
Summarizing Large Number data type support for product versions
The following table summarizes the possible situations you may encounter when using the Large Number data type for different product versions. Note that enabling occurs when you add the Large Number data type to a table and save the design or set 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 Type |
Product version: Access 2013 and Access 2016 version number lower than 16.0.7812 |
Product version: Access 2016 version number 16.0.7812 or higher |
|
Enabled |
You 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 enabled |
You 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 may inherit an Access database and want to determine if the Large Number data type is enabled. You can do the following:
Examine the title bar If the database has been stamped, the title bar says <database name> (Access 2016). Otherwise, the title bar says <database name> (Access 2007-2016).
Examine programmatically View the current database version number. Press CTRL+G to display the Visual Basic Immediate Window, enter ?CurrentDb().Version and then press Enter. If the return value is less than 16.7, support is not enabled. If the return value is 16.7 or higher, then support is enabled.
Backward Compatibility Considerations
It's important that you understand your database environment if you want to maintain backward compatibility with different 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 maintain a shared database file format environment with databases in Access 2007-2016 file format (.accdb) and prior file formats (.mdb), do the following:
-
Do not add the Large Number data type as a column to any table.
-
Do not enable 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 add Large Number data type support to a database in Access 2007-2016 file format (.accdb), do the following:
-
Open the database in Access 2016 (16.0.7812 or higher).
-
Enable 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.
-
Refresh the linked tables by using the Linked Table Manager (Select External Data > Linked Table Manager, select the relevant tables, and then select OK).
OR
Add a field with the Large Number data type to a table and save the table design.
As a result, fields that had the Short Text data type are converted to the Large Number data type.
Remove Large Number data type support from a database in Access 2016 file format
You cannot open a database in Access 2016 file format from versions of Access prior to Access 2016. To work around this issue, do the following:
-
Create a new database in the 2007-2016 file format and make sure the Large Number data type support for linking and importing operations is not enabled. For more information, see Set user options for the current database.
-
In the new database, import the objects you want from the original database.
-
In the new database, link to the tables from the original database.
As a result, fields that had the Large Number data type are converted to the Short Text data type, and the new database is backward compatible with all Access 2007-2016 file formats (.accdb).
