How to copy a table definition to a new database 
Author Message
 How to copy a table definition to a new database

I want to create an exact, but empty, copy of a table in a different
database (for archiving).  I tried the following...

        Set tdfOld = dbOld.TableDefs("MyTable")               ' Get tabledef from original
table
        dbNew.TableDefs.Append tdfOld                   ' Append it to the new database

but received the error "An object with that name already exists in the
collection".  Does that mean a tabledef is 'hard coded' to a database?

I then tried to loop through the fields in the fields collection and add
them to a new tabledef.  That worked, but I ran into problems when trying
to duplicate each field's properties.  Some properties in the properties
collection had values of '<Invalid operation>'.  I didn't have any better
luck trying to copy the indexes.  

Is there a simple way to duplicate a table?  If it requires creating every
object and setting every property individually, does anyone have the code
to do this that they would share?  

Thanks.
--
Bob Voss
Twin State Technical Service



Fri, 09 Feb 2001 03:00:00 GMT  
 How to copy a table definition to a new database
Bob,

Try Docmd.Transferdatabase <appropriate parameters here>

HTH
Dimitri

Quote:

> I want to create an exact, but empty, copy of a table in a different
> database (for archiving).  I tried the following...

>         Set tdfOld = dbOld.TableDefs("MyTable")         ' Get tabledef from original
> table
>         dbNew.TableDefs.Append tdfOld                   ' Append it to the new database

> but received the error "An object with that name already exists in the
> collection".  Does that mean a tabledef is 'hard coded' to a database?

> I then tried to loop through the fields in the fields collection and add
> them to a new tabledef.  That worked, but I ran into problems when trying
> to duplicate each field's properties.  Some properties in the properties
> collection had values of '<Invalid operation>'.  I didn't have any better
> luck trying to copy the indexes.

> Is there a simple way to duplicate a table?  If it requires creating every
> object and setting every property individually, does anyone have the code
> to do this that they would share?

> Thanks.
> --
> Bob Voss
> Twin State Technical Service



Fri, 09 Feb 2001 03:00:00 GMT  
 How to copy a table definition to a new database
Docmd.Transferdatabase works for the situation I described.  Unfortunately
the original table is linked so the copy ends up being linked also.  I need
a 'real' table.  Any other ideas?

Thanks again.
--
Bob Voss
Twin State Technical Service



Quote:

> Try Docmd.Transferdatabase <appropriate parameters here>


> > I want to create an exact, but empty, copy of a table in a different
> > database (for archiving).  I tried the following...



Sat, 10 Feb 2001 03:00:00 GMT  
 How to copy a table definition to a new database
Hm, maybe you can use a make-table query (SELECT <list of
all fields> INTO) with WHERE clause that doesn't return any
records. That would create an empty table with the same
field structure in any database of your choice.
Unfortunately, field properties and indexes won't be
preserved (I guess you could add them in code after that,
but that could be a bit of pain). Or, if the original table
isn't too big, just import it with all its records into your
front end database and then delete all records. Can't think
of anything else right now.

Dimitri

Quote:

> Docmd.Transferdatabase works for the situation I described.  Unfortunately
> the original table is linked so the copy ends up being linked also.  I need
> a 'real' table.  Any other ideas?

> Thanks again.
> --
> Bob Voss
> Twin State Technical Service



> > Try Docmd.Transferdatabase <appropriate parameters here>


> > > I want to create an exact, but empty, copy of a table in a different
> > > database (for archiving).  I tried the following...



Sat, 10 Feb 2001 03:00:00 GMT  
 How to copy a table definition to a new database
I have already created the fields with code.  The field properties and
indexes are what gave me problems.  The database is over 300 MB so
importing it is not feasible.  Since the source table is linked, I guess
using code is the only answer.  

Thanks for the suggestions.
--
Bob Voss
Twin State Technical Service



Quote:
> Hm, maybe you can use a make-table query (SELECT <list of
> all fields> INTO) with WHERE clause that doesn't return any
> records. That would create an empty table with the same
> field structure in any database of your choice.
> Unfortunately, field properties and indexes won't be
> preserved (I guess you could add them in code after that,
> but that could be a bit of pain). Or, if the original table
> isn't too big, just import it with all its records into your



Sun, 11 Feb 2001 03:00:00 GMT  
 How to copy a table definition to a new database

Quote:

> Docmd.Transferdatabase works for the situation I described.  Unfortunately
> the original table is linked so the copy ends up being linked also.  I need
> a 'real' table.  Any other ideas?

> Thanks again.
> --
> Bob Voss
> Twin State Technical Service



> > Try Docmd.Transferdatabase <appropriate parameters here>


> > > I want to create an exact, but empty, copy of a table in a different
> > > database (for archiving).  I tried the following...

Try opening the database object of the database that has the actual
table. Then  use the  DoCmd.TransferDatabase.

Private Sub Command0_Click()
Dim dbs As DATABASE
Dim strSourceDatabase As String
Dim strDestinationDatabase As String

strSourceDatabase = "C:\My Docuements\MySource.mdb"

Set dbs = OpenDatabase(strSourceDatabase)

strDestinationDatabase = "C:\My Documents\MyDestination.mdb"
'DoCmd.TransferDatabase [transfertype], databasetype, databasename [,
objecttype], source, destination [, structureonly] [, saveloginid]
DoCmd.TransferDatabase  acExport, "Microsoft Access"
,strDestinationDatabase, acTable,  "SourceTableName",
"DestinationTableName"

dbs.Close

End Sub



Tue, 13 Feb 2001 03:00:00 GMT  
 How to copy a table definition to a new database

Quote:


> > Docmd.Transferdatabase works for the situation I described.  Unfortunately
> > the original table is linked so the copy ends up being linked also.  I need
> > a 'real' table.  Any other ideas?

> > Thanks again.
> > --
> > Bob Voss
> > Twin State Technical Service



> > > Try Docmd.Transferdatabase <appropriate parameters here>


> > > > I want to create an exact, but empty, copy of a table in a different
> > > > database (for archiving).  I tried the following...

> Try opening the database object of the database that has the actual
> table. Then  use the  DoCmd.TransferDatabase.

> Private Sub Command0_Click()
> Dim dbs As DATABASE
> Dim strSourceDatabase As String
> Dim strDestinationDatabase As String

> strSourceDatabase = "C:\My Docuements\MySource.mdb"

> Set dbs = OpenDatabase(strSourceDatabase)

> strDestinationDatabase = "C:\My Documents\MyDestination.mdb"
> 'DoCmd.TransferDatabase [transfertype], databasetype, databasename [,
> objecttype], source, destination [, structureonly] [, saveloginid]
> DoCmd.TransferDatabase  acExport, "Microsoft Access"
> ,strDestinationDatabase, acTable,  "SourceTableName",
> Try opening the database object of the database that has the actual
> table. Then  use the  DoCmd.TransferDatabase.

> Private Sub Command0_Click()
> Dim dbs As DATABASE
> Dim strSourceDatabase As String
> Dim strDestinationDatabase As String

> strSourceDatabase = "C:\My Docuements\MySource.mdb"

> Set dbs = OpenDatabase(strSourceDatabase)

> strDestinationDatabase = "C:\My Documents\MyDestination.mdb"

> DoCmd.TransferDatabase  acExport, "Microsoft Access"
> ,strDestinationDatabase, acTable,  "SourceTableName",
> "DestinationTableName",-1

> dbs.Close

> End Sub

Sorry I forgot to indicate to export the tableDef and not the data see
corrected code

Hope that helps

Lance



Tue, 13 Feb 2001 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Copying table definition to create new table

2. MS Access DB - Copy Table A from Database A to Table A Database B

3. Copying the structure of a table to a new table

4. Copying the structure of one Access table to a new Access table

5. Help: copying database data into new database

6. Copying records from jet database to a new jet database

7. Copying the contents of a table in one database to another database

8. Copying the contents of a table in one database to another database

9. VB4 linking tables results in read-only table definition

10. Copy Recordset to New Access Table

11. Create New Document and Copy Table from Existing Document

12. New to Word references: Newbie question on copying paragraphs in table to combobox

 

 
Powered by phpBB® Forum Software