Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete table in remote db (Access 2000)

    I want to delete a table in a remote database. In order to do that i have to delete the relation, otherwise i am not allowed.The table to be deleted is called products and it is related with the table order details. My friend has given me a function to do that. The function works, but i am asked two times about the password, even though i have declared it to be " secret".How can i correct the code ? By second clicking the code deletes the table products indeed
    Private Sub Command1_Click()
    Dim test As String
    Dim ThisRel As Relation
    Dim wsp As DAO.Workspace
    Dim StrPassword As String
    StrPassword = "secret"
    Set wsp = DAO.DBEngine.Workspaces(0)
    Dim dbs As DAO.Database
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & StrPassword)
    For Each ThisRel In dbs.Relations
    If ThisRel.table = "products" Or ThisRel.ForeignTable = "order details" Then
    dbs.Relations.Delete ThisRel.Name
    End If
    Next ThisRel
    Call KillObject("C:BEstoreBE.mdb", 0, "products")
    End Sub

    Public Sub KillObject(strDbName As String, acObjectType As Long, strObjectName As String)
    Dim adb As Object
    Set adb = CreateObject("Access.Application")
    adb.OpenCurrentDatabase (strDbName)
    adb.DoCmd.DeleteObject acObjectType, strObjectName
    adb.CloseCurrentDatabase
    Set adb = Nothing
    End Sub

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

    Re: delete table in remote db (Access 2000)

    You open the database using DAO in the line Set dbs = wsp.OpenDatabase(...), then you open it again using Automation in the KillObject function in the line adb.OpenCurrentDatabase (...).
    In the first place, there is no need to open the database twice, and in the second place, you only specify the password in the first line, not in the second one.

    Instead of the line

    Call KillObject("C:BEstoreBE.mdb", 0, "products")

    use

    dbs.TableDefs.Delete "products"

    You don't need KillObject any more.

    Note: you should close dbs at the end of the procedure:

    dbs.Close

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete table in remote db (Access 2000)

    Just to let you know that it is perfect now . Thank you so much. !

Posting Permissions

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