[Lazarus] MS Access file - is it 'accessible' concurrently ?

[Reinier Olislagers]

On 7-9-2011 18:00, Dimitri Smits wrote:
> 
> ----- "Lukasz Sokol" <el.es.cr at gmail.com> schreef:
>> Hi Group,
>>
>> http://wiki.lazarus.freepascal.org/ODBCConn
>> http://wiki.lazarus.freepascal.org/MS_Access
>>
>> do not contain this information :
>>
>> Can I open the same database and access (read/write) 
>> the same (few) table(s) from 2 or more concurrently
>> running programs (written in Lazarus of course) using
>> the MDB (MS Office Access) as the underlying file? 
>>
>> Or will I need (MS) (SQL) Server?
>>
>> Pointers welcome :)
>>
> 
> Access ALWAYS was intended for non-concurrent access (pun intended :-))!
> 
> When you want to use concurrent access, you should use MS SQL if you want to stay in the same/similar dialect/syntax realm.
> 
> The problem stems in part from the fact that it is an embedded DB engine (even if over ODBC) as opposed to a dedicated, dispatching and mitigating server in a client/server setting that handles the concurrency issues.
Eeehm, yes, it is a file-based database system.
Still, Access developers have succesfully (and cheaply) been developing
quite reliable, split Access front-end (GUI)/back end (data) solutions
for department-level use.
(I used to be one, a long time ago).

However, Dimitri is correct in that doesn't seem a good idea to use an
Access file for new development, especially in Lazrus.

As of now, you'd have to use ODBC in any case for SQL Server and Access.
IIRC, transaction support in FPC ODBC is not present yet.

If that is a factor for you, you might want to look at other databases,
such as Firebird (easy to run as embedded, too), PostgreSQL, Oracle, or
perhaps MySQL.

As for your plan (in your other post) to use another database and keep
the Access DB for reading, that looks ok to me, but you might even want
to consider just datapumping everything over immediately, maybe to a
different database:
Maintaining 2 access layers, with dependencies on 2 database
(servers/systems), less need to rewrite
vs
1 access layer, but need to migrate data/probably program logic to the
new db, maybe with conversion issues.

Last option would be to just leave it in an .mdb file and see how far
you get, but have the client/server database server standing by...

Regards,
Reinier