Too much data for Access - what are options? (2 Viewers)

[Cotswold]

Thanks. Is that free software? Is it similar to Microsoft SQL Server 2022, which is free and can handle 10GB?

MariaDB is basically the same as MySQL and written by the original MySQL team with very few language differences. It is also free to use to the best of my knowledge.

 

[cheekybuddha]

SQLite is not a multi-user database

That is incorrect.

Though caution should be used in a multi-user situation.

From their "When to use" page:

Situations Where A Client/Server RDBMS May Work Better

High Concurrency

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

 

[Cotswold]

SQLite is able to support moderate transaction rates. It is not designed to support the level of concurrent access provided by many client/server RDMS products. Most server systems provide table or row level locking without risk of data loss. Concurrency protection in SQLite depends on file locks to protect against data loss. Whilst it allows multiple database connections the whole SQLite database file must be exclusivly locked for amendments. As a result writes are serialised across all database connections, limiting the overall transaction rate.

But if you think you can get away with it then use it.

 

[cheekybuddha]

Whilst it allows multiple database connections the whole SQLite database file must be exclusivly locked for amendments

From sqlite:

... and no lock lasts for more than a few dozen milliseconds

What apps do you write where users require that kind of concurrency?

Anyway, I wasn't suggesting anyone should use SQLite (note I said caution should be used in a multi-user environment), just that your blanket statement was wrong.

 

[Pat Hartman]

Just a dumb question. Why in the world are you fighting with ACE when you could be using SQL Server Express????? It is free and has import tools also.

Is this a one time load? and you NEVER have to add additional data?

 

[Neros]

You can run BULK INSERT on SQL Server to import .dat files to the database.
Or you can create a SQL Server Integration Services Package to run extract/transform/load and import data to SQL Server. Since Express edition doesn't support this feature, you can use Enterprise or Developer edition.