Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

Delete Queries in MS Access, Run from VB 2008

I am wanting to combine several delete statements in one MS Access query.  
How do I tell a command to execute a query?  I know in SQL Server I would just execute a stored procedure.
Please provide a code example and no links.  My IE crashes when I click on a link.
Thanks
Public Function DeleteSelected() As Boolean
        Dim oC As New OleDbCommand
        Dim i As Integer
 
        Try
            oC = oleCnn.CreateCommand
            oC.CommandText = CreateDeleteStatement()
            oC.Connection.Open()
            i = oC.ExecuteNonQuery()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
 
        Finally
            oC.Connection.Close()
            oC.Dispose()
        End Try
        If i > 0 Then
            Return True
        Else
            Return False
        End If
 
    End Function

Open in new window

0
Sheritlw
Asked:
Sheritlw
  • 4
  • 4
2 Solutions
 
rockiroadsCommented:
there isnt such a thing as stored procedures in access. I would expect either

1. Run a number of delete queries in vb.net (preferable)
2. See if you can open the database then run a  vba function which runs a series of delete statements


I would stick with coding in vb.net, create a function in there which takes the sql, you then run that with ExecuteNonQuery only. If you got a class, you can create the connection object as a member variable then use that or pass it into this delete statement. The caller is then responsible for initiating and closing the connection.
0
 
mfhorizonCommented:
One easy option is to create a String variable in your program and assign a string text similar to follows:

"delete * from table1 where....
go
delete * from table2 where...
go"
and execute it, in this way you can execute lots of queries in one go...

Enjoy!
0
 
SheritlwAuthor Commented:
Really wanted to avoid so much code in the front end, but since I have to use MS Access as the backend, the front end is getting heavy.
What I wanted was a sample, using the code I provided, showing me how to execute a query with parameters from VB 2008.  
I have done it from VB 6, but don't know how in VB.Net.
Thanks
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
rockiroadsCommented:
You mean you want to use parameterised queries?
you can create a OleDbCommand, use ? where you want the variables to go
then

eg

Dim sSql As String = "Delete * From mytable Where field1 = ? And field2 = ?"
dim cmd as OleDbCommand

'conn is your connection object
cmd As New OleDbCommand(sSql, conn)

cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("Field1", "xx")
cmd.Parameters.AddWithValue("Field2", 123)

cmd.ExecuteNonQuery()

0
 
SheritlwAuthor Commented:
In VB 6 I would execute a query with parameters like ...
            goConn.Execute "Exec UpdateCutSchedule " & LColumnID & ", #" & dStart & "#, " & iStart & ", " & LApptID & ""

and in Access the query would be...
UPDATE tblSchedules SET columnid = [@columnid], scheddate = [@SchedDate], enddate = dateadd("d",datediff("d",SchedDate,EndDate),[@SchedDate]), starttime = [@starttime], endtime = [@StartTime]+(EndTime-StartTime)
WHERE schedid=[@scheduleid];

I want to do something similar in VB.Net.

0
 
rockiroadsCommented:
so did u try the example I gave above?

0
 
SheritlwAuthor Commented:
yes, that is similar to what I've been using.
I basically wanted to provide the values/parameters and send to the query.
Like...  Execute QueryName
Parameter
Parameter  etc.
0
 
rockiroadsCommented:
how about this example for deleting using parameters

        Dim sSql As String = "DELETE * FROM myTable WHERE MyField = ?"

'conn is your connection object
        Dim command As New OleDbCommand(sSql, conn)

'Specify field to check as wel as its datatype then give it a value
        command.Parameters.Add("@MyField", OleDbType.Numeric).Value = 23
0
 
SheritlwAuthor Commented:
I  wanted to know how to send parameters to  a stored query  in  Access.  The delete was a poor example because it was so quick and easy to write the  sql  string.
I  have provided an example of  what  I  used to send parameters to a stored query  in MS Acess.
This  statement  "EXECUTE DuplicateCarBuild"    Execute plus the stored queries name...does what I need.

Public Function  AddRecord() as integer
 Dim cmd As New OleDbCommand
        Dim oleCnn As New OleDb.OleDbConnection(ConnectionString)
        Dim i As Integer = 0

        Try

            cmd.Parameters.Add(AddParam("BuildName", OleDbType.VarWChar, msBuildName))
            cmd.Parameters.Add(AddParam("CarBuildID", OleDbType.Integer, miCarBuildID))
            cmd.CommandText = "EXECUTE DuplicateCarBuild"
            cmd.Connection = oleCnn
            oleCnn.Open()
            i = cmd.ExecuteNonQuery()
            Dim idCmd As New System.Data.OleDb.OleDbCommand
            idCmd.CommandText = "Select @@IDENTITY"
            idCmd.Connection = oleCnn
            i = idCmd.ExecuteScalar()
            oleCnn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If oleCnn.State = ConnectionState.Open Then
                oleCnn.Close()
            End If
        End Try

        Return i

end function

 Public Shared Function AddParam(ByVal sP As String, _
                    ByVal dType As OleDbType, _
                    ByVal oValue As Object, _
                    Optional ByVal iSize As Integer = 0, _
                    Optional ByVal iScale As Integer = 0) _
                    As OleDb.OleDbParameter
        'Dim s As String = "Param" & sP
        Dim sParam As New OleDb.OleDbParameter

        If iScale > 0 Then
            With sParam
                .ParameterName = sP
                .OleDbType = dType
                '.Size = iSize
                .Scale = iScale
                .Value = oValue
            End With
        ElseIf iSize > 0 Then

            With sParam
                .ParameterName = sP
                .OleDbType = dType
                .Size = iSize
                .Value = oValue
            End With
        Else
            With sParam
                .ParameterName = sP
                .OleDbType = dType
                .Value = oValue
            End With
        End If

        Return sParam

    End Function
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now