locked
deleting Access Query Object in vb.net using OleDb

    Question

  • in pervious version of VB (DAO 3.6)  I used to be able to delete a query object and then creating it again (not the records) using the code below.  but I am having a hard time finding how to do it using the OleDb in VB.net.  does anyone know how to do this. 

    Dim ws As Workspace, db As Database
    Dim qy As QueryDef

    db.QueryDefs.Delete ("WISE QUERY")

    SQL = "select ...."

    Set qy = db.CreateQueryDef("WISE QUERY", SQL)

    • Moved by Amy Li Tuesday, November 02, 2010 6:57 AM Not relative to VSTO (From:Visual Studio Tools for Office)
    • Moved by Amy Li Wednesday, November 03, 2010 1:19 AM Related to that (From:Access for Developers)
    Monday, November 01, 2010 3:19 PM

Answers

  • none of the answer above help me at all.  I finally figured this one out by seaching the net.  here is the answer to my own question.  this is for anyone who is searching for the same answer.

     

    Dim myconnection As OleDb.OleDbConnection

            Dim wisepath As String

            wisepath = "c:\"

            Dim filename As String

            filename = "test.accdb"

     

     

            Dim connectionString As String

            Dim SQL As String

            Dim mycommand As OleDb.OleDbCommand

               connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PATHname & Filename & ";Jet OLEDB:Database Password=memo"

            myconnection = New OleDb.OleDbConnection(connectionString)

            myconnection.Open()

    'delete the query    

         SQL = "Drop Proc queryname  "

            mycommand = New OleDb.OleDbCommand(SQL, myconnection)

     

            mycommand.ExecuteNonQuery()

     

     

            SQL = "enter your sql here"

      'create query

            SQL = "Create PROC queryname as " & SQL

            mycommand = New OleDb.OleDbCommand(SQL, myconnection)

            mycommand.ExecuteNonQuery()

    

    good luck

     

    • Marked as answer by Sherry V Friday, April 15, 2011 12:40 PM
    Friday, April 15, 2011 12:39 PM

All replies

  • Hi Sherry V,

    I have moved the thread to Access For Developer Forum, from where you can find many experts in that field.
    They can offer more helps.

    Best Regards,
    Amy Li
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, November 02, 2010 6:59 AM
  • Amy,

     

    Even though this is realted to Access database, it is VB.NET programming question.  can you move it to VB.NET section.

     

    thanks

    Sherry

    Tuesday, November 02, 2010 5:31 PM
  • Hello, Sherry

    Thanks for your post!

    For using Ado.net to query Access, you may start here http://msdn.microsoft.com/en-us/library/ms971485.aspx.

    And For connect string of access database, please see this link http://www.connectionstrings.com/.

    Hope this helps.

    Thanks

    Chao


    Please unmark it if it does not help, and mark it if it helps.
    • Marked as answer by Sherry V Wednesday, November 10, 2010 6:42 PM
    • Unmarked as answer by Sherry V Wednesday, November 10, 2010 6:42 PM
    Tuesday, November 09, 2010 10:01 AM
    Moderator
  • Hi,

    My personal preference would be to send SQL Statements to the db (such as DROP VIEW statement, see http://office.microsoft.com/en-us/access-help/drop-statement-HP001032226.aspx).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Tuesday, November 09, 2010 10:58 AM
  • Chao,

    the information that you have given me does not tell me how to delete a query object, I know how to connect to database and run any action query or display the data,  I need to know if there is a command that deletes the object ( removes  it for good).

     

    Sherry

    Wednesday, November 10, 2010 6:46 PM
  • The Drop is only in Access and it does not work in the VB.NET codes. I would like to know if there is anyway I can delete/remove an existing query

     

    thanks

     

     

    Wednesday, November 10, 2010 6:48 PM
  • Of course you'll use an OleDbCommand to send the drop SQL statement to your Access database (I assume this is what you are using as you talked about DAO).

    See http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery.aspx. It allows to send whatever all SQL statements recognize by the jet engine including those who are creating/dropping tables, views and procedures...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Chao KuoModerator Thursday, November 11, 2010 9:42 AM
    • Unmarked as answer by Sherry V Monday, November 15, 2010 4:38 PM
    Wednesday, November 10, 2010 7:48 PM
  • what you are saying will not work.  I tried the drop statement and it says drop only works for table and index.  then I tired the code below:

     

      filename = "myfile.accdb"

       connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" &   Filename

            myconnection = New OleDb.OleDbConnection(connectionString)

            myconnection.Open()

            sql = "DeleteObject Query Myquery"

            Dim command As New OleDbCommand(sql, myconnection)

            command.ExecuteNonQuery()

    and I got the error

    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

     

    I need a command that says command.execute or somtehing similar. 

    Monday, November 15, 2010 4:47 PM
  • none of the answer above help me at all.  I finally figured this one out by seaching the net.  here is the answer to my own question.  this is for anyone who is searching for the same answer.

     

    Dim myconnection As OleDb.OleDbConnection

            Dim wisepath As String

            wisepath = "c:\"

            Dim filename As String

            filename = "test.accdb"

     

     

            Dim connectionString As String

            Dim SQL As String

            Dim mycommand As OleDb.OleDbCommand

               connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PATHname & Filename & ";Jet OLEDB:Database Password=memo"

            myconnection = New OleDb.OleDbConnection(connectionString)

            myconnection.Open()

    'delete the query    

         SQL = "Drop Proc queryname  "

            mycommand = New OleDb.OleDbCommand(SQL, myconnection)

     

            mycommand.ExecuteNonQuery()

     

     

            SQL = "enter your sql here"

      'create query

            SQL = "Create PROC queryname as " & SQL

            mycommand = New OleDb.OleDbCommand(SQL, myconnection)

            mycommand.ExecuteNonQuery()

    

    good luck

     

    • Marked as answer by Sherry V Friday, April 15, 2011 12:40 PM
    Friday, April 15, 2011 12:39 PM
  • Sorry it didn't help but the solution does use DROP as I suggested earlier.

    What is strange is that your previous attempt told that DROP was usable only for tables and indexes and now it works...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Friday, April 15, 2011 1:15 PM