Checking for existance of tables using VB in Access 2000

[David Gartrell]

14th May???? Oops sorry - date wrong on my PC!

 

[Gunnar Nygaard]

14th May???? Oops sorry - date wrong on my PC!

Hi David

I assume you delete the table and then re-generate it?
What you could do is to delete it without checking, and simply ignore or
trap the errors that may arise.

'*****************
'Ignore possible errors
On Error Resume Next

'Delete the thing, no matter what (In my case a query)
DoCmd.DeleteObject acQuery, QUERY_NAME_TO_UPDATE

'Assume normal error handling
On Error GoTo Err_LagreSporring5

'Run your code to re-generate the object

'*****************


Hth

Rgds
Gunnar Nygaard

 

[SusanV]

Hi David,

I have a similar situation with a temp table (tblTemp), this is the code I
use:

If Not IsNull(DLookup("Name", "MSysObjects", "[Name] = 'tblTemp'")) Then
DoCmd.DeleteObject acTable, "tblTemp"
End If

 

[David Gartrell]

Hi David

I assume you delete the table and then re-generate it?
What you could do is to delete it without checking, and simply ignore or
trap the errors that may arise.

'*****************
'Ignore possible errors
On Error Resume Next

'Delete the thing, no matter what (In my case a query)
DoCmd.DeleteObject acQuery, QUERY_NAME_TO_UPDATE

'Assume normal error handling
On Error GoTo Err_LagreSporring5

'Run your code to re-generate the object

'*****************


Hth

Rgds
Gunnar Nygaard
Hi Gunnar,

Thanks for your help. I am a little puzzled though by the line 'On Error
GoTo Err_LagreSporring5'. I don't recognise the code after the GoTo command.
Is this something unique to your own situation?

Thanks

David

 

[David Gartrell]

Hiya Sarah

Thanks very much for your help.

Hi David,

I have a similar situation with a temp table (tblTemp), this is the code I
use:

If Not IsNull(DLookup("Name", "MSysObjects", "[Name] = 'tblTemp'"))
Then
DoCmd.DeleteObject acTable, "tblTemp"
End If

--
hth,
SusanV

14th May???? Oops sorry - date wrong on my PC!

 

[Gary Walter]

Hi David,

I have always used Joe Fallon's code:
(you will need DAO reference set)

****quote****
If TableExists("tblTemp") Then
CurrentDb.Execute "DROP TABLE tblTemp", dbFailOnError
End If

You need this function in one of your code modules:

Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

***unquote**

Thanks very much for your help.

Hi David,

I have a similar situation with a temp table (tblTemp), this is the code
I use:

If Not IsNull(DLookup("Name", "MSysObjects", "[Name] = 'tblTemp'"))
Then
DoCmd.DeleteObject acTable, "tblTemp"
End If

--
hth,
SusanV

14th May???? Oops sorry - date wrong on my PC!
message Hi there,

I wonder if someone could help me please. I have an Access 2000
database
which needs to create a temporary table and then later delete it again
(lets
call it 'tableA'). Is there a way (in VB) of checking whether the
table
exists before executing subsequent code. 99% of the time it will exist,
but
sometimes if there's a computer crash etc the table may be left behind
when
it should be deleted.

Any help would be greatly appreciated.

many thanks

David.

 

[Gunnar Nygaard]

*snip*

Thanks for your help. I am a little puzzled though by the line 'On Error
GoTo Err_LagreSporring5'. I don't recognise the code after the GoTo
command. Is this something unique to your own situation?

Thanks

David

Err_LagreSporring5 is correctly just a name used in my particular snippet of
code.

Example on my usual error handling. You'll see this a lot of places.


Function SomeFunction()
On Error GoTo Err_SomeFunction
'Do stuff

'Make exception from error handling:
On Error Resume Next
'Do some dirty other stuff

'Assume normal error handling
On Error GoTo Err_SomeFunction

'Do more stuff

Exit_SomeFunction:
'Maybe do some clean-up
Exit Function

Err_SomeFunction:
'Do your error handling
If Err.Number = X Then
'do some stuff if you expect err #X and know why
Else
MsgBox Err.Description & " - ModuleName - SomeFunction"
Resume Exit_SomeFunction
End If

End Function

 

[David Gartrell]

Hi there,

I wonder if someone could help me please. I have an Access 2000 database
which needs to create a temporary table and then later delete it again (lets
call it 'tableA'). Is there a way (in VB) of checking whether the table
exists before executing subsequent code. 99% of the time it will exist, but
sometimes if there's a computer crash etc the table may be left behind when
it should be deleted.

Any help would be greatly appreciated.

many thanks

David.

 

[jacksonmacd]

If you *know* that it's not required, you can explicitly delete it
just before you create it. If you include an On Error Resume Next
statement before the delete command, then your program will proceed
correctly even if the table does not exist.

Hi there,

I wonder if someone could help me please. I have an Access 2000 database
which needs to create a temporary table and then later delete it again (lets
call it 'tableA'). Is there a way (in VB) of checking whether the table
exists before executing subsequent code. 99% of the time it will exist, but
sometimes if there's a computer crash etc the table may be left behind when
it should be deleted.

Any help would be greatly appreciated.

many thanks

David.

**********************[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security