I recently started working with access and there's something that so far has cause me no problems but I'm concern that it could bring me some issues as the database continues expanding.
When I create tables, Microsoft Access recommend to use their default primary key, which I usually do, the problem is that for some reason when the table get populated the primary key "ID" keeps being inconsistent, it will go from 4 to 2679 (just random example) and it skip lots of numbers, If I'm correct this primary key get set as auto increment automatically, correct? so why is it skipping all numbers in between?
The Table gets populated with a simple SQL query using Visual Studio and C# language. See below a photo from my access table
enter image description here
SQL Server used to do that (in v6.0/6.5 and possibly later ones). It's quite conceivable that Access uses te same mechanism.
IDENTITY works by having the next number (or last, who cares) stored on disc in the DB. To speed up access it is cached in memory, and only occasionally written back to disc (it is SQL Server after all). Depending on how SQL Server was shut down the disk update might be missed. When the server was restarted is had some way of detecting that the disc version was stale and would up it by some number.
Oracle does the same with SEQUENCE's. This got complicated on multi-machine cluster installations where there are multiple servers for the same database. To support this, the first time a server had to get a sequence number it got a lot of them (the Cache variable part of a SEQUENCE's definition, default 20 IIRC) and updated the SEQUENCE assuming that it would use all of the numbers assigned. If it didn't use all the numbers assigned then there would be gaps in the numbers used. (It also meant that with a SEQUENCE in a cluster, the SEQUENCE numbers would not necessarily be used sequentially: machine A writes 21, B writes 41, A writes 22, etc.) I've never checked but I assume that a SQL Server in a fail-over cluster might have the same gaps.
Apply the same mechanism to Access where there is no central server for the DB, just potentially lots of local ones on each client's machine. You can see that there is the potential for gaps.