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 :)

Lukasz

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.

kind regards,
Dimitri Smits

Dimitri Smits wrote

Access ALWAYS was intended for non-concurrent access (pun intended :-))!

Yeah, good one, just like I thought... :)

When you want to use concurrent access, you should use MS SQL if you want to stay in the same/similar dialect/syntax realm.

Hmm, not really, this isn't _really_ needed.

I see.

Hence my plan would rather be - because the mdb file comes from older version of the system which I'd rather preserve in whatever shape it is, for backwards compatibility, the 'logic' will be connecting to it, reading configs etc.

And for feeds <-> logic traffic, I'll use separate database (with a Server)... If that works OK, I'd have less problems migrating from mdb into the Server later :)

Thanks,
Lukasz

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

Reinier Olislagers wrote

Eeehm, yes, it is a file-based database system. Still, Access developers have succesfully (and cheaply) been developing...

+1 on everything Reiner has said!

I forwarded this answer to our helpdesk.
They're now all in the reanimation section of the nearest hospital, after which they'll be held for 6 months of treatment in the local psychiatric ward.

"Concurrent", "MS-Access" and "reliable" in one sentence was just too much for them...

Michael.

Short answer is Yes you can..... but....

Long answer: ...that was not its indented use! We have a system where 30-40 clients connect to a single MDB file on a network share. The only reason we are getting this right is because there is a very small chance that any of those clients will access the same data. This behaviour was known at design time. Also, our system was designed using VB6 some 13 years ago, and still running today (but hopefully not for too long still).

Back then we didn't have as much choice in cheap [and reliable] database servers, so opted for MS Access. If you are starting a client/server program today, I would highly recommend you rather use a real RDBMS. Firebird RDBMS is an excellent choice. It's dirt cheap (free), and is very small to deploy, and the server doesn't need much in terms of maintenance. MS SQL-Server on the other hand is very expensive, you need a well trained DB Admin to manage and maintain it, and is a *huge+complex* install. I'm certified in MS SQL Server too.

Yes I know MS SQL-Server now has a "baby" version which is cheaper or free. But why settle for a "limited functionality" RDBMS when Firebird RDBMS is the full blown thing, has excellent stability, excellent performance and FREE.

Michael Van Canneyt wrote

"Concurrent", "MS-Access" and "reliable" in one sentence was just too much for them...

LOL! So true!

Our VB6+Access system is deployed at over 250 franchises. We have constant database corruption, or people "accidentally" deleting the MDB file on the network share etc... This is why I said in my earlier message "hopefully not for too long".

We are slowly but surely converting those franchisees over to our new system which is developer with FPC+fpGUI+tiOPF2 and Firebird RDBMS. A MUCH more robust system than before!

@Original Poster
I never even touched on database connectivity in my earlier message. With Firebird there are many database access component to choose from (IBX, SqlDB, FBLib etc..), and Firebird is fully cross-platform enabled (unlike MS-Access or MS SQL-Server), so you can deploy it on Windows, Linux, Mac etc..

Sorry I was the cause for these medical bills. Hope you can get a nice fresh new crop from college or something ;)

Might go to show how much your helpdesk people know of good Access development (yes, that's not an oxymoron) ;)
Did I mention department-level use, thereby limiting the number of concurrent users, a good front end/back end design etc??

But who cares, you brought up a valid point: the Access reputation has been tarnished by its success (easy to slap together quickly) & the corresponding botched "development" jobs by fly by night cowboys that earned the well-deserved scorn of real IT people.
Another reason I won't go into details regarding Access is because with Lazarus/FPC, you don't need to needlessly constrain yourself to a closed platform that only runs on Windows (and not on OSX, Linux)

Graeme brought up another valid point: yes you can install SQL Server express, but why not install Firebird instead.
(There was a reason I specified Firebird first in the client/server dbs the OP could try).

I'm going to crawl back into my hole now and dream of proper CSV export in FPC, something a certain database product has been able to do well for quite some time ;)

With my sympathy to your former helldesk people,
Reinier

May be interesting for how much craic is needed to understand that ;)

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

OK. Not surprised ;)

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.

What does it have instead ? Table/row locking ?

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.

Which one requires least setup/dependencies and has least steep learning curve ?

I came across a program once, that is supposed to run the same on MySQL and on Postgres (and AFAIK it does), but could have a 3rd party module installed that only works correctly on MySQL... (FYI - StyLite's EGroupware vs ProjectERP (PERP)) so there I learned there are sub(tle|stantial) differences between them - how does Firebird relate to that ?

Thing is - I need to consider my options for the dynamic part (feeder-logic communication table) now, as the new system is being developed, while still being able to run both the new the old system on the old mdb file, for as long as it's needed; and then, there is the other purpose the MS Access was used - it was creating Reports.

So until my conversion is feature-complete, I guess I will use MS Access and the database server connection concurrently.

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...

Or as I wrote above, writes to the db-server and the mdb will happen concurrently so I can determine what really needs to be where.

Regards, Reinier

Thanks,
Lukasz

Firebird is the easiers and smallest by FAR! At it has many more feature - at least compared to MySQL. So small doesn't mean less features here.

A full Firebird Server download is a mere 8-12MB download. It can even run on a Windows 98 system if you wanted, so resource using in minimal too (obviously depending on how many concurrent connections you handle). A client-only install is even smaller, but you can use the same setup files, just select "client libraries only" during the install.

Even installing Firebird from a tar.gz file on Linux (not via a distro repository), it takes no more than 2 minutes to setup.

I came across a program once, that is supposed to run the same on MySQL and on Postgres (and AFAIK it does), but could have a 3rd party module installed that only works correctly on MySQL...

We have tested our company applications on Firebird, MySql and PostgreSQL. Our application behaved the same on all, though the queries where quite a bit slower on PostgreSQL for some reason. So maybe that 3rdParty component was rubbish. MySQL just sucks with all it's limited features. MySQL is only popular because of marketing and hype (eg: LAMP) - not because of features and stability.

But which variant of Firebird? If I recall correctly there are three, I was prodding at it a bit a few months ago and came to the conclusion that Firebird classic might be a viable alternative to PostgreSQL since it provided an equivalent to the listen/notify commands.

As far as I know, they are equivalent in exposed functionality since version 2.1.

The only difference is how they handle concurrent connections, multiple CPU cores and the cache.

Michael.

+1

Just for interest sake, I'm using Firebird SuperServer 2.5 on my development machine. We also run Firebird SuperServer 2.1.3 on our server.

Does that include asynchronous notifications (I forget what Firebird calls these, but similar to Postgres's listen/notify) across sessions and users? I am very likely wrong, but I got the impression that one of the variants was file-access-only, and wasn't expecting that one to handle notifications.

That's cool.

Even installing Firebird from a tar.gz file on Linux (not via a distro repository), it takes no more than 2 minutes to setup.

I use Windows (blush) XP 32 bit and Lazarus 0.9.28.2... (which is an improvement since the original system is written in VC++ 6.0)

Oh well, something to think about when time comes to integrate this, for now it's nowhere near that stage, yet.

[...]

Yeah, heard that too.

and just when I thought all's jolly good,

:)

Lukasz

Not, I must stress, that I've got any intention of abandoning Postgres. However, I've got something lined up that could benefit from multiple layers of database where having asynchronous notifications is essential, so I was very interested to find that Firebird could possibly be used as a lighter-weight alternative for the "middleware".

Mark Morgan Lloyd wrote

Lukasz Sokol wrote:

[...]

Hence my :)

FB looks promising. But it's not the stage to add it yet, so I have some time to research :)

Lukasz