[2005] Connecting to an mdb file.

[Bruce Fox]

Hi Guys,

I have a simple front end to an mdb. Can you please confirm that I need to make a (new) connection (and adapters etc) to the mdb 'each' time I want to read, add, or modify the db.

In other words, do I make a connection, read/manipulate the data (via a DataTable/Adater), then Close() - and do that each time I access the mdb.

I know thats how I would do it VB6, and I recall doing something along those lines with VB.Net2003, but, 2005 is doing my head in......



Regards,
Bruce.

[Shuja Ali]

Well 2005 ADO.NET is not much different than 2003. You still do the same way. Open Connection, do stuff and close it. That is pretty much how you would do in ADO.NET.

[Bruce Fox]

Thanks for the reply Shuja Ali,

My intension was to create form level variables for the 'con', 'da' 'dt' etc and set them in each method with the new keyword - and do that each time I manipulate the db.
My concern also was with freeing up memory used by the creation of those objects (each time) - I assume the garbage collector will take care of then at the termination of each method.


So what is best practice, Dim 'con' in each method (Sub), or Dim once at form level and set new each time?



Cheers,

[jmcilhinney]

Although you can, you don't need to create connections explicitly in .NET 2.0. Create a Data Source for your database, which creates a typed DataSet class and a typed TableAdapter class for each table. Now you just add an instance of your Dataset and and an instance of each TableAdapter you need to use to your form in the designer. The creation and destruction of those objects is then taken care of by the form. All you have to do is use them in code like you would any other control or component you add at design time.

Note also that the connection is incorporated into the TableAdapter, so you don't have to touch them explicitly unless you actually want to. The code will just be something like:

Me.someTableAdapter.Fill(Me.someDataSet.SomeTable)

Read about TableAdapters here.

[Bruce Fox]

Thanks for the reply jmcilhinney,

I have read alot over the last day (thanks to you) in relation to the subject.
There are many examples, of which usually show a single method such as reading data, or saving data, and usually nothing of a 'full' blown example where you do the lot.
There are examples (MSDN) but they are for 2003 (to which I was familiar).

That all said, I think I understand. I can multi use the TableAdapter (and not explicitly Open and Close() the connection each time I need access.

I am aware that I can use the wizard, but I prefer to do it by hand (at least until I have a decent grounding in whats going on behind the scene, and it helps me avoid binding).

I have also read there are many ways to impliment data access with 2005.


Thanks once again, I will persue the TableAdapter as mentioned.


Cheers,
Bruce.