Re: Delete an Access query using VB code

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Al Reid (areidjr_at_reidDASHhome.com)
Date: 01/21/05 

Date: Fri, 21 Jan 2005 11:03:02 -0500

Bob,

This is easily accomplished via ADO. Look at the OpenSchema Method of the connection object. You can retrieve a list of views and
determine if the view exists.

Alternatively, you can just drop the view and either handle the error that is thrown if it does not exist, or ignore it.

Create a connection and Execute the DDL Statements.

cnn.Open
cnn.Execute "DROP VIEW TEST"
cnn.Execute "CREATE VIEW TEST AS " & _
                          "SELECT Field1, Field2, Field3 " & _
                          "FROM MyTable " & _
                          "WHERE Field4 = 'Y' = 2;"

In fact, you can execute any valid SQL DDL statements to create, drop, truncate and alter tables, create views, stored procedures,
etc. through ADO.

If you have any specific questions, post back. 

-- 
Al Reid
"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so."  --- Mark Twain
"bob bonehead" <bob@bonehead.com> wrote in message news:O2DHR52$EHA.2552@TK2MSFTNGP09.phx.gbl...
> Hello everyone,
>
> I hope I posted this to the newsgroups correctly.
>
> My question is this. I have an access database with a query in it that needs
> to be updated. There is a bug in it so we need to write VB code to go out to
> the database, find the query if it exists, delete it and create a new one.
>
> How can this be done? If it where just one database, we could update it
> manualy but there are about 60 copies of it floating arount the state of
> Pennsylvania, making it hard to get everyone of them in to update, so we
> will send a service pack out with the database update in it, if we can do
> what we need to do.
>
> Thanks
>
> Dunnie
>
>

Relevant Pages

  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • ADO exception with character combination inside string...
    ... We also have various Delphi7 programs that operate on this database. ... These programs are set up to trap the exception in an ADO operation ... and log the query text that was in use when the exception happened. ... What happens is that if we have a string to store inside the database ...
    (borland.public.delphi.database.ado)
  • Re: Bitwise And
    ... I don't know how to create queries using ADO. ... ANSI query looks like in the MSysObjects table. ... > ANSI queries in a Non-Ansi database. ...
    (microsoft.public.access.queries)
  • Re: Cant find table
    ... > I'm connecting to a SQL database to get data for an Excel ... > I wrote some ADO code in the Excel workbook to connect to ... > the Access database and run the query and return the data. ...
    (microsoft.public.sqlserver.programming)
  • Re: Delete an Access query using VB code
    ... Our whole project is based on ADO. ... > using DAO and that I'm not deleting the query if it exists, ... > Doug Steele, Microsoft Access MVP ... >> go out to the database, find the query if it exists, delete it and create ...
    (microsoft.public.vb.database.dao)