Checking for existance of tables using VB in Access 2000

  • Thread starter Thread starter David Gartrell
  • Start date Start date
David Gartrell said:
14th May???? Oops sorry - date wrong on my PC!
Click to expand...

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 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
 
Gunnar Nygaard said:
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,
Click to expand...

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
 
Hiya Sarah

Thanks very much for your help.


SusanV said:
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


David Gartrell said:
14th May???? Oops sorry - date wrong on my PC!
Click to expand...
Click to expand...
 
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**

David Gartrell said:
Thanks very much for your help.


SusanV said:
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


David Gartrell said:
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.
Click to expand...
Click to expand...
Click to expand...
 
*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
Click to expand...
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
 
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.
 
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.
Click to expand...
**********************[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Back
Top