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.
- i want to replace all the fill colors after the word vba cursor position
- i want to get the result of auto-filling with excelvba, excluding the title row (heading)
- an error occurs when a macro using excel driver is executed in 64-bit excel with 32-bit excelvba
- i want to replace a specific character string of data on excel querytable with vba and then output
- cannot print on both sides with excel/vba code
- [excelvba] id, password masking and concealment method used inside vba
- when deleting a string with strikethrough in excelvba, it falls into an infinite loop
- i want to register data in accessdb with excelvba
- data posting according to conditions excelvba
- code error avoidance for excel/vba merged cells
- unable to extract date range from excelvba to sqlserver date field
- vlookup notation in excelvba
- in excelvba, sometimes the last row of a table with ruled lines cannot be taken well
- i want to replace word vba "blue letters" with "red letters"
- when obtaining values using vlookup in excelvba, if there is a space in the search range, it will not be obtained correctly
- [excelvba] when errraise is done after adodbopen is closed
- [excelvba] if statement is skipped in the middle of for statement loop
- i want to search and replace text strings in a slide according to the replacement text mapping table in excel with vba macro of
- vba - i want to replace a specific character string by referring to the correspondence table
- get number of pdf pages with excelvba
- how to create source when posting from workbook to workbook in excel with vba macro
- vba - i want to add item by looping from database class to combobox
- i can't xlwings runpython install
- vba - i want to get data without opening the workbook
- vba - i want to generate another instance of excel and copy the sheet
- vba arrange multiple graphs with line breaks
- java - working with ie in excel vba
- vba - range value is batch-assigned to dynamic array by range error when the number of data is 1 or less
- about exchanging tables with excel vba
- extract and copy vba data