Delete object (Win XP Office 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

How did you do that so quickly?

Thank you. That works OK.

Reply | Quote