Add a table to new database from existing one

[dlarmour]

Dear all,

I am creating a table in a database and am trying to put that table in a different database. I get an error 3367 "Object already exists in collection" Not sure what I am doing wrong. My code is:

Dim sFileName As String
Dim catThisDb As ADOX.Catalog
Dim catSource As ADOdb.Connection
Set catThisDb = New ADOX.Catalog
catThisDb.ActiveConnection = CurrentProject.Connection
Set tbl = New ADOX.Table
Set tbl = catThisDb.Tables("Yields")

Set catSource = New ADOdb.Connection
catSource.ActiveConnection = "Provider=Microsoft.JET.OLEDB.4.0;Data Source= C:\Clients\WF1.mdb"
catSource.TableDefs.Append tbl

Thanks,
D

[jggtz]

Not tested your code but that error is saying that the table you want to append is already in the database... Check and say if that is not the case...

JG

[dlarmour]

The table does not already exist in the database to which I want to append.

[techgnome]

you're not doing what you think you're doing... you can't pull up an existing table def, and then simply append it to a new database... it still belongs to the old database....

you have to create a new table def object, loop through the fields of the old table, copy them over (by creating new ColumnDefs and appending them to the new table) ... THEN you can append your new table to the second database.

-tg

[dlarmour]

Ok, I am trying the following code. (TableDef is not recognized.) I am getting error "Arguments are of the wrong type or in conflict".

Dim sFileName As String
Dim catThisDb As ADOX.Catalog
Dim catSource As ADOdb.Connection
Set catSource = New ADOdb.Connection
Set catThisDb = New ADOX.Catalog
catThisDb.ActiveConnection = CurrentProject.Connection
Dim tblNew As ADOX.Table
Set tbl = New ADOX.Table
Set tbl = catThisDb.Tables("Yields")
Set tblNew = New ADOX.Table
tblNew.Name = "Yields"

For Each col In tbl.Columns
tblNew.Columns.Append (col)
Next col
catSource.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source= C:\Clients\WF.mdb"

Set tblNew = catSource.Tables("Yields")
catSource.Tables.Append tblNew

Thanks,
-D