Good concurrency isn't something that's built into the engine, it's
something you design in your table schema. Normalizing your tables so
that users aren't editing the same row in the same table at the same
time works no matter what the engine is. Fetching only needed data to
keep result sets small and elmininating pessimistic locking also
helps. If you have one table and multiple users all editing the same
record at the same time then you'll have problems with any engine.
Switching engines is never a cure for inadequate design, and in the
case of porting an inefficient  Access app to SQL Server, it has often
been found to make concurrency problems worse, not better, since there
is increased overhead involved with SQL Server.

--Mary