error 3265 Item not in this collection?

[mikebres]

I'm inporting data and appending it to a table. The
method I'm using is to import the raw data into a temp
table which I call pcodes. Then use a query to filter
the data and append it top my main table. I've been
doing this one file at a time. I decided to add multiple
file selection to my file dialog routine. Here's the
problem, when the program gets to the 2nd file in the
loop and I try to delete the temp table. Then I get
error 3265, Item not found in this collection (code shown
below). Before the deletion I run a function that checks
to make sure the table is in the collection. It returns
true. Also I can look at the tables tab and see the
table pcodes. Yet I get the error.
Does anybody have any idea whay I am getting this error.
TIA
Mike

If .Show = True Then
'Loop through each file selected and add it
to our data
For Each varFile In .SelectedItems
If CheckPcodes() Then
db.TableDefs.Delete ("PCODES")
End If

'Import the file using previously defined
specification planetcode_imp_specs
'and create new file PCODES
DoCmd.TransferText
acImportDelim, "planetcode_imp_specs", "PCODES", varFile,
False

If CheckPcodes() Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMoveData"
DoCmd.SetWarnings True
End If
strPath = varFile
Next

 

[Dirk Goldgar]

I'm inporting data and appending it to a table. The
method I'm using is to import the raw data into a temp
table which I call pcodes. Then use a query to filter
the data and append it top my main table. I've been
doing this one file at a time. I decided to add multiple
file selection to my file dialog routine. Here's the
problem, when the program gets to the 2nd file in the
loop and I try to delete the temp table. Then I get
error 3265, Item not found in this collection (code shown
below). Before the deletion I run a function that checks
to make sure the table is in the collection. It returns
true. Also I can look at the tables tab and see the
table pcodes. Yet I get the error.
Does anybody have any idea whay I am getting this error.
TIA
Mike

If .Show = True Then
'Loop through each file selected and add it
to our data
For Each varFile In .SelectedItems
If CheckPcodes() Then
db.TableDefs.Delete ("PCODES")
End If

'Import the file using previously defined
specification planetcode_imp_specs
'and create new file PCODES
DoCmd.TransferText
acImportDelim, "planetcode_imp_specs", "PCODES", varFile,
False

If CheckPcodes() Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMoveData"
DoCmd.SetWarnings True
End If
strPath = varFile
Next

I notice you're using a previously set database object, db, as the base
for your call to the .TableDefs.Delete method. You also seem to be
making changes to the TableDefs collection of the current database by
mechanisms external to the db database object. Possibly you need to
refresh the db.TableDefs collection after doing so, and before working
with it. Try adding a line as shown below:

For Each varFile In .SelectedItems
db.TableDefs.Refresh

If CheckPcodes() Then
db.TableDefs.Delete ("PCODES")
End If

and see if that helps.

 

[mikebres]

Excelent!! That worked great. Thank You.

I notice you're using a previously set database object, db, as the base
for your call to the .TableDefs.Delete method. You also seem to be
making changes to the TableDefs collection of the current database by
mechanisms external to the db database object. Possibly you need to
refresh the db.TableDefs collection after doing so, and before working
with it. Try adding a line as shown below:


and see if that helps.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[sjbarber373]

I notice you're using a previously set database object, db, as the base
for your call to the .TableDefs.Delete method. You also seem to be
making changes to the TableDefs collection of the current database by
mechanisms external to the db database object. Possibly you need to
refresh the db.TableDefs collection after doing so, and before working
with it. Try adding a line as shown below:


and see if that helps.

Refreshing the TableDef worked for me - thanks Dirk!