Two problems with copying tables from one database to another 
Author Message
 Two problems with copying tables from one database to another

I currently have a database that is split into front and back ends.  I am
trying to create a new database DbExport which has all the objects in one
file.  So far my procedure copies the front end to a new file.  Next I want
to delete all of the linked tables and then copy the actual tables into the
new database.  I am having two problems.

First I cant seem to delete the linked tables in DBExport from within
another database.   Does anyone know how to do this?  Ive tried
dbExport.Execute "DROP TABLE tablename;"   And Ive also looked at
docmd.deleteobject, but that only seems to work in the current database.

Second, Im not sure how to copy tables from one database to another, from a
third database.  The Docmd.TransferDatabase only applies to the current
database.  Any help on this is greatly apprecieated.

Thanks
Jim



Fri, 25 Jan 2002 03:00:00 GMT  
 Two problems with copying tables from one database to another
Hi Jim,

  You can try getting to it via OpenDatabase or Automation.

Sub sDeleteTable()
Dim dbRemote As Database
  Set dbRemote = DBEngine.OpenDatabase(CurrentDb.Name)
  dbRemote.TableDefs.Delete "sadf"
  dbRemote.Close
  Set dbRemote = Nothing
End Sub

or TransferDatabase

'******** Code Start ********
Sub sMakeCopiesInLocal()
Dim objAccess As Access.Application
Dim tdfRemote As TableDef
Dim dbRemote As Database
  Set objAccess = New Access.Application
  With objAccess
    .OpenCurrentDatabase CurrentDb.Name
    Set dbRemote = .CurrentDb
    For Each tdfRemote In dbRemote.TableDefs
      If (tdfRemote.Attributes And dbSystemObject) = False Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", _
CurrentDb.Name, acTable, tdfRemote.Name, _
          tdfRemote.Name & "xx", False
      End If
    Next
  End With
  Set tdfRemote = Nothing
  Set dbRemote = Nothing
  objAccess.Quit
  Set objAccess = Nothing
End Sub
'******** Code End ********

  -- Dev


Quote:
> I currently have a database that is split into front and back ends.  I am
> trying to create a new database "DbExport" which has all the objects in
one
> file.  So far my procedure copies the front end to a new file.  Next I
want
> to delete all of the linked tables and then copy the actual tables into
the
> new database.  I am having two problems.

> First I can't seem to delete the linked tables in DBExport from within
> another database.   Does anyone know how to do this?  I've tried
> dbExport.Execute "DROP TABLE tablename;"   And I've also looked at
> docmd.deleteobject, but that only seems to work in the current database.

> Second, I'm not sure how to copy tables from one database to another, from
a
> third database.  The Docmd.TransferDatabase only applies to the current
> database.  Any help on this is greatly apprecieated.

> Thanks
> Jim



Fri, 25 Jan 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

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

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

3. DAO copy tables between two Access databases

4. Copying tables from one database to another

5. To Copy table from one Access database to other

6. Two tables, one form , BIG problems

7. Copy a table from one Database (AS400 or SQL server) to another (MDB)

8. Copy entire table from one database to another

9. Copying Tables from one Database to another

10. copying tables from one database to anaother

11. ADO Copy Tables from One Database to Another (Access 97)

12. Comparing two tables and then updating one table.

 

 
Powered by phpBB® Forum Software