Currently, I am using a system that reads and appends the records in the accdb file table from xlsm created with ExcelVBA.
The accdb file is placed on the central PC in the office, and each accdb file is accessed from each of the Excel files copied to other PCs connected via LAN.
Users of this system are only aware that they are opening an excel file created with ExcelVBA, and are not aware that there is an Access file behind them. There are only a few tables in the accdb file, and there are no reports, forms, or action queries.

Currently, from the information seen on the net, Access is originally assumed to be used standalone, and it is vulnerable to multiple simultaneous access, and the number of records in the table in accdb will be 100,000 in the future. I understand that there is also a possibility of exceeding this, and I am worried about this point, so at this time I am thinking about replacing the accdb file with a SQLite file. (It is not possible to introduce a larger DB system such as SQLserver. Because it is used even in places where only simple installation such as moving and copying files)

Therefore, I examined the exchange of accdb and sqlite3 on my own. The following is enumerated, so please point out any misunderstandings or lack of recognition.

(1) If i prepare a table with the same name and structure as in the current accdb file, it is easy to replace it with the sqlite file

(2) If i just use SQLite files in this way, there is no need to install software on each PC, and in that sense, you can simply replace it with an accdb file. Live and do n’t affect user interaction

(3) For ExcelVBA, you only need to rewrite the existing instructions for connecting to the accdb file, as shown below, and you do not need to rewrite the parts related to record editing

acf = accdb path
    conStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source ="&acf
    con.Open (conStr)

(4) If there is a problem, the accdb file can be protected with a password, but the sqlite file cannot do so, so the damage when copied and taken out is large (currently, Opening the accdb file directly will protect you from seeing the table.)

How about it?
Is recognition too sweet?

  • Answer # 1

    I think SQLite is not suitable for such usage.

    Proper usage of SQLite-kandamotohiro


    The correct general rule is to avoid using SQLite if the same database is accessed directly from many machines over the network (without the application server going in the middle) and at the same time.

    In addition, in order to use SQLite with Excel from ADO, it is necessary to install a driver (SQLite ODBC Driver) on each PC, and the record operation code also needs to be changed in most cases. Let's do it.

  • Answer # 2

    SQLite is also not suitable for sharing.
    I think that switching to SQLServer is the most troublesome for Access.
    Move Access data to a SQL Server database using the Upsizing Wizard
    SQLServerExpress is basically free (please check).

    However, in terms of management as a DBMS, it may be better to consider open source RDBMS (Postgres, MySQL), but there may be preference here.

  • Answer # 3

    Because there is a possibility of using Access original syntax in SQL,
    If you don't check it, it may not work after the replacement.

    After that it can be replaced, such as the difference in handling at the time of error, etc.
    It may be necessary to actually move and try.