Runtime Error 3367 when using TableDefs.Append

[mattcdse]

Hi all,

I keep getting the following error:-

Run-time error '3367':

Cannot Append. An object with that name already exisits in the collection.


This occurs when I use TableDefs.Append.

The situation is this. I'm developing a module to create a table. Passed ot the module is a string with the new table's name and a string array with fields name and type. Currently as I am testing I am only creating a table with the name Test and fields test0 to test4, all type dbText.

Currently my module checks to see if the table test already exists. If it does it deletes it and refreshes TableDefs. It then creates the new table and adds the fields. This first field (test0) is always added without issue, the second field (test1) is always when the code fails at TableDefs.Append.

Here is the code:-

Public Sub CreateTable(strTblName As String, strTblFields() As String)

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim intCount As Integer
Dim boolTableExists As Boolean

Set db = CurrentDb

boolTableExists = False
intCount = 0

Do While boolTableExists = False And intCount <= db.TableDefs.Count - 1
        
        If db.TableDefs(intCount).Name = strTblName Then boolTableExists = True
        intCount = intCount + 1

Loop

If boolTableExists Then db.TableDefs.Delete (strTblName)
db.TableDefs.Refresh

Set tbl = db.CreateTableDef(strTblName)

intCount = 0

Do While intCount <= UBound(strTblFields, 1)

    tbl.Fields.Append tbl.CreateField(strTblFields(0, intCount), strTblFields(1, intCount))
    db.TableDefs.Append tbl
    db.TableDefs.Refresh
    intCount = intCount + 1

Loop

End Sub

Many thans in advance for any help

Matt

 

[gemma-the-husky]

try this to test if the table exists - you dont have to check every table
change tablename to suit, or use a variable

boolfound=false
on error resume next
if currentdb.tabledefs("tablename").name = "tablename" then boolfound=true

 

[mattcdse]

Hi all

I've had a sandwich moment! (You know, settled down for some lunchtime munchies, not really thinking too hard about the problem in hand and voila! The answer jumps straight into my head!

Simple really and particularly muppetry of me not to notice! Serves me right for slavishly copying code!


db.TableDefs.Append tbl appends the whole table therefore you can do this once for a particular table.

When I add multiple fields using tbl.Fields.Append, I don't need to use db.TableDefs.Append tbl each time I append a field, therefore it should not be within the Loop.

This is why it would parse the loop once as the new table had no yet been appended, but on the second loop it had been, causing the clash in table names.

Gemma - Thanks for your reply. I tend to use extended code for testing and keep error checking running at all times. Once I'm happy with code I'll start introducing Resume Next sections. This is just for peace of mind, one of my prefered quirks. Resume next does often provide a nice shortcut I must admit

Cheers,

Matt

 

[gemma-the-husky]

resume next wasnt breaking a search loop

your code seemd to be checking every table to see if the one you wanted was there

----------
instead all you have to do is try to verify whether table you are looking for exists, directly, by trying to get any property (but in this case the name) of the table

if the table does exist, it will obviously return a value - in this case the name of the table, and therefore the search result is TRUE

BUT if it doesnt find the table(ie the search is FALSE) you will ACTUALLY get a run time error - so resume next merely says, im not bothered about the runtime error in this case - just ignore it, and set the search result to false - but you HAVE to handle the error