Delete Table

[pklocke]

Hey..I am an old-timer at this "new" stuff, so please bear with the dumb question. In the "olden" days, in access if I wished to delete a table, I would use:

DoCmd.DeleteObject acTable <mytablename>

Someone noted to me that DoCmd is really obsolete. Can you tell me what you would use, now? Also, I really should check to see if the table exists, before attempting to delete it. What's the best way to error trap that to make sure I delete only if it already exists?

I realize this is pretty simple, but really appreciate your help while I try to figure out how Access 2007 does things.

Thx!

pklocke

[OBP]

pklocke, from one old timer to another, welcome to the Forum.
There is nothing wrong with using the Docmd method, but as you say you should really check if the table is there.
You can use the TableDef and Table Collection to check if it is there.
This is one way to do it
Dim dbs As Object, tempTable As String
Set dbs = Application.CurrentData
' Search for Temporary Table object in AllTables collection.
tempTable = "no"
For Each obj In dbs.AllTables
If obj.Name = "tbl_Temp" Then
tempTable = "yes"
End If
Next obj
If tempTable = "yes" Then
DoCmd.DeleteObject acTable, "tbl_Temp"
End If

To see the TableDef version see the VBA Editor's Help on TableDefs Collection and the Example of "TableDef Object, TableDefs Collection Example"
which uses the
.TableDefs.Delete tdfNew.Name
method

[pklocke]

THanks..works like a champ....thx so very much!

Pklocke

[pklocke]

So, I ran into a bit of a snag on this...wondering if you may have additional insight. Before I delete the table, I need to check if the excel file I am going to import into the table exists. Is there a good way to check to see if a file exists (on my PC) and using that result to determine if I proceed with subsequent steps?

Thx!

Pklocke

[Imdabaum]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=559

Function FileOrDirExists(PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified file
' or folder exists, false if not.
'PathName : Supports Windows mapped drives or UNC
' : Supports Macintosh paths
'File usage : Provide full file path and extension
'Folder usage : Provide full folder path
' Accepts with/without trailing "\" (Windows)
' Accepts with/without trailing ":" (Macintosh)

Dim iTemp As Integer

'Ignore errors to allow for error evaluation
On Error Resume Next
iTemp = GetAttr(PathName)

'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select

'Resume error checking
On Error Goto 0
End Function

One I like to use.

[pklocke]

thanks for fast reply! I tried this code, but it returned false regardless of whether the file existed or not. Pretty sure I copied it right, but take a look at my usage..maybe I am not setting up the path correctly? My test code:


Dim test As Boolean
test = FileOrDirExists("C:\Documents and Settings\pklocke\My Documents\00-iTOP FY10\work\BadfilenoExists.xls")
MsgBox ("Result of Delete test: " & test)
test = FileOrDirExists("C:\Documents and Settings\pklocke\My Documents\00-iTOP FY10\work\ApplicationDetail.xls")
MsgBox ("Result of Delete test: " & test)
End Sub



Function FileOrDirExists(PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified file
' or folder exists, false if not.
'PathName : Supports Windows mapped drives or UNC
' : Supports Macintosh paths
'File usage : Provide full file path and extension
'Folder usage : Provide full folder path
' Accepts with/without trailing "\" (Windows)
' Accepts with/without trailing ":" (Macintosh)

Dim iTemp As Integer

'Ignore errors to allow for error evaluation
On Error Resume Next
iTemp = GetAttr(PathName)
'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select

'Resume error checking
On Error GoTo 0
End Function



I tried this, too:


Sub testdelete()
Dim test As Boolean
test = FileOrDirExists("C:\Documents%20and%20Settings\pklocke\My%20Documents\00-iTOP%20FY10\work\BadfilenoExists.xls")
MsgBox ("Result of Delete test: " & test)
test = FileOrDirExists("C:\Documents%20and%20Settings\pklocke\My%20Documents\00-iTOP%20FY10\work\ApplicationDetail.xls")
MsgBox ("Result of Delete test: " & test)
End Sub


Thx!!

Pklocke

[pklocke]

Ok..my bad..the "GOOD" file I was testing for an excel file with a .xlsx extension...once I fixed that, it worked. Sorry about that.

Is there a version like this that would also work for a file hosted on a sharepoint server, ie,
test = FileOrDirExists("http://teams10.sharepoint.mydomain.com/teams/itop/Periodic%20Reports/AssetMasterReport.xls")


??