Posting data to MS Access using VB.NET 2

[DeGeneral]

Does anyone know of a simple VB project that I could download which posts data to MSaccess?

I am trying to upgrade some programmes from VB6 to .NET. I had previously used DAO data objects to post my data to MS Access. These are not supported in .NET, so I have to move to ADO. However, I have never used ADO before. I have tried lots of different ADO.NET code from my Visual Studio code snippets and from the web, but .NET keeps giving me various errors that I don't understand. I just want to get a very simple data posting routine that works and that I can tailor to my needs.

Thanks

DeGeneral

 

[ZmrAbdulla]

http://support.microsoft.com/kb/821765/EN-US

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[earthandfire]

I don't know where you got the idea that DAO is not supported under .NET it works perfectly.

You just need to add it as a reference (from the COM tab) in exactly the same way that you would when using VBA in Access or VB6.

A very simple example:

Public Class Form1

	' ***  NB NO ERROR CHECKING

	Private dbe As New DAO.DBEngine
	Private db As DAO.Database

	Private DBName As String
	Private TableName As String

	Private Sub AddRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddRecordButton.Click

		Dim rs As DAO.Recordset

		MessageBox.Show(DBName)
		MessageBox.Show(TableName)

		If DBName = "C:\db8.mdb" AndAlso TableName = "Table1" Then
			If TextBox1.Text <> "" AndAlso TextBox2.Text <> "" Then
				rs = db.TableDefs(TableName).OpenRecordset
				With rs
					.AddNew()
					.Fields("Field1").Value = TextBox1.Text
					.Fields("Field2").Value = TextBox2.Text
					.Update()
					.Close()
				End With
				rs = Nothing
			Else
				MessageBox.Show("Enter some data")
			End If
		Else
			MessageBox.Show("Unknown database or table")
		End If

	End Sub

	Private Sub btnOpenDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenDB.Click

		OpenFileDialog1.ShowDialog()
		DBName = OpenFileDialog1.FileName
		db = dbe.Workspaces(0).OpenDatabase(DBName)
		TablesListBox.Items.Clear()
		For Each tbl As DAO.TableDef In db.TableDefs
			TablesListBox.Items.Add(tbl.Name)
		Next

	End Sub

	Private Sub TablesListBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TablesListBox.SelectedIndexChanged

		If TablesListBox.SelectedIndex <> -1 Then
			TableName = TablesListBox.SelectedItem.ToString
			FieldsListBox.Items.Clear()
			For Each fld As DAO.Field In db.TableDefs(TableName).Fields
				FieldsListBox.Items.Add(fld.Name)
			Next
		End If

	End Sub

	Private Sub CloseDBButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseDBButton.Click

		db.Close()
		db = Nothing
		dbe = Nothing

	End Sub
End Class

Hope this helps.

 

[DeGeneral]

Thanks earthandfire,

I am using Visual Basic 2005 which gives me a non-conversion error message that "the Data control cannot be upgraded because it depends on DAO, which is not supported in Visual Basic 2005." I have tried to set variables to this object type but could not find it available. I have also looked in "choose toolbox items" to see if DAO was something I could select. Perhaps DAO worked in earlier versions of .NET but not in later versions. If not, please let me know as DAO would be my preferred data object.

DeGeneral

 

[earthandfire]

My sample was produced (and tested) under VB 2005.

My solution was called WindowsApplication4, so

In the Solution Explorer window, right click on WindowsApplication4 and then select Add Reference. From the dialog box that opens switch to the COM tab and scroll through the list and select the DAO library (on my machine I have DAO 3.5 and DAO 3.6 - I used 3.6) and click OK.

Now if you run my sample (you will need three buttons, two textboxes, two listboxes and an openfiledialog and a simple .mdb file (the structure should be self-evident)) it should work.

Your VB 6 data control will probably not work because (apart from anything else) DAO is not seen as a data source - just as an external object.

If you are working with unbound controls you should not have any problems, however working with bound controls I don't know whether you can directly assign a DAO Recordset object as the datasource for a control - but you could populate a dataset object (using a loop?) from the recordset; unfortunately I haven't had chance to experiment with any of that. All projects that I have converted have used unbound controls.

Hope this helps