Avoideing error 3265

[Middlemarch]

Is there a method for seeing if a Table Exists?



I was using If IsObject(CurrentDb.TableDefs("Table11")) Then but this doesn't return true or false but 3265 Item not found in this collection if the table is not there.

[Bob Fitz]

Amazing what google finds. See: https://www.access-programmers.co.uk...exists.148109/

[Welshgasman]

Amazing what google finds. See: https://www.access-programmers.co.uk...exists.148109/

Now there is a novel idea Bob

[Bob Fitz]

Now there is a novel idea Bob

Yes. All this "thinking outside the box" is all well and good but sometimes it's better to think inside it

[Micron]

I hope the table name isn't really "Table11"

[Middlemarch]

It was Google where I found the routine that went to error. I ended up with

Function TableExists() As Boolean
    Dim tbldef As TableDef
    For Each tbldef In CurrentDb.TableDefs
       If tbldef.Name = "Table11" Then
          TableExists = True
          Exit Function
       End If
    Next tbldef
End Function

But it it really necessary to look through each table name ?
BTW What's wring with the name "Table11" ? It's generated by Access after a Saved ImportExport.
It's actually "Table1" but if that exists the new import calls it "Table11", "Table12". etc.

[June7]

Why are you importing to new table instead of existing?

If Access is assigning table name, how do you know what name to look for?

If you don't want to loop, can use error handling code.

[Middlemarch]

DoCmd.RunSavedImportExport won't write an an existing table, will it ? If yes, that'd make it much more straightforward.
I know what Access will call it, if it's deleted.
My logic was to avoid error handling as it isn't an error, but see what you mean.

[June7]

Yes, you can.

Create table first. Manually perform an import to existing table and save. Now it is available to run with code.

Worked for me.

[Middlemarch]

That's really weird. I'm sure there's only one way to do that. Mine will not use an existing table and calls it the same as the source table, (with "1" added if it exists).
I did try selecting a table first (which works for Exporting) but no change.

[June7]

What are you importing from?

[Middlemarch]

From a table in .accdb file.

[June7]

Okay, I tested import from Excel (making wrong assumption).

With Access source wizard options are to import as new table or set a link.

Why don't you just set a link? If you really need to load into local table, can then run INSERT SELECT action.

Or don't set a link and run an INSERT SELECT SQL action to pull directly from other table into existing local table.

Simple example:

INSERT INTO Airports SELECT * FROM Airports IN "C:\Users\June\Airports\AirportsAdmin.accdb";

That example works because there is no autonumber ID field involved and fields are same and same order in both tables. Otherwise, will likely be a little more complicated.

[Middlemarch]

I've set up 4 saved imports with 60 fields and special text/field delimiters! Your method would have ben easier that's for sure... but its all in and working now.
I need it be called Table11 though. And there is an ID autonumber field but I don't think its used.
It has 'come right' now with Drop Table only used if it exists. But any more trouble and I'll try your Insert Into.