4 ms·

MS Access “allowed” it but depending on the version it could be quite problematic. We had a use case where Tableau connected to an Access file read only (but wh

by wenc 7y ago

MS Access “allowed” it but depending on the version it could be quite problematic. We had a use case where Tableau connected to an Access file read only (but which another program used as a data store and wrote to it often) on an Windows file share, and once in a while the lock files would get screwy and we would have to manually delete the lock files to get things working again. Deleting lock files could be a huge chore because you have to figure out stuck processes and kill them. Task Manager wasn’t up to the task, so had to use SysInternal tools to help with that. Access is really meant for single-user scenarios I feel. Maybe the locking mechanism has gotten better but for multiuser access I tell people to use a real SQL database.

velox_io 7y ago
Yeah, Access' Jet Engine and many of its issues are very well known. [https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine]
slfnflctd 7y ago
> Access is really meant for single-user scenarios In a lot of cases, I've found it to be the best tool for a temporary or one-off (preferably smaller scale) data mining/massaging project. The query-building interface was the way I originally learned the basics of relational databases, and it also helped me get a better grasp of SQL-- the ability to flip back & forth from the GUI query builder to the SQL it generates is nice. On the multiuser side, however, I have found a couple workarounds in the past. If you have everyone operate locally and space out their central database connections to intermittent, automated burst queries, you can get more concurrent users than you might expect. It helps to have fewer users per table, as well, and of course it really helps if they don't need to see the most recent adds/changes in real time.
- bshipp 7y ago
  My recollection was that the automated wizard loved to nest statements and it was a huge chore to manually massage stuff after the fact; almost incomprehensible to parse. but I agree that it was a real godsend for people trying to branch out from the limitations of excel.
m0xte 7y ago
It was ok pre-windows 2000. I had access 97 it on 9 workstations on NT with never a problem! Moment office 2000 and windows 2000 came out it went to hell. Moved to SQL Server then. I am not sure what changed. It was quite frankly the most productive custom business software package I have ever used. Literally would take 10 people a week to do something custom you could do in an afternoon with 1 person in Access. I suspect the same is true now.
- JasonFruit 7y ago
  We had a much heavier multiuser setup on Access 97, and it worked fine. We had the same breakdown as you did, and had to do a registry edit on all users' machines to keep it limping along until we could move to SQL Server, which was the right thing to do anyway.
  - m0xte 7y ago
    Interesting. Nice to know it wasn't just me. There was no one to talk to about it back then so you had to think on your toes. SQL Server was a crap load easier to back up reliably

by wenc 7y ago

velox_io 7y ago

Yeah, Access' Jet Engine and many of its issues are very well known. [https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine]

slfnflctd 7y ago

> Access is really meant for single-user scenarios In a lot of cases, I've found it to be the best tool for a temporary or one-off (preferably smaller scale) data mining/massaging project. The query-building interface was the way I originally learned the basics of relational databases, and it also helped me get a better grasp of SQL-- the ability to flip back & forth from the GUI query builder to the SQL it generates is nice. On the multiuser side, however, I have found a couple workarounds in the past. If you have everyone operate locally and space out their central database connections to intermittent, automated burst queries, you can get more concurrent users than you might expect. It helps to have fewer users per table, as well, and of course it really helps if they don't need to see the most recent adds/changes in real time.

bshipp 7y ago
My recollection was that the automated wizard loved to nest statements and it was a huge chore to manually massage stuff after the fact; almost incomprehensible to parse. but I agree that it was a real godsend for people trying to branch out from the limitations of excel.

m0xte 7y ago

It was ok pre-windows 2000. I had access 97 it on 9 workstations on NT with never a problem! Moment office 2000 and windows 2000 came out it went to hell. Moved to SQL Server then. I am not sure what changed. It was quite frankly the most productive custom business software package I have ever used. Literally would take 10 people a week to do something custom you could do in an afternoon with 1 person in Access. I suspect the same is true now.

JasonFruit 7y ago
We had a much heavier multiuser setup on Access 97, and it worked fine. We had the same breakdown as you did, and had to do a registry edit on all users' machines to keep it limping along until we could move to SQL Server, which was the right thing to do anyway.
- m0xte 7y ago
  Interesting. Nice to know it wasn't just me. There was no one to talk to about it back then so you had to think on your toes. SQL Server was a crap load easier to back up reliably