I have two Jet-type databases. I want to copy a table from database 1
and paste it into database 2 in the simplest way possible. I'd like to
copy the data and the design all at once.

I thought this would work, but it doesn't:

    Dim db1 as Database
    Dim db2 as Database
    Dim tdfNewTable as TableDef

    Set db1 = OpenDatabase("db1.mdb")
    Set db2 = OpenDatabase("db2.mdb")

    Set tdfNewTable = db1.TableDefs("MyTable")
    db2.TableDefs.Append tdfNewTable

I get this far and it errors me: "Can't append. An object with that name
already exists in the collection." I can loop through the TableDefs
collection of db2 and print the Name property of each TableDef to the
debug window and none of them match the Name property of tdfNewTable.

I'm stumped; all of the examples I've found for .Append-ing a TableDef
to the TableDefs collection involved making a "blank" TableDef with
.CreateTable first, rather than loading a preexising one.

Any ideas what's going wrong here? What's the best way to do this? Is
there a simple solution, or do I have to iterate through countless
properties and fields?

Dave

I have come across this exact problem myself . I can't tell you exactly why
it happens but I can give a good guess and a solution . When you create the
new table it has the same name as the old one , this means you would be
creating 2 tables with the same name in the one Workspace and thats why the
error occurs , but thats just my humble opinion . Here is a possible
solution

Dim NewTableDef as Table
Dim StrTemp as String
Dim NewField as Field  

Set NewTableDef = VDb.CreateTableDef(StrTemp & " Temp")
For Each NewField In Db.TableDefs(StrTemp).Fields
  NewTableDef.Fields.Append_          
NewTableDef.CreateField(NewField.Name,NewField.Type, NewField.Size)
Next
VDb.TableDefs.Append NewTableDef
Db.Close
Set Db = Nothing
VDb.TableDefs(StrTemp & " Temp").Name = StrTemp

To explain :
        StrTemp is variable for the tables name
        VDb is the destination database
        Db is the source Database

What I did was create a new table in VDb with the name in StrTemp & an
extra string of " Temp" to make it unqiue . Then I looped thru the fields
in the source and appended those to the new table . Then I appended the
table to the destination , closed the source database and renamed the
appeneded table to what it should be . Its as simple as that and it does
work because I have it in an application already for updateing database
structures . To copy the data simply write some SQL to Insert into etc etc
, I haven't done that in the application but I'm sure its easy enough :)

Regards
        Alex

Or you can open two workspaces, one for each DB. Then you can copy the table
straight over.
--
|tlf:22 23 67 57 - *Jon Arne Hegge* - mob: 90 78 52 48|

--===> Wherever you are, there you are <===--

I did try this , creating 2 workspaces but it didn't work . That was the
first thing that popped into my head when I originally got this problem .
Out of interest I would love to know if somebody has over come this problem
in that way .

Alex

Thanks for responding, Alex.

Dave

Either way:
The table you are copying, does the structure of it change often?
If not, make an empty table in your DB which is exactly the same as the one
you're copying. And then you can only copy records from the DB, with
som proper recordset variables.

Jon Arne
--
|tlf:22 23 67 57 - *Jon Arne Hegge* - mob: 90 78 52 48|

--===> Wherever you are, there you are <===--

To restate my objective: I want to write a procedure that will take as parameters a
tableDef and a database not containing that tableDef and which will copy that table
and its data into the database.

Speed is not an issue, but I want to make an identical clone of the table into the
destination database, including all properties of the table and each field. Something
like using the "Import ..." menu command inside Access.

One potential solution I see might involve Database Replication, but that seems an
overly heavy-handed approach.

--
Dave LaDelfa                             Electronic Claims Management, Inc.

ICQ: 4977869                                            http://www.ecmi.net