Copy all objects to another db

[Arno R]

Hi all,
I have a situation where I (have to) develop a database together with some other person.
Let's say we have develop1.mdb and develop2.mdb
We need to work on different 'pieces' of the app.
For testing purposes I need to 'merge' these two together to another database "Final.mdb"
Happily there will be no problem with object-names. (I use a naming convention, other developer does not)

From develop2.mdb I have code to
-- copy develop1.mdb to final.mdb (I delete the previous version first)
-- copy all objects from develop2.mdb to the new created develop.mdb

All works fine but is is slow... (> 2 minutes to copy about 200 objects)
To copy all object I use code like the following:
'This is code to copy the tables only, I do similar for forms, reports, query's and modules

Set db = CurrentDb
strSql = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~')"
strSql = strSql & " AND Left$([Name],4)<>'Msys'"
strSql = strSql & " AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name"
Set rst = db.OpenRecordset(strSql)
Do Until rst.EOF
'Debug.Print rst!Name
DoCmd.CopyObject strDestmdb, rst!Name, acTable, rst!Name
rst.MoveNext
Loop

Any ideas to improve this are welcome.

Arno R

[Danny J. Lesandrini]

You might check out a tool at my site called CopyWiz. It's free, and
doesn't really speak to your situation exactly, but the code behind
the tool might be usefu.
http://amazecreations.com/datafast/
go to downloads and look for Copy Wiz
--

Danny J. Lesandrini
dl*********@hotmail.com


"Arno R" <ar***********@tiscali.nl> wrote ...
Hi all,
I have a situation where I (have to) develop a database together with some other person.
Let's say we have develop1.mdb and develop2.mdb
We need to work on different 'pieces' of the app.
For testing purposes I need to 'merge' these two together to another database "Final.mdb"
Happily there will be no problem with object-names. (I use a naming convention, other developer does not)

From develop2.mdb I have code to
-- copy develop1.mdb to final.mdb (I delete the previous version first)
-- copy all objects from develop2.mdb to the new created develop.mdb

All works fine but is is slow... (> 2 minutes to copy about 200 objects)
To copy all object I use code like the following:
'This is code to copy the tables only, I do similar for forms, reports, query's and modules

Set db = CurrentDb
strSql = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~')"
strSql = strSql & " AND Left$([Name],4)<>'Msys'"
strSql = strSql & " AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name"
Set rst = db.OpenRecordset(strSql)
Do Until rst.EOF
'Debug.Print rst!Name
DoCmd.CopyObject strDestmdb, rst!Name, acTable, rst!Name
rst.MoveNext
Loop

Any ideas to improve this are welcome.

Arno R

[MacDermott]

I wonder if you couldn't do this faster "by hand" -
just use the wizard to import all objects from first one, then the other
database...
(you can use options to import relationships, toolbars, etc. without
writing code, too)

HTH

"Arno R" <ar***********@tiscali.nl> wrote in message
news:43********************@dreader2.news.tiscali. nl...
Hi all,
I have a situation where I (have to) develop a database together with some
other person.
Let's say we have develop1.mdb and develop2.mdb
We need to work on different 'pieces' of the app.
For testing purposes I need to 'merge' these two together to another
database "Final.mdb"
Happily there will be no problem with object-names. (I use a naming
convention, other developer does not)

From develop2.mdb I have code to
-- copy develop1.mdb to final.mdb (I delete the previous version first)
-- copy all objects from develop2.mdb to the new created develop.mdb

All works fine but is is slow... (> 2 minutes to copy about 200 objects)
To copy all object I use code like the following:
'This is code to copy the tables only, I do similar for forms, reports,
query's and modules

Set db = CurrentDb
strSql = "SELECT MSysObjects.Name FROM MsysObjects WHERE
(Left$([Name],1)<>'~')"
strSql = strSql & " AND Left$([Name],4)<>'Msys'"
strSql = strSql & " AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name"
Set rst = db.OpenRecordset(strSql)
Do Until rst.EOF
'Debug.Print rst!Name
DoCmd.CopyObject strDestmdb, rst!Name, acTable, rst!Name
rst.MoveNext
Loop

Any ideas to improve this are welcome.

Arno R

[Arno R]

"MacDermott" <ma********@nospam.com> schreef in bericht news:yD*******************@newsread2.news.atl.eart hlink.net...

I wonder if you couldn't do this faster "by hand" -
just use the wizard to import all objects from first one, then the other
database...
(you can use options to import relationships, toolbars, etc. without
writing code, too)

HTH

Thanks, but I would rather do this 'automated'
I might try the other way around.
I am getting the idea that importing works way faster than exporting.

Arno R

[Arno R]

"Danny J. Lesandrini" <dl*********@hotmail.com> schreef in bericht news:ec********************@comcast.com...

You might check out a tool at my site called CopyWiz. It's free, and
doesn't really speak to your situation exactly, but the code behind
the tool might be usefu.
http://amazecreations.com/datafast/
go to downloads and look for Copy Wiz
--

Danny J. Lesandrini
dl*********@hotmail.com

I looked at the CopyWiz.mdb.
Basically it works the same (also uses docmd.CopyObject) but it is also slow, even slower actually.
This CopyWiz-code uses 3 mdb's if I am right.
First imports object from A.mdb to CurrentDb, then exports.to B.mdb and deletes the object again from CurrentDb.
It works perfect and it certainly has it's advantages, but in my case it is not the solution I am seeking.

I might try the other way around though. It may be the case that importing objects is faster than exporting.
I will also test SaveAsText - LoadFromText routines.

Your code might be useful in 'controlling' multiple open mdb's.
Thanks,

Arno R