
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