The following are stored procedures used in a VB.Net program I have. Now, if I wanted to transfer it to Excel, what is the equivalent code to be used in VBA?
Dim ds As New DataSet()
Dim da As New OleDbDataAdapter()
Dim conn As New OleDbConnection()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As system.EventArgs) Handles MyBase.Load
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\ADODB\sample.accdb;"
da.SelectCommand = New OleDbCommand("SELECT * FROM tbl_Master")
da.SelectCommand.Connection = conn
da.UpdateCommand = New OleDbCommand("UPDATE tbl_Master SET FirstName = @FirstName, LastName = @LastName, Location = @Location WHERE EmployeeID = @EmployeeID")
da.UpdateCommand.Connection = conn
With da.UpdateCommand.Parameters
.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
.Add("@LastName", OleDbType.VarChar, 40, "LastName")
.Add("@Location", OleDbType.VarChar, 40, "Location")
.Add("@EmployeeID", OleDbType.Integer, 5, "EmployeeID")
End With
da.InsertCommand = New OleDbCommand("INSERT INTO tbl_Master(FirstName, LastName, Location) VALUES(@FirstName,@LastName,@Location)")
da.InsertCommand.Connection = conn
With da.InsertCommand.Parameters
.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
.Add("@LastName", OleDbType.VarChar, 40, "LastName")
.Add("@Location", OleDbType.VarChar, 40, "Location")
End With
da.DeleteCommand = New OleDbCommand("DELETE FROM tbl_Master WHERE EmployeeID = @EmployeeID")
da.DeleteCommand.Connection = conn
da.DeleteCommand.Parameters.Add("@EmployeeID", OleDbType.Integer, 5, "EmployeeID")
da.Fill(ds)
End Sub
Comments
Post a Comment