Question about good pratices (DB connection)

[RH+]

Im starting a new project (first in .NET) and Im thinking that this is the time to change the things that im not doing very well.
The question is, I have a project with 7-10 forms, all have access (and use) the same SQL or Access database.
How would be the best way to handle connections?
one option is to open/close connections in every form, other is to make connection public in one form...
what other ways are there? and...what would be the best approach?
Thanks!

[Shuja Ali]

The better way would be to separate the UI from data access. Write separate Data Access classes that will only be responsible for data access and nothing else. I am not sure how big your application is right now, but if you are foreseeing it to grow in the near future then this is the way to go.

Check out MSDN, they have quite a few samples on how to create Data Access layers using ADO.NET.

[RH+]

Sorry, I forgot to say Im doing this on VB.NET

[dglienna]

We know that, and the answer remains the same. Look up Two-Tier or Multi-Tier and you'll find that you can use any language

[RH+]

wow dglienna you are everywhere, thanks!

[RH+]

Sorry if it seems that I like all done by other. Im googling but i get things like two-tier=client-server, where the server is the SQL database and the other answer i get is I code an aplication for the client, one for the server and the application in server side make the requests to the DB.
Im looking for a simple example in psudocode or a draw at least, to try to understand the difference between what I do now and what should I do.
I keep googling
thanks again!

[Shuja Ali]

Ideally if you are using .NET 3.5, I would go with either entity framework or LINQ.

However, here are some of the examples that show you how to write a simple DAL with ADO.NET
http://www.aspdotnet6976.blogspot.com/2006/09/3-tier-aspnet-architecture-on-msdn.html
http://msdn.microsoft.com/en-us/library/aa581778.aspx
http://msdn.microsoft.com/en-us/magazine/cc188750.aspx