Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete object (Win XP Office 2003)

    This code runs from a form in DB1, correctly creating the tmakRecentOrders table in another db, Northwind db; however, the DoCmd.DeleteObject statement returns an error saying the tmakRecentOrders tables is not found. It appears that the DoCmd is looking for the table in DB1, not in Northwind. Can you tell me why?

    Private Sub cmdExecute_Click()

    Dim wks As Workspace
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim tdf As TableDef

    Set wks = Workspaces(0)
    Set dbs = wks.OpenDatabase(cDBNAME)
    strSQL = "SELECT Orders.*, * INTO tmakNewOrders FROM Orders WHERE OrderDate>#1/6/96#;"
    Set qdf = dbs.CreateQueryDef("", strSQL) 'Took out name of query

    'Execute a make-table query to produce the tmakRecentOrders table
    qdf.Execute

    For Each tdf In dbs.TableDefs
    Debug.Print "Table Name: " & tdf.Name & vbTab & vbTab & _
    "Attributes: " & tdf.Attributes
    Next tdf

    DoCmd.DeleteObject acTable, "tmakRecentOrders"
    dbs.Close

    End Sub

    Thanks

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

    Re: Delete object (Win XP Office 2003)

    DoCmd.DeleteObject only works in the current database.

    Since you already have a DAO Database object dbs that refers to the external database, you can use that:

    dbs.TableDefs.Delete "tmakRecentOrders"

    By the way, instead of creating a querydef, you can execute the SQL string directly: replace the lines

    strSQL = "SELECT Orders.*, * INTO tmakNewOrders FROM Orders WHERE OrderDate>#1/6/96#;"
    Set qdf = dbs.CreateQueryDef("", strSQL) 'Took out name of query

    'Execute a make-table query to produce the tmakRecentOrders table
    qdf.Execute

    with

    strSQL = "SELECT Orders.* INTO tmakNewOrders FROM Orders WHERE OrderDate>#1/6/96#;"
    'Execute a make-table query to produce the tmakRecentOrders table
    dbs.Execute strSQL

    I also removed a superfluous * from the SQL.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete object (Win XP Office 2003)

    How did you do that so quickly?

    Thank you. That works OK.

Posting Permissions

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