Fed up with Access97! What are my options????

[cavery]

Hello all,
I'm a frustrated Access application developer and I'm now in the process of upgrading to a new, more powerful system. I currently have about 80,000 records with queries, forms, reports, etc. What are my options for upgrading to a new system, which can hold more volume and be more stable. Such as any mainframe, Oracle/Unix, SQL driven database and last but not least, upgrading to Access 2000 isn't an option for me...

Thanks for your time,
~Clark


~Clark

 

[WildHare]

Hmmm.. First off, I'd say that 80K records is easily within Jet's capabilities - I routinely use db's with 750K to 2M records, and, apart from the somewhat slow delivery (which can be tweaked by throwing hardware), it's as stable as anything else in the Windows world..

What do you mean by "upgrading to a new, more powerful system."

Are you thinking of shoving an AS/400 under your desk?

The big thing about "server" database engines is that they tend to need more administrative detail - you don't say whether this is just "you" you're talking about, or if you are part of a larger enterprise that already has Oracle or SQL Server or something else like that available to you.

I can't in good faith recommend tossing Oracle Desktop on a PC for an 80K record database.

And why can't you upgrade to A2K?

Some additional details will help us/me/them fix you up.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[JeremyNYC]

I'm with Jim, except that I really dislike A2K, and would recommend AXP instead. 80,000 records is really quite small, if you've got things properly indexed, split into FE/BE, and you only pull across the data you need.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Hap007]

Dear Clark,

I would hve to completely agree with Wildhare.

I have had great success with Access 97, 100,000's of records and upwards of 50 users retrieving from on backend database source.

I and moving to Access 2000 or XP or MS SQL is much cheaper than AS400, unix or Oracle.

Couple of thoughts.

1) Is your current hardware up to speed?
2) Do you have enough memory, you need 256 k in Windows 98 and Win 2000 or XP (Pro), should be 512 or more!!!
3) Are you Designing, building and coding your database and programs correctly? One programmer/developer mistake could kill your entire application.
4) Have you thought of contacting another developer to search your design and code for suggested improvements? We all make mistakes.
5) If a network is involved, is it 100 k or better? Is the Nic card properly configured?


Good Luck,
Hap



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[grnzbra]

JeremyNYC,

How do you pull across only the data you want? I thought that was one of the drawbacks of Access; if you want the name and zipcode of all customers in New Jersey, you get the whole record for every coustomer in the table and the query is run on the client machine. This is not the way it works?

 

[HomeALone]

If I were in your situation that money is not the subject! I would go out and get the best toy. We all do that by looking around the SUV owners driving around the cities. It is absolutely OK to overkill to impress the world!

I would pay a premium price to get an Oracle server and hire a team of world-class developers charging $1K an hour to get the job done. Your 80K records can fly around the globe in measurement of nanosecond with hack proof security.

Best of all, you never need to get back here for any Access tips. Cheers!

 

[jsteph]

cavery,
I'll just throw in my nod of agreement to the consensus here--Access 97 is more than enough for 80k records.

As for stability, there *is* that issue. But that isn't really solved in Access 2000 (can't speak for xp). For true stability you'd have to go to a 'real' dbms, like Oracle, but you'd still need a front end.

Bottom line is to look at the application(s). If it's just the one app with 80k records, Access is a good bet.
--jsteph

 

[lameid]

grnzbra,

Don't confuse the native database Engine of Access (JET) with Access as a developement tool. Your limitations of 'Access' are true of JET. However, even with Access 97 it is possible to access data in other DBMS via ODBC. Beginning with A2k Access supports ADO which is an object model that utilizes and runs on top of ODBC. In other words, knowing ADO is easier to do than setting up all the ODBC level stuff in 97 (allegedly but only having cursory knowledge, seems relatively similar in difficulty but divergent in method). When you use a server DBMS, you send a query to it and it executes it and only sends you the data requested rather than the query being executed on the machine.

cavery,
I have to second the recommendations of these guys that 80k records isn't inherently a problem. Only if you have several users updating information at a time do I see this as a potential problem. As I stated above, A2k or better is probably the way to go for using a different RDBMS package. A2k+ also supports MSDE for free... Get most of the benefits of SQL server with none of the costs. Least expensive approach to MSDE plus Acc2k+ to multiuser rollout is most likely one of the Developers Editions of Office that includes the Acces runtime... Just install the royalty free Access Runtime to all the users. No matter what you do, I'm betting you will have to learn alot as the RDBMS way of doing things efficiently are different than using Access with JET. If you use your JET coded Access with an RDMS without optimizations (work), it will likely run in much the same fashion it does now and you will not see much benefit to performance and maybe even degradation in performance.

I guess to give you the best answer we need to know what kind of political problems you face and what kind of budgetary constraints. More info on how your App works may help us recommend a $ solution or maybe even a labor solution (Building an index, etc.)