0

Currently the VB6 application is using a local .mdb that stores the queries and has linked tables to 2 other networked .mdb files.

I'm not sure how the Jet engine can perform with this kind of setup. I've noticed a considerable delay when the number of users is increased.

Without going down the lane of don't use MSAccess:

  • What would be my best shot at solving the performance drop of the application?

So basically, is this setup - VB6 app using local mdb that points to other mdbs on a network share - a recipe for trouble?

Vivek Jain
  • 3,811
  • 6
  • 30
  • 47
CodingEdge
  • 143
  • 1
  • 10
  • Is this setup a recipe for trouble? Yes. Can anyone here say how to improve your performance? No, because there isn't enough information to work from. It could be I/O constraints on the server, network capacity/design, your front-end app's design, your database schema, or Access's inability to scale. – alroc Jan 25 '14 at 17:46
  • Jet is fine when you stay within the bounds of its ideal use cases. However file-based DBMSs do not perform well when shared via network file share techniques. About the only thing you could do to improve performance (and data integrity) significantly would be to add a middle tier running on the box where the data lives (custom software, RDS over DCOM, etc.). At that point you may as well just move to a client-server DBMS of some kind. – Bob77 Jan 25 '14 at 19:17
  • Does application run fast with one user but 2+ it is slow? (JET opens the file "different" depending if single user vs multi-user mode). If this runs slow with one, then if basic issues like indexing etc. HAVE been addressed then not much you can do. Despite popular myths JET with indexing can pull ONLY the one record out of a million row table and does NOT pull down the whole table over the network. While JET is not much scalable it can perform over a standard LAN network very well but for a WAN/internet connection it is too slow. – Albert D. Kallal Jan 26 '14 at 02:49
  • Yes, it runs fast with one user. With more users sometimes the delay of opening a detail screen (only a few records involved) can take 30 seconds. I'm thinking in the direction of some locking issues but it seems too random to be record locking. I found an earlier post suggesting keeping a recordset connected at startup of the application so that the negotation for the connection is avoided. I added it but data retrieval in the app is going through my first mdb (the "schema" database) which is on the client. It is MSAccess that handles the connection to the back-end databases, not? – CodingEdge Jan 26 '14 at 06:50
  • That is SIGNIFICANT information. This not so much record locking but switching that occurs due to op-locks. A persistent connection fixs this 9 out of 10 times and restores multi-user performance to that of single user (assuming multiple clients are hitting the mdb file). Such small data pulls should work well with even 20 users connected. All code runs in-process here and using JET is no different than calling a .dll. In EVERY SINGLE CASE your connection resolves to plain Jane windows file on the disk drive that you open. So yes, this connection must be keep open at all times. – Albert D. Kallal Jan 26 '14 at 19:32
  • Well, I thought the connection (.ldb file) persisted, but as it turns out the `cnn.CursorLocation`needs to be `adUseServer`. If I specify `adUseClient` the .ldb file is constantly recreated. – CodingEdge Jan 27 '14 at 11:21

0 Answers0