4 ms·

I need to find companies/projects like these. I would have loved to solve this problem in one way or another. a) Rewrite as a web-app in platform of choice. b

by chime 16y ago

I need to find companies/projects like these. I would have loved to solve this problem in one way or another. a) Rewrite as a web-app in platform of choice. b) Put the entire DB in RAM on a really fast Windows server using SuperVolume. I'm guessing the MDB is less than 16GB in size. Setup Citrix/XenApp on it and deploy MS Access 97 as a standalone Xen application. Users only need to log on to http://citrix.company.com http://citrix.company.com and click the "Access 97" icon. It will launch Access as a local app but it actually runs on the server. You get local disk speeds on the DB with minimal network usage. XenApp licenses are very cheap compared to even new Office licenses, let alone a full rewrite. If users do not need to change the Access 97 code/queries, you can deploy Access runtime instead of the full-access. c) I'm guessing they already tried to move the tables to MS SQL while just linking it from Access 97. If that isn't possible because the code uses tons of Access specific queries, mirror every table to MS SQL periodically, depending on the frequency of updates. Convert the slowest read-only reports to use the data from SQL while data-entry continues to use Access 97 tables. d) Break up the single Access DB into multiple DBs if possible. If Finance and Warehouse modules share almost nothing in common, they don't need to be on the same DB. If the 'employee' table is used to authenticate logins, put that table on MS SQL and link it from both Finance.mdb and Warehouse.mdb. e) Implement a decent ERP system if this application is really core to the company's operation. MS Dynamics NAV would probably be their best bet.

csomar 16y ago
Thanks for your insightful comment. Here are my comments on your points. a) They have currently started doing it. b) Companies don't need it. They aren't really bothered with Access 97 and Windows 2000 or XP. All they need is our application. Low OS/Application means low and cheap hardware. c) Tried the simple linking in MS Access, no improvement in speed. We need to re-write all the queries from scratch. Many problems showed and since the code base is huge, rewriting from scratch seemed more feasible and easier. d) The application is huge, it has dozen of DBs e) I don't know what you are talking about, but will do a little research.
- chime 16y ago
  Those were just five different ways you could have gone. (a) is what I would have suggested too. I'm going to guess the main reason for the speed issues is bad queries (m*n joins, no/few indexes etc). The best solution is often a rewrite in these cases. What I meant by (b) was to put the entire database in RAM and let users access it by logging on to the server directly. You can put the MDB files on a file-server network share (like P:\Database\Company.mdb) and 10 people can use it at the same time as long as they all have Access 97 on their local machines and have read/write access to the P:\Database folder. Instead of doing it this way, try this for super speed: setup a fast Windows server with tons of RAM, create/format a 2GB-16GB partition as drive D:, install SuperVolume to load that entire drive D: to RAM. Move the MDB files here. Don't make users open the MDB files from their PCs directly. Let them log on to a Windows Server as Terminal Server (TS) users and access it from the D: drive. If you have some money, install Citrix so logging on to the TS could be done by just visiting a website and logging in. This would let people from all over the world use the Access MDB at exactly the same speed as anyone physically logged into the server. With Citrix, you'll only need 20-32kb of bandwidth for most users.
  - csomar 16y ago
    Users working over the same database crash it. It seems that when users send requests in the same time, the DB crash (it can be fixed, but that's annoying). It doesn't always happen, but when you have more than 10 users, this increases the odds. So the short term solution was Terminal Server. It worked great and speed was fine, until in some days something happened in the Windows Server that slowed it down (some kind of caching or somewhat), so the illiterate technician just said that Windows Server is bad. We are now (Ok, I left) reckoning on an advanced/new Web or Desktop platform. They are aiming to finish a part in the software this year. Will see what they do ;)

by chime 16y ago

csomar 16y ago

Thanks for your insightful comment. Here are my comments on your points. a) They have currently started doing it. b) Companies don't need it. They aren't really bothered with Access 97 and Windows 2000 or XP. All they need is our application. Low OS/Application means low and cheap hardware. c) Tried the simple linking in MS Access, no improvement in speed. We need to re-write all the queries from scratch. Many problems showed and since the code base is huge, rewriting from scratch seemed more feasible and easier. d) The application is huge, it has dozen of DBs e) I don't know what you are talking about, but will do a little research.

chime 16y ago
Those were just five different ways you could have gone. (a) is what I would have suggested too. I'm going to guess the main reason for the speed issues is bad queries (m*n joins, no/few indexes etc). The best solution is often a rewrite in these cases. What I meant by (b) was to put the entire database in RAM and let users access it by logging on to the server directly. You can put the MDB files on a file-server network share (like P:\Database\Company.mdb) and 10 people can use it at the same time as long as they all have Access 97 on their local machines and have read/write access to the P:\Database folder. Instead of doing it this way, try this for super speed: setup a fast Windows server with tons of RAM, create/format a 2GB-16GB partition as drive D:, install SuperVolume to load that entire drive D: to RAM. Move the MDB files here. Don't make users open the MDB files from their PCs directly. Let them log on to a Windows Server as Terminal Server (TS) users and access it from the D: drive. If you have some money, install Citrix so logging on to the TS could be done by just visiting a website and logging in. This would let people from all over the world use the Access MDB at exactly the same speed as anyone physically logged into the server. With Citrix, you'll only need 20-32kb of bandwidth for most users.
- csomar 16y ago
  Users working over the same database crash it. It seems that when users send requests in the same time, the DB crash (it can be fixed, but that's annoying). It doesn't always happen, but when you have more than 10 users, this increases the odds. So the short term solution was Terminal Server. It worked great and speed was fine, until in some days something happened in the Windows Server that slowed it down (some kind of caching or somewhat), so the illiterate technician just said that Windows Server is bad. We are now (Ok, I left) reckoning on an advanced/new Web or Desktop platform. They are aiming to finish a part in the software this year. Will see what they do ;)