What is an alternative to Access 2

[TomSnider]

I need some feedback from some of you experienced VB people. I am fairly new to VB, but over 25 years of programming. I have developed very large accounting apps using Revelation (from the Pick system) and some small (mainly single user) apps using both Delphi and VB. The Paradox DB seems better for small multi-user apps than Access; however, I wanted to go more mainstream and use a M$ product.....so my question is this....is Access a reasonable product for 12-14 users on a peer-to-peer network? Application contains 10+ tables and minimal actual new records added each day. I have already written the application and tested it under a 3 station network, but I before I actually install it, I wanted to get some feedback from experienced VB programmers. Sorry for the long message, but I'm verbose.

Any help/direction greatly appreciated,

 

[chiph]

Hi Tom!

Access usually tops out around the 10 user level, depending on usage patterns. It still gets corrupted too often for my tastes, though.

I was going to suggest MSDE (Microsoft Data Engine -- the desktop version of SQL Server), but it only allows 5 users at a time (or so the documentation says). I suspect (but haven't tried) that more can connect, but with degraded response time. It's also limited to 2gb worth of data, but that doesn't sound like it might be a problem.

You could write your app to disconnect from the database as soon as your queries are complete, but you'd have to handle the case where it was unable to get a connection due to them all being in use. (Delay a random time and re-try?) On second thought, this sounds like a stateless web-app -- can your program fit into that model?

The good news about MSDE is that it is file-compatible with SQL Server. If your users need more power, just install SQL Server and copy the .mdf and .ldf files over to it. The other good news is that MSDE is ANSI SQL compliant (and Access isn't), so any SQL that you write will be easier to transfer to another database vendor sometime in the future.

Chip H.

 

[TomSnider]

Chip:
Thanks for the response. I am very impressed by this forum, both in knowledge available and in way in which the help is provided.

Yes, the MSDE will not work for me on this app, due to the number of concurrent users. However, for my future reference, is MSDE an expensive solution as is SQL Server? The application I have written is fairly low-priced (under $4000) and I think that a per seat charge (plus the required maintenance fee) would overwhelm users of this app. Is SQL Server the only solution (with VB) for over 5 concurrent users?

Thanks again for the response.

 

[JoeNg]

I think Paradox or Delphi and Paradox combo should work fine with 12-14 users on peer-to-peer.

I personally prefer a “real” network, like Windows NT or Netware.

The only other SQL Sever level database I would consider is Oracle, but that is not cheap either.

Now, if you are brave, you can take a look at MySQL (

http://www.MySql.com)

. It’s free.

 

[TomSnider]

Thanks, I will look at MySQL and see how solid it looks, however, I will probably just use Access since it appears to work ok.

 

[tedsmith]

I have used Access Version 2 with 15 users with no crashes. The number of users doesn't seem to matter unless they all run complicated queries testing every table entry for reports (like an end of year balance)at the same time which temporarily slows it down on other workstations. I have only the data tables on the "server" and have all forms, queries & code on each workstation.
A SCSI drive on the "server" seems to work much better than an IDE for some reason even though their speed specs are the same.
Ted

 

[TomSnider]

Thanks Ted,
Good information. I sort of expected that Access would not be a problem, but I wanted to see if I was going down a bad road before I actually installed the application.

 

[MichaelRed]

Tom,

I have used MS Access by itself for up to ~ 75 concurrent users with a total of >> 200 possible users. This system "crashed" only twice in over a year, both traceable to code problems (e.g. MY fault). In several years of using MS Access and VB, I have become ever MORE conservative in the use of validation and error coding - and seen a direct proportion of reduced "crashes". IMHO, the VAST MAJORITY of crashes are the direct result of poor programming practices, not inherent faults in the tools used by the programmers. Many of the "quick" implementations of features for "demo" purposes never get revisited and upgrded to "production quality" and are never tested in a formal program. I have seen numerous crashes where the problem is traced to functionality which is not even referenced in the sytem specification - much less tested and documented.

So, despite the soap box, my answer is that your app will be as good as you are careful. As Robust as your test program. As solid as the design specification for the development. In this latter, If you are using the "performance" of an existing app as the design spec for the replacement - you can expect the replacement to suffer from many of the same problems and some additions created by the 'translation'.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[TomSnider]

Thanks Michael. You are correct...it is much easier to blame another person's code rather than check for bugaboos in your own code. I'm comforted to hear that Access is stronger than I've read on various forums. What kind of application was running on the 75 concurrent stations? Was is transaction intensive? I only ask because I am starting to rewrite my job cost accounting system in VB (from a dos language) and it is very transaction intensive...so I'm curious.

 

[MichaelRed]

Call center tracking app. It 'received' ~ 1K calls per month. There were typically > 5 transactions per call:

(1)[tab]Call Receipt

(2)[tab]Inital review by assingee

(3)[tab]Posting of Response / fix

(4)[tab]Supervisor review and approval

(5)[tab]Return to Orininator/approval at field level.

Additional transactions required if the response was not "perfect". Any additional 'work' on the issue involved at least two transactions.

In addition to the actual logging, every change to the calls data was recorded (e.g. history) with the field name, previous value, new value, user, date/time stamp. Also had ~14 daily management reports for sections/divisions with roll-up to top (three levels). Depts had 'ad-hoc' reporting as 'on-demand' with "current" information.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[TomSnider]

Michael, Thanks again for the info.