Check if table exists before trying to delete

RichardMGreen

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
Hi all

I've got a piece of code I inherited that deletes some tables from a database before importing updated versions (I'm trying to get an append query to work but I'm struggling with the downloads).

At the moment it uses an on error event to just continue regardless but I'd like to make it a bit tidier.

This is the code I have:-
Code: 
    On Error Resume Next
    DoCmd.DeleteObject acTable, "ext_Activities"
    DoCmd.DeleteObject acTable, "ext_HTs"
    DoCmd.DeleteObject acTable, "ext_Clients"
    DoCmd.DeleteObject acTable, "ext_Assessments"
    DoCmd.DeleteObject acTable, "ext_Contacts"
    DoCmd.DeleteObject acTable, "ext_Wellbeing"
    DoCmd.DeleteObject acTable, "ext_PHPGoals"
    DoCmd.DeleteObject acTable, "ext_PostAssessments"
    DoCmd.DeleteObject acTable, "ext_Reviews"
    DoCmd.DeleteObject acTable, "ext_Maintenance"
    DoCmd.DeleteObject acTable, "ext_Deprived"

What I would like to do is test for the existense of each table in turn before doing the delete and remove the on error event completely.

I'll admit to the fact that my Access VBA knowledge is next to nil so any help would be greatly appreciated.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Richard

You can loop through the tables to find, deleted the ones you want.

What version of Access are you using?

Mind you, it's probably a good idea to work on the appending.

That might be a better approach, especially if you are deleting/creating tables regularly.
 
Upvote 0
Hi Norie

I'm only continuing with this database as part of a project to be handed over to someone else when it's finished.

The deleteing/creating of tables will only take place once a month (maybe twice in some cases). I've also got the database set to compact on close to keep the bloat down as much as possible.

I'm using Access 2003.

I can probably get the append to work at some point, it's the data downloads that are going into the tables that I'm struggling with. It looks like it's going to be very difficuly to only get new information from the last download without downloading everything (it's a problem with the data itself not the database).

::edit::
The code I've got is using this:-
Code: 
DoCmd.TransferText acImportDelim, "", "ext_Activities", file, True, ""
to import the data. Do I need to delete the tables themselves with this or can I just delete the contents and insert the new data or would I need to change the code for that as well?
 
Upvote 0
You can use the following code

Code: 
Function utfnTableExists(strTableName As String) As Boolean

' Parameters:
' ARGUEMENT             :   DESCRIPTION
'-----------------------------------------------------------------------------
' TableName (String)    :   Name of table to check for
'-----------------------------------------------------------------------------
' Returns:  True, if table found in current db, False if not found.
'=================================================  ============================

Dim strTableNameCheck
On Error GoTo ErrorCode

'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(strTableName)

'If no error and we get to this line, true
utfnTableExists = True

ExitCode:
    On Error Resume Next
    Exit Function

ErrorCode:
    Select Case err.Number
        Case 3265  'Item not found in this collection
            utfnTableExists = False
            Resume ExitCode
        Case Else
            MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, "hlfUtils.TableExists"
            'Debug.Print "Error " & Err.number & ": " & Err.Description & "hlfUtils.TableExists"
            Resume ExitCode
    End Select

End Function

Jack
 
Upvote 0
Thanks for all the input.
After a bit of experimentation I've discovered I can just delete the contents of the tables and do the import as normal using the same import code.
Saves a lot of messing about.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Simple way to delete table if it exists?
Replies
4
Views
2K
Joe4
Joe4
RichardMGreen
  • Question
Deleting tables and reimporting
Replies
5
Views
676
Joe4
Joe4

Forum statistics

Threads
1,214,653
Messages
6,120,755
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top