Solved

Delete Queries in MS Access, Run from VB 2008

Posted on 2009-04-08
9
422 Views
Last Modified: 2012-05-06
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
Comment
Question by:Sheritlw
  • 4
  • 4
9 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 300 total points
ID: 24094844
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
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24094858
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
 

Author Comment

by:Sheritlw
ID: 24094905
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24095317
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
 

Author Comment

by:Sheritlw
ID: 24099931
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24100019
so did u try the example I gave above?

0
 

Author Comment

by:Sheritlw
ID: 24100223
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24104634
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
 

Accepted Solution

by:
Sheritlw earned 0 total points
ID: 24180730
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question