Precautions for using the ACCESS database

Precautions for using the ACCESS database

1. Define a concept: Access is different from access database.
For most users who use access, access and access databases are the same thing. However, for developers who use access databases, access programs and access databases must be differentiated. The Access Program is the interface for interacting with users (IMAGINE windows Resource Manager and CMD command prompt lines. They are different interfaces). The ACCESS database contains two parts ,*. MDB and jet engines :*. MDB is the file that stores data, and jet Data Engine is the operation *. the API of the MDB file. Shows the relationship between the three elements:

Access.exe | access query analyzer | websites and other applications
--------------------------------------------------------
Jet Data Engine
--------------------------------------------------------
*. MDB File

Currently, the mainstream Windows operating systems (WIN 2000 and later) are installed with the jet engine (usually version 4.0). Therefore, you do not need to install the jet engine separately in most cases, applications Based on access databases can work.

2. The ACCESS database is a file database. The file database does not support concurrent access and network access.
I will not introduce file databases here. Simply put, special files must be in special formats and can only be accessed using dedicated APIs.
The file database does not support concurrent access. The concurrency here refers to the simultaneous execution of two or more write operations. The topic of the write operation may be a process or a thread.
The file database does not support network access. Network access does not include network sharing. Developers who have used databases such as MySQL, Oracle, and SQL server will find that the database must start a listener (not necessarily called a listener) to be accessed by programs on the network, in fact, this listener is the entry to the (database) service. File databases are essentially files rather than services, so network access is not supported.

3. *. The MDB file size is limited to 2 GB.
To be accurate ,*. the upper limit of valid data stored in MDB is 2 GB, and the space occupied by table structure and other management data (meta data) is removed. The actual upper limit of valid data is about 1.8 GB.
In *. mdb, the deleted data is marked as deleted, and the Occupied storage space is not automatically released or used again. After repeated insert and delete operations, the size of *. MDB file will exceed 2 GB, but when the valid data reaches 2 GB, an error will be reported when the insert operation is performed.
To release a space that is no longer in use, you must use the "Data Compression" of the access program or write your own program (some COM components provided by access are required ).
Note: The maximum size of a single file in the FAT32 partition is 4 GB. NTFS does not have this limit.

4. Stored Procedures and Foreign keys.
Supports Simple stored procedures and is edited in "query.
Foreign key, set in "Link.

5. case insensitive. The characters in a record (when comparing characters) are case-insensitive, but the characters displayed (or select) are case-insensitive during insert.

This article only lists some situations I encountered during development. Welcome to the discussion.