REPOST with More info - tables are there but IsTablesPresent() tells me its not !

[Chris]

If all you want to do is delete them, why not just delete
the tables and let it fail if they don't exist?


On Error Resume Next
DoCmd.Delete acTable,"TableName"
DoCmd.Delete acTable,"TableName"
DoCmd.Delete acTable,"TableName"
DoCmd.Delete acTable,"TableName"


Chris

-----Original Message-----
Hi,

After stepping through carefully, here is my observation -

note : my problem is FIND these staging tables and REMOVE
if EXISTS - Before, After or During (if the process
fails) Import Process.

1) Run #1 : the Import Stage Tables exist (did not get

deleted during last run for whatever reason)

2) Function will find these residual Import Stage Tables and remove it.

3) New sets of Import Stage Tables get created and the Import Process begins.

4) During CleanUpAfterImport, function will find these

Import Stages Tables exist and remove it.

5) No residual Import Staging Tables found - (good cleanup !)

6) Run #2 begins

7) Function will find NO residual Import Stage Tables.

8) New sets of Import Stage Tables get created and the Import Process begins.

9) During CleanUpAfterImport, Import Stages Tables are

there but the function returns as UNABLE to find any.

and therefore Not REMOVED !

10) Import Staging Tables not deleted.

It appears that the existence of tables are registered

only the first time the *.mdb file is opened. If I add
another table to it, and then check if my new table is
there, it will not be able to find it eventhough its there
(supposedly the registered items in the beginning does not
reflect this table's existence).

Do I have to refresh the table objects ?? so jet

registers additional information after it first open ???
If so, how do I do so ???

Any help is appreciated ...




----- cameron wrote: -----

Hi,

I am working on importing data from Staging IMP

tables into Staging EXP tables. All rs* and the
objCurrentDB are declared as globals.

Refer to BeginImport() - after the import process, I

want to delete all existing Staging tables.

This is so strange because, Run #1 - always find

files and delete ok, Run #2 - files are there but
says "Items not found in this collection" if I do the
cursor over on the strFindTable = objCurrentDB.TableDefs
(arrStagingTables(intLoop)).Name code and No delete takes
place, Run #3 - find files, Run #4 - no find again...

I have included at different specific location in

the BeginImport() to call IsTablesPresent() to debug -
this function always return "Tables Not Exists".

Tables are there I am able to export the data, this

I have verified. I do not know if my explanation is clear
enough. If not, please reply asking for clarifications.
I am trying to explain as clear as I can.

Appreciate any help.

function BeginImport() 'extract only - has too many lines to put in here

Set objCurrentDB = CurrentDb
.
. call sub to remove any Staging tables

found. (***this call always remove all Staging
tables found)

.copy tables to import and export into Staging..... table names
.IsTablesPresent()
.
Set rsIMPTable1 = objCurrentDB.OpenRecordset

("Staging Import Table1", dbOpenForwardOnly)

Set rsIMPTable2 = objCurrentDB.OpenRecordset

("Staging Import Table2", dbOpenDynaset)

'
Set rsEXPTable1 = objCurrentDB.OpenRecordset

("Staging Export Table1", dbOpenDynaset)

Set rsEXPTable2 = objCurrentDB.OpenRecordset

("Staging Export Table2", dbOpenDynaset)

.IsTablesPresent()
.
. loop each record in rsIMPTable1 until EOF
. logic to handle when to do ADD or EDIT to EXP tables
. EOF
.
. call sub to rename only the Staging EXP tables into Master tables
.IsTablesPresent()
. call sub to remove any Staging tables

found. (***this call tells me NO Staging tables found
on alternate run)

.IsTablesPresent()
.
. close recordset and objCurrentDB
end function


Private Sub IsTablesPresent()

'check if Staging Import/Export Table1/2 present

On Error GoTo errorhandler
Debug.Print "*********** Is Tables Present ****************"
Dim intLoop, strFindTable
'Find each table if its exits, if YES - delete the table, NO - do nothing
For intLoop = LBound(arrStagingTables) To UBound(arrStagingTables)
strFindTable = ""
strFindTable = objCurrentDB.TableDefs (arrStagingTables(intLoop)).Name
If strFindTable = "" Or Err.Number = 3265 Then
'do nothing - table does not exist
Debug.Print " ---" &

arrStagingTables(intLoop) & " DOES NOT EXIST"

Else
'DoCmd.DeleteObject acTable, arrStagingTables(intLoop)
Debug.Print " ---" &

arrStagingTables(intLoop) & " EXIST "