Question

Locked

VB.NET - Run a MS Access Delete Query

By martinbullock15 ·
Hi

I have a program where i have created different types of querys to run to get information from my MS Access database but i can not work out how to run a Delete query.

This is the code i have so far:

Dim varConn As New ADODB.Connection
Dim varCmd As New ADODB.Command
Dim varRecSet As New ADODB.Recordset

Dim varSQLString As String

If varEnteredSelect = "" Then
lblInfo.Text = "NOTHING SELECTED TO REMOVE"
Exit Sub
End If

varSQLString = "DELETE * FROM tblUpdate WHERE LEFT(STKCODE,13) ='" & varEnteredSelect & "'"

varConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source = DATAPATH"

varConn.Open()


varCmd.CommandText = "tblUpdate"
varCmd.CommandType = ADODB.CommandTypeEnum.adCmdText
varRecSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient
varRecSet.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
varRecSet.LockType = ADODB.CursorTypeEnum.adOpenDynamic


As you can see from the SQLString i am trying to Delete a row from the database, based on the input from the user earlier in the program.

I would be very grateful for any help or different suggestions. If any more details are required let me know.

Kind Regards

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Sorted...

by martinbullock15 In reply to VB.NET - Run a MS Access ...

Sorry, i should have just waited another 5 minutes as i found a way of doing it by using the following code and losing the varSQLString:

varRecSet.Open("tblUpdate", varConn, , , ADODB.CommandTypeEnum.adCmdTable)

'Find Row to Delete
varRecSet.Find("STKCODE Like '" & varEnteredSelect & "'")

'Delete Row
varRecSet.Delete()
'Close Datatable
varRecSet.Close()
varConn.Close()

Collapse -

Hmm just bear in mind that while this

by Tony Hopkinson In reply to Sorted...

method is efficient in access beacause it's a desktop, it's a total no no on a full DBMS.

If performance isn't an issue (e.g. request from user input), you'd be better off just uesing a command object, selt it's connection and commandstring properties and then call it's executeNonQuery method.

The way you doing requres all teh data in the table to be sucked on to your machine, then locate the record, then delete, then post the change back.

Obviously if you were working on a hefty table out of sql server or oracle, people are going to jump up and down on your head if you use your method.

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums