Best database to use with vb.net applications

[Ultrawhack]

Hi,

I'd like everyone's opinions on this please. What would be the best backend database to adopt with a vb.net application - assuming the app is going to be deployed on a per-user basis, 1 user 1 backend - no backend sharing via intranet, etc.

I'd also like to know your opinions on strengths/pitfalls of various db formats.

Thanks !

 

[kulrom]

I would simply say MSAccess at least it is me. File based which has many advantages, easy for deployment, small size, easy for backup etc.

 

[zekeman]

I would agree with MS-Access also. I have used it many years and

have had very few if any problems. One issue I once had was with

using one version of the Jet DB and opening it with another version

using MS-Access.

I would suggest that you obtain MS-Access to view files, define tables, etc. even if you can access all the tables with your programs.

 

[TechGnome]

Personaly, I prefer the power of SQL Server and would suggest SQL Express... BUT... it is slightly more complicated to install....

So my real recomendation would be to determine your needs (I'd be lost w/o my stored procedures as I can't stand having my SQL statements in my code) and then based on that make your decision. It's all about the right tool for the right job. If it's a small, quick and dirty type application where you don't need much power from the database, then Access is more likely the way to go (and no, I don't mean to imply that Access isn't powerful, it's jsut less so than SQL Server). I tend to do a lot of work with stuff that is very data intensive, so for me, Access isn't an answer, but SQL server is. If all you are looking for is "just a place to store some data" again, Access maybe the ideal choice.

So you really need to determine what your needs really are, let us know what you are looking for, and then a better suggestion can be made.

-tg

 

[cjard]

I cant see there being a need for Access now that SQL Server express/MSDE is available.. Access sucks big time for a lot of things (and you can even find articles written by MS telling you not to use it) not least for reasons of performance, scalability, data durability and concurrency. While acces smight be fine for holding your granny's record collection, you wouldnt consider writing a multi user transactional, journalling financial system using it. Assess closely your need, but bear in mind that if there is ever to be a commercial consideration, then learning SQLServer makes you more employable than access (And learning Oracle even more so).

 

[Administrator]

You may want to investigate VistaDB as well. The problem I have with Access is making structural changes after the app is deployed! ADO.NET has not capability to add tables, columns, etc. for MS Access.

 

[cjard]

Wow.. can it not be done with SQL statements?

 

[TechGnome]

I cant see there being a need for Access now that SQL Server express/MSDE is available.. Access sucks big time for a lot of things (and you can even find articles written by MS telling you not to use it) not least for reasons of performance, scalability, data durability and concurrency. While acces smight be fine for holding your granny's record collection, you wouldnt consider writing a multi user transactional, journalling financial system using it. Assess closely your need, but bear in mind that if there is ever to be a commercial consideration, then learning SQLServer makes you more employable than access (And learning Oracle even more so).

Biggest reason I can say for using Access is ease of deployment.... you don't need Access installed... just the mdb file and ADO.... with SQL Express or MSDE you have to install the database engine.... and then you have to deal with issues of it already being installed (shouldn't really install more than one if you can help it.) Plus, that means you then also have to distro it in your install, which leads to a bigger install file, or provide as a download (which means yet one more thing the user has to download and install - presumably on their own).

You may want to investigate VistaDB as well. The problem I have with Access is making structural changes after the app is deployed! ADO.NET has not capability to add tables, columns, etc. for MS Access.

Neal - that's actualy quite simple. You can .ExecuteNonQuery DDL (Data Definition Language) queries... IE: ALTER TABLE tblMyTable {ADD COLUMN MyTest text NULL}.... I dont' think Access has a Scripting function, but they aren't all that different from CREATE TABLE SQL commands.

-tg

 

[Administrator]

TG,

Try it, post an example if you don't mind! I don't think it's possible with the SQL system in MS Access, but prove me wrong!

Typically for MS Access you need to do COM INTEROP to DAO or COM INTEROP to the ADOX library.

 

[TechGnome]

Hey, no prob... I'll see if I can whip one up real quick.

-tg

 

[Ultrawhack]

If making structural changes to end users backend db is possible, then I may consider keeping Grandma's record collection.mdb a little longer. Look forward to your sample TechGnome.

 

[TechGnome]

Well dang... shoot.... Looks like I'm wrong on this one....
Now, for the record, I know it can be done with SQL Server using the .ExecuteNonQuery of the SQLClient class....
And I do know that Access can run DDL queries (ALTER TABLE and the like).... BUT I couldn't find a way to run such queries in the OLEDB class.... Imagine my surprise. That quite honestly changes my opin about ADO.NET drasticaly (since I deal with SQL Server almost exclusively, I didn't know about this limitation in the OLEDB object). It looks like the only way to do it is using ADOX which is a COM component. I thought there was a way to add cols to the DataTable, then push the change to the database.... but the results were... um... less than desireable - read: "my laptop went nutz and I had to reboot inbetween CPU usages of 100%."

Sorry, I thought I had something there, but I was wrong. Good thing humble pie goes good with a bit of crow..... and I learned something to boot.

-tg

 

[zekeman]

If you want to work with Legacy stuff the rest of your life, go with Access. I just realized its time for me to try to move forward.

 

[cjard]

TG,

Try it, post an example if you don't mind! I don't think it's possible with the SQL system in MS Access, but prove me wrong!

Typically for MS Access you need to do COM INTEROP to DAO or COM INTEROP to the ADOX library.

Neal, you might find the following article interesting:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;180841

googled for jet ddl

 

[vis781]

Good Info!!