delete table in remote db (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote