I'm writing a stand alone app for a cd product. I need to get some ideas for everyone. This app will be a standard exe file with a db connection. I can not use MS Access for 2 reasons. First, the data is to big, second is this is and Enterprise packet. I have been looking at FoxPro but it does not seem to be as friendly as VB. What other DB's can I use??? What doesn't kill you makes you stronger.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Best Database for VB 3
- Thread starter EZEason
- Start date
- Thread starter
- #4
-
2
- #7
If the data will not be used by multiple users at the same time, and especially if the data is local, then an MDB will work. Even though a single MDB can physically hold up to 2GB of data. you can link multiple MDBs together, in which case there is no size limitation, pending disk space.
If you write the application in VB6, then you do not need Access. An MDB is the native Db for VB6 and is a MS Jet db, which is used by Access, and VB6. Therefore, you do not need any Access runtime files to access the db.
Another way would be to use MSDE. But this has a 2Gb limitation.
If you write the application in VB6, then you do not need Access. An MDB is the native Db for VB6 and is a MS Jet db, which is used by Access, and VB6. Therefore, you do not need any Access runtime files to access the db.
Another way would be to use MSDE. But this has a 2Gb limitation.
-
1
- #8
CajunCenturion
Programmer
Besides Oracle, SQL Server, and Access as discussed above (all reasonable options), you could also consider MySQL. MySQL has the nice cost benefit in that its free. You can get more info on MySQL at Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
mattKnight
Programmer
Hi,
As CajunCenturion says MySQL is free... so is MSDE both development and runtime. the only proviso is that you have to "add siginificant value" to he db engine
as for Jet/Access, CCLINT is correct that it will handle about 2GB of data but it really will need loads of resource to do it. more importantly Jet 3.5 corrupts its indexing (keys etc) quite regularly with large MDBs. Jet 4.0 is better, but I wouldn't be sure that it cures the problem. If you can rejig yout architecture, I would suggest you use something like SQL ORACLE MySQL or MSDE
Matt
As CajunCenturion says MySQL is free... so is MSDE both development and runtime. the only proviso is that you have to "add siginificant value" to he db engine
as for Jet/Access, CCLINT is correct that it will handle about 2GB of data but it really will need loads of resource to do it. more importantly Jet 3.5 corrupts its indexing (keys etc) quite regularly with large MDBs. Jet 4.0 is better, but I wouldn't be sure that it cures the problem. If you can rejig yout architecture, I would suggest you use something like SQL ORACLE MySQL or MSDE
Matt
But MSDE is limited to 2Gb. Oracle and SQL Server cannot run on a stand-alone machine as the questioner wants.
As far as corruption, I have this very, very seldom, and when, then usually when it is on a server, because of the server, or, when a user shuts down improperly. If it is local, or even on a server as single use, then opening it exclusive will help prevent this and will make it faster. It's a file Db. It can happen. But, if this is a stand alone, then this can be checked and if needed, be compacted to usually correct (note: use JRO even if DAO is used - or make sure you compact then repair then compact again). Of course, this can take some time with a large database.....
And backups are just as easy.
I would anyways keep the MDB under 500mb by seperating the larger tables from the rest, linking them to one main MDB, and normalizing the database as much as possible.
For an MDB, you can use DAO 3.51 with Jet 3.0 Sp3, or DAO 3.6 with Jet 4.0 (more flexible, '2000 compatable, and a little more stable - but also alittle slower), or ADO with Jet 4.0 (again, will be slower on an MDB with alot of records, but this is because of Jet 4.0 and not ADO).
I did some investigating in the past on MySql and almost decided to give it a try, but then read in several places that while it may be efficient for reading large amounts of data, it has it's problems with writing large amounts of data(not as effecient). I do not know in practice how true this may be.
Again, the solution needed here, is for a stan-alone-system....there are few options and none of them will be as ideal as using a server database with large amounts of data - with smaller amounts of data it is different.
As far as corruption, I have this very, very seldom, and when, then usually when it is on a server, because of the server, or, when a user shuts down improperly. If it is local, or even on a server as single use, then opening it exclusive will help prevent this and will make it faster. It's a file Db. It can happen. But, if this is a stand alone, then this can be checked and if needed, be compacted to usually correct (note: use JRO even if DAO is used - or make sure you compact then repair then compact again). Of course, this can take some time with a large database.....
And backups are just as easy.
I would anyways keep the MDB under 500mb by seperating the larger tables from the rest, linking them to one main MDB, and normalizing the database as much as possible.
For an MDB, you can use DAO 3.51 with Jet 3.0 Sp3, or DAO 3.6 with Jet 4.0 (more flexible, '2000 compatable, and a little more stable - but also alittle slower), or ADO with Jet 4.0 (again, will be slower on an MDB with alot of records, but this is because of Jet 4.0 and not ADO).
I did some investigating in the past on MySql and almost decided to give it a try, but then read in several places that while it may be efficient for reading large amounts of data, it has it's problems with writing large amounts of data(not as effecient). I do not know in practice how true this may be.
Again, the solution needed here, is for a stan-alone-system....there are few options and none of them will be as ideal as using a server database with large amounts of data - with smaller amounts of data it is different.
mattKnight
Programmer
CCLINT,
Whoa who yanked your chain this morning? Not me I hope!
Ihave had many support instances where the MDB has become corrupted. This cannot be traced to any single cause and the server has remained up. (small tables big table, multiplie linked mdbs etc)
When you say "shuts down improperly" are you saying machine or DAO/ADO connection?
As often, I re-read the post and the answer is there! You point out correctly the solution is required to bestandalone therefore Jet is the realistic answer fro a relational db.
I'll shut up now!
Matt
Whoa who yanked your chain this morning? Not me I hope!
Ihave had many support instances where the MDB has become corrupted. This cannot be traced to any single cause and the server has remained up. (small tables big table, multiplie linked mdbs etc)
When you say "shuts down improperly" are you saying machine or DAO/ADO connection?
As often, I re-read the post and the answer is there! You point out correctly the solution is required to bestandalone therefore Jet is the realistic answer fro a relational db.
I'll shut up now!
Matt
Matt, no chains yanked.
Your arguments are justified, as you have already experienced.
I just want to explained it the way I see it. And I like to defend Jet to a certin point of usage - it is a very good tool when used the right way and for the right environment and under the right conditions.
Sure, some of my comments were based on your comments, but I have encountered just the same problems(corrupted MDB) in the past as well - and many can be prevented. And just feel I have been through them all (there just can't be any more p l e a s e).
I am not focused on just one database type, and do not always shun away and tell clients that it has to SQL Server and nothing else (even though it would be nice). It depends on many factors, and the customer has to identify the goals, and budget for the project. I can give the options based on that along with the up/down-sides - and what can get achieved by going a different route. If it means using an MDB in a multi-user environment, then alright - let's try to pull out of it what can be gotten (even though I do set a fair limit of a 5-user-access at the same time, and up to 500 mb of data - which means as one factor increases, the other one has to be reduced). That means alot of testing in different enviroments, under all sorts of options, settings and situations. The problem may not be the tool, but the way it's being used. You can create large problems, by just writing an SQL SELECT statement the wrong way - and this is very often done.
Mostly the problem was with:
1. The application not closing down properly (not only internal to the app. but also via Task Manager);
2. The system not shutting down properly (system crash or user fault);
3. The server going down;
4. Certain read/write cache that a server uses on files, or the locks imposed.
5. Certain bugs in certain versions of DAO, Jet, or MDAC(such as corruption caused by cursors), that have beed mostly corrected.
These situations may cause Jet from closing the db correctly - causing a corruption.
You are right about MSDE - not a bad choice at all if the amount of data doesn't go below 2Gb - especially when there is the possibily of upgrading. However, there may be more work envolved, esp. when distributing to many clients with different systems, in order to get things up and running correctly.
For the questioner: you can see a comparison between Jet and MSDE at these links:
I just feel that the more info a person has, the better they can make a decision. That is why we discuss these things.
Your arguments are justified, as you have already experienced.
I just want to explained it the way I see it. And I like to defend Jet to a certin point of usage - it is a very good tool when used the right way and for the right environment and under the right conditions.
Sure, some of my comments were based on your comments, but I have encountered just the same problems(corrupted MDB) in the past as well - and many can be prevented. And just feel I have been through them all (there just can't be any more p l e a s e).
I am not focused on just one database type, and do not always shun away and tell clients that it has to SQL Server and nothing else (even though it would be nice). It depends on many factors, and the customer has to identify the goals, and budget for the project. I can give the options based on that along with the up/down-sides - and what can get achieved by going a different route. If it means using an MDB in a multi-user environment, then alright - let's try to pull out of it what can be gotten (even though I do set a fair limit of a 5-user-access at the same time, and up to 500 mb of data - which means as one factor increases, the other one has to be reduced). That means alot of testing in different enviroments, under all sorts of options, settings and situations. The problem may not be the tool, but the way it's being used. You can create large problems, by just writing an SQL SELECT statement the wrong way - and this is very often done.
Mostly the problem was with:
1. The application not closing down properly (not only internal to the app. but also via Task Manager);
2. The system not shutting down properly (system crash or user fault);
3. The server going down;
4. Certain read/write cache that a server uses on files, or the locks imposed.
5. Certain bugs in certain versions of DAO, Jet, or MDAC(such as corruption caused by cursors), that have beed mostly corrected.
These situations may cause Jet from closing the db correctly - causing a corruption.
You are right about MSDE - not a bad choice at all if the amount of data doesn't go below 2Gb - especially when there is the possibily of upgrading. However, there may be more work envolved, esp. when distributing to many clients with different systems, in order to get things up and running correctly.
For the questioner: you can see a comparison between Jet and MSDE at these links:
I just feel that the more info a person has, the better they can make a decision. That is why we discuss these things.
I agree with CCLINT the more info the better. And also it can't always be up to personal preference, you have to use what you can. And if that means access, jet, mdse, sql, oracle, or whatever it doesn't matter. It just amounts to what is best for the specific project that you're working on. ----------------
Joe
Joe
Guest_imported
New member
- Jan 1, 1970
- 0
Just because no one else has mentioned it, there is always Btrieve. I have found their microkernel it to be insanely reliable and it is blinding fast. Of course it is a new API to learn and isn't for lamers who always want to use objects and 3 layers too-much of abstraction...
Guest_imported
New member
- Jan 1, 1970
- 0
Just because no one else has mentioned it, there is always Btrieve. I have found their microkernel it to be insanely reliable and it is blinding fast. Of course it is a new API to learn and isn't for lamers who always want to use objects and 3 layers too-much of abstraction...
- Status
