Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run SQL (Access 2000)

    I work with success with the delete query suggested by Hans which is as follows

    DELETE TblClients.*
    FROM TblClients
    WHERE TblClients.CompanyName In (SELECT Customers.CompanyName FROM Customers);
    The query runs fine, i have no problems with it.However i need to build a function out of it.
    However, the funcioon i tried to build on the basis on that query seems not to function.What is the reason ?My function is :

    Public Function DeleteObsoleteContacts()
    ' delete those contacts whose company name matches the company name of a customer
    On Error Resume Next
    Dim SQlObsoleteContacts As String
    SQlObsoleteContacts = " DELETE TblClients.* FROM TblClients

    " & _
    " WHERE TblClients.CompanyName In (SELECT

    Customers.CompanyName FROM Customers):"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQlObsoleteContacts
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run SQL (Access 2000)

    You should not put On Error Resume Next in your code unless you have a very good reason for it. It will suppress any error messages, so you won't know if things go wrong.

    There are several superfluous line ends in your code as posted. It's impossible to know whether they are in the original code or only in your post. Moreover, the SQL string should not end in a colon :

    You should always reset SetWarnings to True if you set it to False temporarily.

    Try this:

    Public Function DeleteObsoleteContacts()
    ' delete those contacts whose company name matches the company name of a customer
    Dim SQlObsoleteContacts As String
    SQlObsoleteContacts = "DELETE TblClients.* FROM TblClients " & _
    "WHERE TblClients.CompanyName In " & _
    "(SELECT Customers.CompanyName FROM Customers)"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQlObsoleteContacts
    DoCmd.SetWarnings True
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •