docmd.copyobject

[allen, gary]

How do I copy all of the forms from one access database file to another.

I am already copying the tables using the following
Dim t As TableDef
For Each t In CurrentDb.TableDefs
If t.Attributes = 0 Then 'zero = user table
DoCmd.CopyObject sbutname, "", acTable, t.name
End If
Next

I am having problems adapting it to handle forms, reports and queries.

 

[Dirk Goldgar]

in message

How do I copy all of the forms from one access database file to another.

I am already copying the tables using the following
Dim t As TableDef
For Each t In CurrentDb.TableDefs
If t.Attributes = 0 Then 'zero = user table
DoCmd.CopyObject sbutname, "", acTable, t.name
End If
Next

I am having problems adapting it to handle forms, reports and queries.

Dim ao As AccessObject

' Copy Forms
For Each ao In CurrentProject.AllForms
DoCmd.CopyObject sbutname, , acForm, ao.Name
Next ao

' Copy Reports
For Each ao In CurrentProject.AllReports
DoCmd.CopyObject sbutname, , acReport, ao.Name
Next ao

' Copy Queries
For Each ao In CurrentData.AllQueries
If Left(ao.Name, 1) <> "~" Then
DoCmd.CopyObject sbutname, , acQuery, ao.Name
End If
Next ao

 

[Ryan]

Hi Dirk,

how do i adapt this to copy the objects from a outside database into the
current one...including modules??

Ryan

 

[Dirk Goldgar]

Hi Dirk,

how do i adapt this to copy the objects from a outside database into the
current one...including modules??

You'll use DoCmd.TransferDatabase instead of DoCmd.CopyObject to do the
actual copying, but you'll have to use a different method to enumerate the
objects in the source database. Do you want to copy all the objects in the
other database? This should work, though it's air code and may need some
tweaking:

'----- start of code -----
Dim strSourceDB As String

strSourceDB = "C:\Your\Path\To\YourSourceDB.mdb"

Dim dbSource As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim cnt As DAO.Container
Dim doc As DAO.Document

Set dbSource = DBEngine.OpenDatabase(strSourceDB)

' Import tables.
For Each tdf In dbSource.TableDefs
If Left$(tdf.Name, 4) <> "MSys" Then
DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acTable, _
tdf.Name, _
tdf.Name
End If
Next tdf

' Import queries.
For Each qdf In dbSource.QueryDefs
If Left$(qdf.Name, 1) <> "~" Then
DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acQuery, _
qdf.Name, _
qdf.Name
End If
Next qdf

' Import forms.
Set cnt = dbSource.Containers("Forms")
For Each doc In cnt.Documents
DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acForm, _
doc.Name, _
doc.Name
Next doc
Set cnt = Nothing

' Import reports.
Set cnt = dbSource.Containers("Reports")
For Each doc In cnt.Documents
DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acReport, _
doc.Name, _
doc.Name
Next doc
Set cnt = Nothing

' Import modules.
Set cnt = dbSource.Containers("Modules")
For Each doc In cnt.Documents
DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acModule, _
doc.Name, _
doc.Name
Next doc
Set cnt = Nothing

' Import macros.
Set cnt = dbSource.Containers("Scripts")
For Each doc In cnt.Documents
DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acMacro, _
doc.Name, _
doc.Name
Next doc
Set cnt = Nothing

' NOTE: This code does not import relationships. The following
' Microsoft KB article gives code for that, which can easily be
' adapted to fit in this spot:
'
' http://support.microsoft.com/kb/128157


' Clean up after all the imports.
dbSource.Close
Set dbSource = Nothing

'----- end of code -----

 

[Ryan]

Hi Dirk,

how do i adapt this to overwrite existing queries, forms, reports, modules
if they already exist...to use per say as a update tool for the front end of
a database.

Ryan

 

[Dirk Goldgar]

Hi Dirk,

how do i adapt this to overwrite existing queries, forms, reports, modules
if they already exist...to use per say as a update tool for the front end
of
a database.

[snip]

You would have to check if the object already exists in the current
database, and delete it if it does. Here's a link to one approach to
finding out if a particular database object exists:

http://allenbrowne.com/ser-73.html#ObjectExists

That version requires you to pass the object name (as a string) and an
object reference to the collection that may contain the object. When
calling the function, you need to know which collection to pass. Note that
there are lots of handy functions on that page.

To delete the object, you could use teh DoCmd.DeleteObject method.