Ado Doa?

[dimava]

I'm going to be starting a project in Visual Basic that will deal with 1000's of records in connection with a databse. I'm thinking of connecting VB to an AccessXP database, but i'm not sure if thats the best method. SQL Server isn't an option because that would cost the client an extra $500 per licence.

What would be the best way to store 1000's of records in a database, because I don't think that an AccessXP DB will be able to handle the amount of records smoothly? Whats the main diffrence between ADO and DOA connection types? Which one would be faster and handle large amounts of data better?


thanks,

Dimava

[randem]

dimava,

I suggest ADO, it's more versitile. Expecially when you want to connect to different databases through different access methods ie ODBC, OLEDB, Jet.

Access can handle 1000 records easily. The problem with Access has mainly been the person who uses it. Don't get me wrong, it's at the bottom of the scale when it comes to SQL databases, But it is useful.

Access needs a little more care than other DB and is a little more finicky... but it gets the job done. I use it mainly for development then turn on a switch and I connect to SQL Server without any code changes. Of course you have to set this up ahead of time.

[phinds]

DOA is now sometimes referred to as "Dead On Arrival" because while it is very solid technology, it is not being taken forward by Microsoft. ADO is the wave of the furture, for sure.

By the way, 1000's of records is not a problem for Access, but if you are going to have more than 10 or so concurrent users, you really should get a real db, not a toy. I love Access for small projects but it is not for production databases of any size or useage requirements.

[dimava]

thanks for the replies

now both of you said that 1000 records isn't a problem for access, what if we went into the 100 thousands of records?

[randem]

dimava,

Well, first you have to realize that Access has a 2GB file size limit. That alone should keep you away from it for data requirements of that size.

[dimava]

Are there any alternatives that I can use instead of an Access Database? Or is Access the best thing to use with visual basic?

like I said before, sql server isn't an option

[randem]

dimava,

You can use and of the SQL based databases (and a few that are not). The choice is totally up to you, your requirements and cost.

Every good database will cost you! (SQL Server, Oracle etc...). I saw some post in the forum about TSQL (free) with Visual Studio I believe. They say it's a chopped down version of SQL Server. I have never attempted to use it though.

[dimava]

ok I see, but nothing stand-alone like access?

[randem]

dimava,

I have used Access for large jobs (only cause the client wanted a free database). It performs well, with a little care and maintenance. Of course designing the database correctly help a whole bunch.

[dimava]

Can you give me some tips on designing it correctly? and what do you mean by maintenance?


thanks,

Dimava

[randem]

dimava,

Maintenance:

Repairing and Compacting the database. Does not happen automatically and you can not set it up to do so.

Backup Database.


Design:

Normalizing your data - If you have repeating data consider putting it all in one table and have an indicator to where it came from (belongs to).

Use numeric valued indexes whenever possible. Most people make the huge mistake of indexing text fields that they really do not have to. let say you have several different items in your database; Table, Chairs and Beds. Some would have these names repeated thoughout their database instead of creating a different table with those names in it and giving eac one a value
ie 1,2,3 and placing that numeric value in the table. This will give a better index for these items and if you cross reference these two tables you can always find out what 1,2,3 means.

Relationships - How one table relates to another. Helpful when used to delete records from your database so that you do not have hanging records from a previous delete. Cascade deletes and updates.

Transactions - All or nothing database changes.

Deleting records - Whenever possible have a deleted field in your tables along with a date field. When you want to delete a record just update these two fields and you are done. When you retrieve data use this field to eliminate records from retrieval.

Deletes can take up time and resources and are unreversible. If you only mark a record deleted, you can always get it back if that was not what you wanted to do.

When you do you other maintenance you can physically delete these records then repair and compact.


Just a few......