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.
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.
Amazing what google finds. See: https://www.access-programmers.co.uk...exists.148109/
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Now there is a novel idea BobAmazing what google finds. See: https://www.access-programmers.co.uk...exists.148109/
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
I hope the table name isn't really "Table11"
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
It was Google where I found the routine that went to error. I ended up with
But it it really necessary to look through each table name ?Code: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
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.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
What are you importing from?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
From a table in .accdb file.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.