Connecting from VB.NET to MS Access

[mza1979m]

I have a project that was originally created in VB 6.0. In it, I had a data control and many text boxes that were bound to fields in an external database created in Microsoft Access. Now I've recently converted this project to VB.NET format, and data controls are no longer supported. Alternately, the first step is to create a data source, to which the text boxes will be bound (an .mdb file -- organizer.mdb). My question is, how do I go about creating a data source? The closest I've gotten is by clicking " Tools > Connect to Database... " but I don't know where to go from there or even if that's the correct step.

 

I would really appreciate the help, as I must present this project between the 19th and 24th.

[stustarz]

vs.net no longer uses recordsets to retrieve data from a datasource (in your case the access database). Instead it uses ADO.net's Dataset, Datatables and Datareaders.

 

If the users need to read, update and delete data from the database you need to use a dataset. If they simply need to read the data then you should use a datareader.

 

Instead of telling you all about them visit:

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dndotnet/html/Usingadonet.asp?frame=true

 

This will explain all about the new data functions in vs.net - it is based on connecting to an SQL Server database but it is easily amendable to connect and use an Access Database.

 

Hope this helps

[mza1979m]

Thanks, stustarz.

 

I was able to create a datasource to a specific .mdb file created in MS Access XP (Not programatically during Run Time, but rather during design time). So now I see that database under "Data Connections" in the left pane. I should now be able to bind a TextBox control to a specific field in the database (database file = organizer.mdb ... field name = fldLastName). However, when I expand the "DataBindings" property, then attempt to change the sub property "Text", the only option I have is "(None)", whereas it should display all the fields in my database, according to Microsoft's VB.NET help files.

[stustarz]

I've had the same problem - so I avoided the whole Data Connection idea that your using and set up the data connections using the Data Adapter & Data Set procedure as explained in the link I posted - although it may be long winded it always works for me!

[a_jam_sandwich]

Ill try and write another tutorial for Datasets and databinding 2nite should shed some light then

 

Andy

[mza1979m]

Ok, I'm attempting your idea stustarz, using the code located in the link you provided. However, I've arrived at a little stumbleblock... According to the following instructions...

 

---------------------------

The connection string shown below is an example of how you would connect to a Microsoft Access 2000 database using the OleDbConnection object in System.Data.OleDb.

 

Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Northwind.mdb

---------------------------

 

However, since I'm new to data connections in VB.NET, I have no clue how to use this line in the code. Clearly, this exact line is not VB code, but how do I implement it in my program?

Thank you.

[stustarz]

Use the following as a variable:

 

   Public DataCon As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb")

 

When you want to open the connection use:

 

DataCon.Open()

 

Hope this helps - anything else just ask

 

Stuart

[mza1979m]

I've got the following piece of test code in my program...

 

--------------------------------

Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click

Dim oConn As SqlClient.SqlConnection

' Dim strConn As String

 

Try

' Create the Connection object

oConn = New SqlClient.SqlConnection()

Dim strConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\organizer.mdb")

 

' Set the Connection String

oConn.ConnectionString = strConn

 

' Open the Connection

oConn.Open()

 

MessageBox.Show("Connection Open", "btnConnect_Click()")

 

' Close the Connection

oConn.Close()

 

Catch oExcept As Exception

MessageBox.Show(oExcept.Message, "btnConnect_Click()")

End Try

End Sub

--------------------------------

 

...yet I get the following error message:

 

Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'String'.

Any ideas?

 

I really appreciate you helping me out.