Hello Mrugesh,
Thank you so much for posting your questions about Access here! I saw your post and wanted to provide some help. Specifically, you asked:
Quote:
|
can anybody help me to write code in VB.Net to connect with MS-Access, simple add, edit & delete code???
|
To answer your questions, there are a number of different methods to "connect with MS-Access", but I'll assume for this conversation that you want to connect to an MS Access database and execute SQL statements against it. This is very easy to do in
VB.NET and in the following code is a simple example of how you can create a
VB.NET class to execute SQL statements against an Access database:
Code:
Public Class DatabaseConnector
''' <summary>
''' Returns an OLEDB connection string to an Access 2010 database
''' </summary>
''' <returns>The OLEDB connection string to the Access 2010 database</returns>
Private Function GetConnectionString() As String
' Create the Connection string
Dim strConnection As String
strConnection = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Test\DatabaseFile.accdb;" & _
"User ID=Admin;Password=;"
' Return the Conntection string
GetConnectionString = strConnection
End Function ' End of: Private Function GetConnectionString() As String
''' <summary>
''' Allow the user to execute non-record returning queries
''' </summary>
''' <param name="SqlCode">The SQL Statement to execute against the database</param>
''' <returns>True if successful, otherwise False</returns>
Public Function RunSqlNonQuery(SqlCode As String) As Boolean
On Error GoTo HandleErrors
Dim bResult As Boolean
Dim cmd As OleDb.OleDbCommand
' Create the OLEDB Command object
cmd = New OleDb.OleDbCommand(SqlCode)
' Open the Connection
cmd.Connection = New OleDb.OleDbConnection(GetConnectionString())
cmd.Connection.Open()
' Execute the SQL Statement
cmd.ExecuteNonQuery()
' It looks like we've succeeded - return True
bResult = True
ExitFunction:
' Close the connection
If (Not IsNothing(cmd.Connection)) Then
If (cmd.Connection.State <> ConnectionState.Closed) Then
cmd.Connection.Close()
End If
End If
' Return the result and exit
RunSqlNonQuery = bResult
Exit Function
HandleErrors:
' Handle any errors here...
MsgBox("An error was raised!" & vbNewLine & "Message: " & Err.Description, MsgBoxStyle.Critical, "Error")
Err.Clear()
bResult = False ' Return failure
Resume ExitFunction
End Function ' End of: Public Function RunSqlNonQuery(SqlCode As String) As Boolean
End Class ' End of: Public Class DatabaseConnector
So, in the above code, notice the "GetConnectionString()" function just returns a connection string to an Access 2010 database. Notice that this connection string provides the
full file path to the database file itself, specified through the "Data Source" parameter. Then notice the "RunSqlNonQuery()" function. This function will allow you to execute non-record returning SQL queries against the database. So then to use this DatabaseConnector class to connect to an Access database and execute SQL statements against it, you could create the following
VB.NET code in a form:
Code:
Dim dbConnector As New DatabaseConnector
Dim strSql As String
' Create an INSERT SQL Statement
strSql = _
"INSERT INTO [Table1] ( [Field1], [Field2] ) " & _
"VALUES (""Field 1 Value"", ""Field 2 Value""); "
' Execute the SQL Statement against the Access database
dbConnector.RunSqlNonQuery(strSql)
So, in this above code, you just create a new instance of the "DatabaseConnector" class, create a SQL Statement (that in this case is non-record returning, like an INSERT or UPDATE statement), and then call the "RunSqlNonQuery()" method. Of course, if you wanted to run just a SELECT statement, you would have to create another method in the DatabaseConnector class that returns a DataReader (or something like that), but hopefully this example provides you with enough information to get you started. So, I hope all of this code helps, but please let me know if you have any other questions about this and I'll do what I can to help.
Also, I should mention that Chapter 20: "Working with .NET" in the
Access 2010 Programmer's Reference book talks about the various methods for using Microsoft Access with .NET, which provides lots of different examples of what you can do with .NET and Access. However, the code examples are actually in C# (and
NOT VB.NET), but the basic objects are all the same and should at least provide a little insight into how things work.
Sincerely,
VB.Net & MS-Access Connection
Linear Mode
