Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to delete in remote DB (Access 2000)

    I want to delete all customers that have a blank CompanyName in a remote database
    I cannot do it.
    I can carry out the deletion in the current database like that:

    Dim bas As String
    bas = " DELETE Customers.*, Customers.CompanyName FROM Customers WHERE

    (((Customers.CompanyName) Is Null))"
    CurrentDb.Execute bas

    Then all customers that have a blank company name are deleted.

    However,i tried in vain to delete them in a remote database
    the function i have made gives no error, but the customers with blank Company Name

    are not deleted :
    Public Function DeleteCustomersInRemoteDB()
    Dim db As DAO.Database
    Dim StrPassword As String
    StrPassword = "oil"
    Set db = DBEngine.Workspaces(0).OpenDatabase(BEpath, dbDriverComplete, False, ";

    PWD=" & StrPassword)
    Dim bas As String
    bas = " DELETE Customers.*, Customers.CompanyName FROM Customers WHERE

    (((Customers.CompanyName) Is Null))"
    db.Execute StrSQL
    End Function
    It is very strange,because a simlar function for updating does work:

    Public Function Changeaffiliate()
    Dim db As DAO.Database
    Dim StrPassword As String
    StrPassword = "secret"
    Set db = DBEngine.Workspaces(0).OpenDatabase(BEpath, dbDriverComplete, False, ";

    PWD=" & StrPassword)
    Dim StrSQL As String
    StrSQL = "UPDATE bank SET affiliate = 3333 WHERE bankid = 1"
    db.Execute StrSQL
    End Function

    I cannot explain myself why it happens.Can somebody help me ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to delete in remote DB (Access 2000)

    Is this an Access database you're trying to open? If so, why are you using dbDriverComplete? You appear to be trying to use it on a database object rather than on an ODBCDirect workspace.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to delete in remote DB (Access 2000)

    I do not know, you may be right,but it works with updating and also in all other cases. I cannot explain myself why my command does not delete.Also,my command does not work or delete with a linked table even in the current database. It works only when the table is imported and not linked. Why is it so, and how can i remedy it?

    Best Regards

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

    Re: how to delete in remote DB (Access 2000)

    In a test, I was able to delete records in a linked table, and I was also able to delete records in a remote table using code similar to yours. So the code itself seems to be OK.

    Is it possible that the records can't be deleted because there are related records in other tables? This can happen if referential integrity has been set for the relationships between Customers and other tables.

Posting Permissions

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