Best DB-type

Hi!

I'm developing a multi-user program where multiple users must be able to connect to a database and list/change records. What type would be the best to use? ADO? DAO? RDO? anything else? There will be lots of data in it (about 1 gig per year) but each year (or maybe even each month) a new database will be generated so the 2 gig barrier will never be crossed. I use the ACCESS2000 DLL's and so on, so Data Access 3.6(1).

There are a few rules:
- The load on the database in not real heavy (so there won't be thousands of updates per second)
- Multi-user: I think if it can handle about 3-5 users it's good enough, so I'll design it with about 50 users in mind... ("640k ought to be enough...")
- Because of it's multi-user nature I must be able to lock 1 or more records at the same time.
- I don't want to use any objects on my form for access to the database,
- I don't want to have to code huge amounts of code just to refesh a record or so,
- I've ordered crystal reports 8, and want to be able to generate reports from the database,
- I'd like not to have to use a "Data Environment" in the IDE (but if I have to .... ok)...
- I don't want to have to run a SQL-server or anything, my app will be "stand-alone". The Database file (mdb or so) will be on a shared drive on (mostly) an NT4/2000 Server
Any suggestions? Ideas? Remarks? Tips? Anything will be appreciated.

[Edited by RobIII on 08-29-2000 at 06:37 AM]

[kovan]

scrap access
either go with
Oracle or SQL Server

[Nathan]

I agree... ado and sql server would be your best bet... by the end of a year your program will be running so slow people will have to start their queries or updates before they go home for the night and then it might be done when they come back.... :)

but seriously... sql server is very fast and very powerful... it makes really good use of indexes so even on 2 gb databases retrievals and updates are still very fast.

OK, thanks for the help guys... But how do I go on from here? Do I have to install SQL server for each client? Access can handle SQL queries (like "Select * from") and that's pretty fast...Will it realy be slow? I just can't believe it...But if you say so? Do I have to use some sort of Connection to the SQL server? Do I have to distribute some extra things (exept for the ODBC drivers or so)? Can I install an SQL server royaltee free on an NT server? And what if it has to run locally? Can anyone please help me a bit more?
I have experience programming access (using just code). So I can process SQL queries, I now how to create and sort and delete a database/records. I know how to make a selection and so on... But will it be the same if I use a SQL server?
I have no idea of how to go from here... Please help...
Thanx a lot....

[parksie]

mySQL is free and very fast. It has no trouble with large databases, and has a very easy-to-use API.

[Gary.Lowe]

Would it not be better to develop it MSDE this way if the database does become bigger than first expected it can be easily(Hopefully) migrated to SQL Server.

otherwise for speed of development I would just use access.
if as you say the databases are archived every few months or year, this would be an quick & easy solution.

[Clunietp]

I agree with Gary

MSDE would be perfect for this problem (perfect if your clients don't want to pay for SQL Server -- SQL Server is even more perfect :) )

Access really slows down around the 60000 record limit, and keeping an Access DB on the network with this many records will kill your network really quickly, assuming this app will be used consistently throughout the day

There is no client install necessary for SQL server, just distribute the MDAC.

Don't use DAO, use ADO for your development. ADO is somewhat slower when using MS Access databases, but is the data access model of the future. DAO is being phased out, and I think we should all try to avoid writing apps that are obsolete as soon as we start designing them :)