enumerating objects - repost

[Mark]

(original posts: "DoCmd.DeleteObject ...Where ... -- Tue, 6 Apr
and "enumerating objects" -- Wed, 7 Apr)

How do I refer to, and compare objects in one database with those in
another?
For example:

FOR EACH object in db1 with the same name and object type IN db2 THEN
'do something here (ultimately, I want to delete the matching objects in
db1).

I've spent hours trying to figure this out through reading the help files,
etc. and the best I can do is simply to get the names of the objects in each
collection (forms, tables/queries, reports, scripts, and modules). I still
don't know how to compare these objects to those in another database and to
then delete only those objects in the second database with names (and
object-types) matching those in the first database. Also, evidently there is
not a way to reference all the objects (excluding system objects) at one
time -- correct?

Dim dbs As Database, ctr As Container, doc As Document
Set dbs = CurrentDb

Set ctr = dbs.Containers!Tables
For Each doc In ctr.Documents
If Left(doc.Name, 4) <> "Msys" Then
Debug.Print doc.Name
End If
Next doc

End Sub

Can someone help please?
Thank you.
Mark

 

[TC]

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef, tdOther as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the other one"
endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object in this
database, whcih is also in the other database, is actually >identical<.
Mathing their >names<, is clearly not nearly enough.

For a table, you'd need to check every field; all the properties of every
field; the table description; every index & their properties; and so on, an
on, and on. For forms, it would be even worse: you'd have to iterate through
an unknown number of levels of subforms!, and so on.

If I personally wanted to match two databases, this is what I would do.
Write code to dump all properties of each object into a flat file - a kind
of homegrown SaveAsText approach. Then run that code on the two database,
and use a word-processor or file comparison program to compare the two
flat-file outputs! This approach gives you an easy "heads up" of the
differences between the two databases. But it would not give you

programattic< visibility of those differences.

Unless, maybe, you structured the flat file output in such a way that you
could then compare two versions >using VBA< - then you >would< get
programattic visibility of what objects were different, & how they wetre
different.

But this would all be a big ask, unless you were >very< familiar with VBA,
and all the relevant object models. IOW there is no easy way to do what you
want. Unless there is an off-the-shelf product from FMS Inc or somesuch?

HTH,
TC

 

[Mark]

TC,

1st: Thank you.

2nd: I'm already assuming that the objects with matching names in dbOther
are NOT identical in terms of structure, fields, etc. That's the whole
point -- to eventually replace those with the newer objects in dbThis. But
first, I need to delete those from dbOther before I can export from dbThis.
I'm using Access97 and will get an error if I try to export/replace an
object with the same name (see Q160875) -- especially if it's a Form. But, I
don't wish to delete ALL the objects in dbOther -- only those that match the
names of those in dbThis.

3rd: I'm having difficulty understanding what's happening in your code. I
think the code is saying: "For each tabledef in this database ("For Each
tdThis In dbThis"), set the tabledef in dbOther to the same name as the
tabledef in dbThis ("set tdOther = dbOther.tabledefs (tdThis.name)"). But, I
don't understand where the matching occurs. Is that the purpose of the error
statement -- that an error will occur for each table in dbOther which does
NOT match that in dbThis -- an error because supposedly there's already a
table in dbOther by that name -- and the error will result in the procedure
skipping over each table in dbOther until the procedure gets to the table of
the same name? And, at this point, instead of "debug.print" I could delete
that table in dbOther. If this is what is happening in the procedure, then
there might be a problem if there IS'NT a table in dbOther by that name --
it would simply rename the first table it came to. Right?

4th: Also, I get an error in the procedure step: "For Each tdThis In
dbThis":
Run-time error '3251 -- Operation is not supported for this type of object.

Thanks again, TC, for your help.
Mark

 

[TC]

TC,

1st: Thank you.

No probs

2nd: I'm already assuming that the objects with matching names in dbOther
are NOT identical in terms of structure, fields, etc. That's the whole
point -- to eventually replace those with the newer objects in dbThis. But
first, I need to delete those from dbOther before I can export from dbThis.
I'm using Access97 and will get an error if I try to export/replace an
object with the same name (see Q160875) -- especially if it's a Form. But, I
don't wish to delete ALL the objects in dbOther -- only those that match the
names of those in dbThis.

Understood. I missed that point. It makes it way simpler!

3rd: I'm having difficulty understanding what's happening in your code. I
think the code is saying: "For each tabledef in this database ("For Each
tdThis In dbThis"),

Yes, except that I should have said dbThis.TABLEDEFS. Oops!

set the tabledef in dbOther to the same name as the
tabledef in dbThis ("set tdOther = dbOther.tabledefs (tdThis.name)").

Yes. In other words, "see if there is a table with the same name in the
other database". I do that by trying to establish a reference (tdOther) to
that table in the other database. If that table does not exist, an error
occurs (Err.Number <> 0). If that table >does< exist, no error occurs, and
tdOther now references that table in the other database. In my original
code, I did not do anything with tdOther. I just wanted to see if I could

get< tdOther. This serves to tell you whether a table of that name does or

does not exist in the other database.

But, I
don't understand where the matching occurs. Is that the purpose of the error
statement -- that an error will occur for each table in dbOther which does
NOT match that in dbThis -- an error because supposedly there's already a
table in dbOther by that name -- and the error will result in the procedure
skipping over each table in dbOther until the procedure gets to the table of
the same name? And, at this point, instead of "debug.print" I could delete
that table in dbOther. If this is what is happening in the procedure, then
there might be a problem if there IS'NT a table in dbOther by that name --
it would simply rename the first table it came to. Right?

See better code below (now that I understand your need properly).

4th: Also, I get an error in the procedure step: "For Each tdThis In
dbThis":
Run-time error '3251 -- Operation is not supported for this type of

object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The next code
should delete, from the "other" database, every table that has the same name
as a table in the >current< database. I've put the changes in UPPER CASE, to
make them stand out. Instead of checking to see whether the table does or
does not exist in the other database, I just delete it unconditionally, &
trap the error if the delete fails (ie. it >is not< in the other database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef ' TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for queries,
using Querydef instead of Tabledef. If you get the queries working,
ask again, & I'll show you how to do Forms, Macros & Modules. (You'll need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!

 

[Mark]

TC,
Thank you so much -- as much for the lesson, as for the solution. I'll work
on this this weekend.
Mark

TC,

1st: Thank you.

No probs

2nd: I'm already assuming that the objects with matching names in dbOther
are NOT identical in terms of structure, fields, etc. That's the whole
point -- to eventually replace those with the newer objects in dbThis. But
first, I need to delete those from dbOther before I can export from dbThis.
I'm using Access97 and will get an error if I try to export/replace an
object with the same name (see Q160875) -- especially if it's a Form.

But,

I
don't wish to delete ALL the objects in dbOther -- only those that match the
names of those in dbThis.

Understood. I missed that point. It makes it way simpler!

3rd: I'm having difficulty understanding what's happening in your code. I
think the code is saying: "For each tabledef in this database ("For Each
tdThis In dbThis"),

Yes, except that I should have said dbThis.TABLEDEFS. Oops!

set the tabledef in dbOther to the same name as the
tabledef in dbThis ("set tdOther = dbOther.tabledefs (tdThis.name)").

Yes. In other words, "see if there is a table with the same name in the
other database". I do that by trying to establish a reference (tdOther) to
that table in the other database. If that table does not exist, an error
occurs (Err.Number <> 0). If that table >does< exist, no error occurs, and
tdOther now references that table in the other database. In my original
code, I did not do anything with tdOther. I just wanted to see if I could

get< tdOther. This serves to tell you whether a table of that name does

or
does not exist in the other database.

But, I
don't understand where the matching occurs. Is that the purpose of the error
statement -- that an error will occur for each table in dbOther which does
NOT match that in dbThis -- an error because supposedly there's already a
table in dbOther by that name -- and the error will result in the procedure
skipping over each table in dbOther until the procedure gets to the

table

of
the same name? And, at this point, instead of "debug.print" I could delete
that table in dbOther. If this is what is happening in the procedure, then
there might be a problem if there IS'NT a table in dbOther by that name --
it would simply rename the first table it came to. Right?

See better code below (now that I understand your need properly).

4th: Also, I get an error in the procedure step: "For Each tdThis In
dbThis":
Run-time error '3251 -- Operation is not supported for this type of

object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The next code
should delete, from the "other" database, every table that has the same name
as a table in the >current< database. I've put the changes in UPPER CASE, to
make them stand out. Instead of checking to see whether the table does or
does not exist in the other database, I just delete it unconditionally, &
trap the error if the delete fails (ie. it >is not< in the other database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef ' TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for queries,
using Querydef instead of Tabledef. If you get the queries working,
ask again, & I'll show you how to do Forms, Macros & Modules. (You'll need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!

Thanks again, TC, for your help.
Mark

tdOther
as on,
an what
you

objects

in

 

[TC]

Hope it works for you.

Cheers,
TC

TC,
Thank you so much -- as much for the lesson, as for the solution. I'll work
on this this weekend.
Mark

No probs

But, match
the

Understood. I missed that point. It makes it way simpler!

code.

I

Yes, except that I should have said dbThis.TABLEDEFS. Oops!



Yes. In other words, "see if there is a table with the same name in the
other database". I do that by trying to establish a reference (tdOther) to
that table in the other database. If that table does not exist, an error
occurs (Err.Number <> 0). If that table >does< exist, no error occurs, and
tdOther now references that table in the other database. In my original
code, I did not do anything with tdOther. I just wanted to see if I could or
does not exist in the other database.

already

a table

See better code below (now that I understand your need properly).


object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The next code
should delete, from the "other" database, every table that has the same name
as a table in the >current< database. I've put the changes in UPPER

CASE,

to
make them stand out. Instead of checking to see whether the table does or
does not exist in the other database, I just delete it unconditionally, &
trap the error if the delete fails (ie. it >is not< in the other database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef ' TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for queries,
using Querydef instead of Tabledef. If you get the queries working,
ask again, & I'll show you how to do Forms, Macros & Modules. (You'll need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!

Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef, tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the other one"
endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object in th is
database, whcih is also in the other database, is actually identical<.
Mathing their >names<, is clearly not nearly enough.

For a table, you'd need to check every field; all the properties of every
field; the table description; every index & their properties; and so on,
an
on, and on. For forms, it would be even worse: you'd have to iterate
through
an unknown number of levels of subforms!, and so on.

If I personally wanted to match two databases, this is what I would do.
Write code to dump all properties of each object into a flat file -

a
kind

of homegrown SaveAsText approach. Then run that code on the two database,
and use a word-processor or file comparison program to compare the two
flat-file outputs! This approach gives you an easy "heads up" of the
differences between the two databases. But it would not give you
programattic< visibility of those differences.

Unless, maybe, you structured the flat file output in such a way

that
you

could then compare two versions >using VBA< - then you >would< get
programattic visibility of what objects were different, & how they wetre
different.

But this would all be a big ask, unless you were >very< familiar

with
VBA,

and all the relevant object models. IOW there is no easy way to do what
you
want. Unless there is an off-the-shelf product from FMS Inc or somesuch?

HTH,
TC


(original posts: "DoCmd.DeleteObject ...Where ... -- Tue, 6 Apr
and "enumerating objects" -- Wed, 7 Apr)

How do I refer to, and compare objects in one database with those in
another?
For example:

FOR EACH object in db1 with the same name and object type IN db2 THEN
'do something here (ultimately, I want to delete the matching

objects

in
db1).

I've spent hours trying to figure this out through reading the help
files,
etc. and the best I can do is simply to get the names of the

objects
in

each
collection (forms, tables/queries, reports, scripts, and modules). I
still
don't know how to compare these objects to those in another

database
and

to
then delete only those objects in the second database with names (and
object-types) matching those in the first database. Also, evidently
there
is
not a way to reference all the objects (excluding system objects)

at
one

time -- correct?

Dim dbs As Database, ctr As Container, doc As Document
Set dbs = CurrentDb

Set ctr = dbs.Containers!Tables
For Each doc In ctr.Documents
If Left(doc.Name, 4) <> "Msys" Then
Debug.Print doc.Name
End If
Next doc

End Sub

Can someone help please?
Thank you.
Mark

 

[Mark]

Hi TC,

This works so far. I believe I just need the delete command for the forms,
reports, macros, and modules. I had to alter one of your "block...if" lines
(I don't remember which), and in the process of trying to figure that out, I
just eliminated the error-handling code.

If you could show me the DeleteObject statement for one of these other
objects, I would sure appreciate it. Thanks again for the assistance you've
already provided.

And, at the risk of overstaying my welcome, I have an additional question
regarding compiled and uncompiled states. If exporting some of these objects
results in an uncompiled state in the target database, how much of a problem
is that and what should I do about it?

code thus far:

Private Sub Command0_Click()
Dim dbThis As Database, dbOther As Database
Dim tblThis As TableDef
Dim qryThis As QueryDef
Dim frmThis As Document
Dim rptThis As Document
Dim modThis As Document
Dim mcrThis As Document

Set dbThis = CurrentDb()
Set dbOther = DBEngine.OpenDatabase("C:\Access97\Test.mdb")

'TABLES
For Each tblThis In dbThis.TableDefs
If Not (tblThis.Name Like "MSYS*") Then
' NOT A SYSTEM TABLE.
On Error Resume Next
dbOther.TableDefs.Delete tblThis.Name

End If
Next

'QUERIES
For Each qryThis In dbThis.QueryDefs
dbOther.QueryDefs.Delete qryThis.Name
Next

'FORMS
For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

Hope it works for you.

Cheers,
TC

TC,
Thank you so much -- as much for the lesson, as for the solution. I'll work
on this this weekend.
Mark

dbThis.
But Form.
But, code.

(tdOther)

to does
or which
does already procedure,
then same
name CASE,

unconditionally,

&

trap the error if the delete fails (ie. it >is not< in the other database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef ' TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for queries,
using Querydef instead of Tabledef. If you get the queries working,
ask again, & I'll show you how to do Forms, Macros & Modules. (You'll need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!


Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef, tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the other one"
endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object in

th

is so
on, would
do.

file -
a those

in modules).
I

objects)

at

 

[TC]

Hi Mark

I'll respond to this tomorrow (Tuesday 13 April, where I live).

Cheers,
TC

Hi TC,

This works so far. I believe I just need the delete command for the forms,
reports, macros, and modules. I had to alter one of your "block...if" lines
(I don't remember which), and in the process of trying to figure that out, I
just eliminated the error-handling code.

If you could show me the DeleteObject statement for one of these other
objects, I would sure appreciate it. Thanks again for the assistance you've
already provided.

And, at the risk of overstaying my welcome, I have an additional question
regarding compiled and uncompiled states. If exporting some of these objects
results in an uncompiled state in the target database, how much of a problem
is that and what should I do about it?

code thus far:

Private Sub Command0_Click()
Dim dbThis As Database, dbOther As Database
Dim tblThis As TableDef
Dim qryThis As QueryDef
Dim frmThis As Document
Dim rptThis As Document
Dim modThis As Document
Dim mcrThis As Document

Set dbThis = CurrentDb()
Set dbOther = DBEngine.OpenDatabase("C:\Access97\Test.mdb")

'TABLES
For Each tblThis In dbThis.TableDefs
If Not (tblThis.Name Like "MSYS*") Then
' NOT A SYSTEM TABLE.
On Error Resume Next
dbOther.TableDefs.Delete tblThis.Name

End If
Next

'QUERIES
For Each qryThis In dbThis.QueryDefs
dbOther.QueryDefs.Delete qryThis.Name
Next

'FORMS
For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

Hope it works for you.

Cheers,
TC

export/replace

an

object with the same name (see Q160875) -- especially if it's a Form.
But,
I
don't wish to delete ALL the objects in dbOther -- only those that match
the
names of those in dbThis.

Understood. I missed that point. It makes it way simpler!


3rd: I'm having difficulty understanding what's happening in your code.
I
think the code is saying: "For each tabledef in this database

("For
Each

tdThis In dbThis"),

Yes, except that I should have said dbThis.TABLEDEFS. Oops!


set the tabledef in dbOther to the same name as the
tabledef in dbThis ("set tdOther = dbOther.tabledefs (tdThis.name)").

Yes. In other words, "see if there is a table with the same name in the
other database". I do that by trying to establish a reference

(tdOther)

to
that table in the other database. If that table does not exist, an error
occurs (Err.Number <> 0). If that table >does< exist, no error

occurs,
and

tdOther now references that table in the other database. In my original
code, I did not do anything with tdOther. I just wanted to see if I could
get< tdOther. This serves to tell you whether a table of that name does
or
does not exist in the other database.


But, I
don't understand where the matching occurs. Is that the purpose of the
error
statement -- that an error will occur for each table in dbOther which
does
NOT match that in dbThis -- an error because supposedly there's already
a
table in dbOther by that name -- and the error will result in the
procedure
skipping over each table in dbOther until the procedure gets to the
table
of
the same name? And, at this point, instead of "debug.print" I could
delete
that table in dbOther. If this is what is happening in the procedure,
then
there might be a problem if there IS'NT a table in dbOther by that
name --
it would simply rename the first table it came to. Right?

See better code below (now that I understand your need properly).


4th: Also, I get an error in the procedure step: "For Each tdThis In
dbThis":
Run-time error '3251 -- Operation is not supported for this type of
object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The next code
should delete, from the "other" database, every table that has the same
name
as a table in the >current< database. I've put the changes in UPPER CASE,
to
make them stand out. Instead of checking to see whether the table

does
or

does not exist in the other database, I just delete it

unconditionally,

&
trap the error if the delete fails (ie. it >is not< in the other
database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef '
TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for
queries,
using Querydef instead of Tabledef. If you get the queries working,
ask again, & I'll show you how to do Forms, Macros & Modules.

(You'll
need

to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!


Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef,
tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the

other
one"

endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object

in

th

and

so file -
a the
two those objects)

 

[TC]

Hi Mark

Sorry for the delay, I've been busy for the last few days.

Comments interspersed.

Cheers,
TC

Hi TC,

This works so far. I believe I just need the delete command for the forms,
reports, macros, and modules. I had to alter one of your "block...if" lines
(I don't remember which), and in the process of trying to figure that out, I
just eliminated the error-handling code.

If you could show me the DeleteObject statement for one of these other
objects, I would sure appreciate it. Thanks again for the assistance you've
already provided.

And, at the risk of overstaying my welcome, I have an additional question
regarding compiled and uncompiled states. If exporting some of these objects
results in an uncompiled state in the target database, how much of a problem
is that and what should I do about it?

*** It's no problem at all. When you export a form (for example), you can
think of it as only exporting a source-code definition of the form. Before
that form can actually >run<, the db (to which it was exported) must compile
it into a runnable state. The db to which it was exported, becomes
uncompiled until that occurs. All compilation actions occur automatically,
as & when required. You do not need to worry about this at all.

code thus far:

Private Sub Command0_Click()
Dim dbThis As Database, dbOther As Database
Dim tblThis As TableDef
Dim qryThis As QueryDef
Dim frmThis As Document
Dim rptThis As Document
Dim modThis As Document
Dim mcrThis As Document

Set dbThis = CurrentDb()
Set dbOther = DBEngine.OpenDatabase("C:\Access97\Test.mdb")

'TABLES
For Each tblThis In dbThis.TableDefs
If Not (tblThis.Name Like "MSYS*") Then
' NOT A SYSTEM TABLE.
On Error Resume Next
dbOther.TableDefs.Delete tblThis.Name

*** You should re-insert the ON ERROR GOTO 0 line that you deleted here.
Otherwise, the ON ERROR RESUME NEXT will apply to all following statements
in the whole module. That is definitely not what you want! You want it to
apply just to the .Delete statement. So you need ON ERROR GOTO 0 after that
statement, to cancel the ON ERROR RESUME NEXT.

End If
Next

'QUERIES
For Each qryThis In dbThis.QueryDefs

*** Again, if you do not use the ON ERROR statements here, you are assuming
that there >is< a query of that name in the other database. Without the ON
ERROR statements, if thgere is >not< a query of that name, the .Delete
statement will fail. It is pointless to take that risk. Re-add the two ON
ERROR statements!

dbOther.QueryDefs.Delete qryThis.Name
Next

'FORMS

*** Be careful with the following loop variables (frmThis, rptThis, mcrThis
and modThis). You need to define them all 'As Document' - not 'As Form', 'As
report' and so on. A Container object contains Document objects - not Form,
Report, Script (macro) or Module objects as such.

As for the delete commands, I feel uncomfortable about the following
suggestion, for some reason, but I don't have Access on this PC, so you'll
just have to give it a blast & see if it works. Try these for the frmThis
loop. The changes for the other loops should be self-evident.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Sorry to be vague on this, but I just can't remember with any certainty.

Cheers,
TC

For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

Hope it works for you.

Cheers,
TC

export/replace

an

object with the same name (see Q160875) -- especially if it's a Form.
But,
I
don't wish to delete ALL the objects in dbOther -- only those that match
the
names of those in dbThis.

Understood. I missed that point. It makes it way simpler!


3rd: I'm having difficulty understanding what's happening in your code.
I
think the code is saying: "For each tabledef in this database

("For
Each

tdThis In dbThis"),

Yes, except that I should have said dbThis.TABLEDEFS. Oops!


set the tabledef in dbOther to the same name as the
tabledef in dbThis ("set tdOther = dbOther.tabledefs (tdThis.name)").

Yes. In other words, "see if there is a table with the same name in the
other database". I do that by trying to establish a reference

(tdOther)

to
that table in the other database. If that table does not exist, an error
occurs (Err.Number <> 0). If that table >does< exist, no error

occurs,
and

tdOther now references that table in the other database. In my original
code, I did not do anything with tdOther. I just wanted to see if I could
get< tdOther. This serves to tell you whether a table of that name does
or
does not exist in the other database.


But, I
don't understand where the matching occurs. Is that the purpose of the
error
statement -- that an error will occur for each table in dbOther which
does
NOT match that in dbThis -- an error because supposedly there's already
a
table in dbOther by that name -- and the error will result in the
procedure
skipping over each table in dbOther until the procedure gets to the
table
of
the same name? And, at this point, instead of "debug.print" I could
delete
that table in dbOther. If this is what is happening in the procedure,
then
there might be a problem if there IS'NT a table in dbOther by that
name --
it would simply rename the first table it came to. Right?

See better code below (now that I understand your need properly).


4th: Also, I get an error in the procedure step: "For Each tdThis In
dbThis":
Run-time error '3251 -- Operation is not supported for this type of
object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The next code
should delete, from the "other" database, every table that has the same
name
as a table in the >current< database. I've put the changes in UPPER CASE,
to
make them stand out. Instead of checking to see whether the table

does
or

does not exist in the other database, I just delete it

unconditionally,

&
trap the error if the delete fails (ie. it >is not< in the other
database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef '
TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for
queries,
using Querydef instead of Tabledef. If you get the queries working,
ask again, & I'll show you how to do Forms, Macros & Modules.

(You'll
need

to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!


Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef,
tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the

other
one"

endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object

in

th

and

so file -
a the
two those objects)

 

[Mark]

Thanks TC,
I'll try these and post back.
Mark

Hi Mark

Sorry for the delay, I've been busy for the last few days.

Comments interspersed.

Cheers,
TC

Hi TC,

This works so far. I believe I just need the delete command for the forms,
reports, macros, and modules. I had to alter one of your "block...if" lines
(I don't remember which), and in the process of trying to figure that

out,

I
just eliminated the error-handling code.

If you could show me the DeleteObject statement for one of these other
objects, I would sure appreciate it. Thanks again for the assistance you've
already provided.

And, at the risk of overstaying my welcome, I have an additional question
regarding compiled and uncompiled states. If exporting some of these objects
results in an uncompiled state in the target database, how much of a problem
is that and what should I do about it?

*** It's no problem at all. When you export a form (for example), you can
think of it as only exporting a source-code definition of the form. Before
that form can actually >run<, the db (to which it was exported) must compile
it into a runnable state. The db to which it was exported, becomes
uncompiled until that occurs. All compilation actions occur automatically,
as & when required. You do not need to worry about this at all.

code thus far:

Private Sub Command0_Click()
Dim dbThis As Database, dbOther As Database
Dim tblThis As TableDef
Dim qryThis As QueryDef
Dim frmThis As Document
Dim rptThis As Document
Dim modThis As Document
Dim mcrThis As Document

Set dbThis = CurrentDb()
Set dbOther = DBEngine.OpenDatabase("C:\Access97\Test.mdb")

'TABLES
For Each tblThis In dbThis.TableDefs
If Not (tblThis.Name Like "MSYS*") Then
' NOT A SYSTEM TABLE.
On Error Resume Next
dbOther.TableDefs.Delete tblThis.Name

*** You should re-insert the ON ERROR GOTO 0 line that you deleted here.
Otherwise, the ON ERROR RESUME NEXT will apply to all following statements
in the whole module. That is definitely not what you want! You want it to
apply just to the .Delete statement. So you need ON ERROR GOTO 0 after that
statement, to cancel the ON ERROR RESUME NEXT.

End If
Next

'QUERIES
For Each qryThis In dbThis.QueryDefs

*** Again, if you do not use the ON ERROR statements here, you are assuming
that there >is< a query of that name in the other database. Without the ON
ERROR statements, if thgere is >not< a query of that name, the .Delete
statement will fail. It is pointless to take that risk. Re-add the two ON
ERROR statements!

dbOther.QueryDefs.Delete qryThis.Name
Next

'FORMS

*** Be careful with the following loop variables (frmThis, rptThis, mcrThis
and modThis). You need to define them all 'As Document' - not 'As Form', 'As
report' and so on. A Container object contains Document objects - not Form,
Report, Script (macro) or Module objects as such.

As for the delete commands, I feel uncomfortable about the following
suggestion, for some reason, but I don't have Access on this PC, so you'll
just have to give it a blast & see if it works. Try these for the frmThis
loop. The changes for the other loops should be self-evident.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Sorry to be vague on this, but I just can't remember with any certainty.

Cheers,
TC

For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

export/replace in
the name
does of
the

tdThis

In

dbThis":
Run-time error '3251 -- Operation is not supported for this type of
object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The

next
code

should delete, from the "other" database, every table that has the same
name
as a table in the >current< database. I've put the changes in UPPER
CASE,
to
make them stand out. Instead of checking to see whether the table does
or
does not exist in the other database, I just delete it unconditionally,
&
trap the error if the delete fails (ie. it >is not< in the other
database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef '
TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for
queries,
using Querydef instead of Tabledef. If you get the queries
working,
ask again, & I'll show you how to do Forms, Macros & Modules. (You'll
need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two
databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!


Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef,
tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the other
one"
endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object

in

th
is
database, whcih is also in the other database, is actually
identical<.
Mathing their >names<, is clearly not nearly enough.

For a table, you'd need to check every field; all the

properties
of

every
field; the table description; every index & their properties;

and

so
on,
an
on, and on. For forms, it would be even worse: you'd have to iterate
through
an unknown number of levels of subforms!, and so on.

If I personally wanted to match two databases, this is what I would
do.
Write code to dump all properties of each object into a flat file -
a
kind
of homegrown SaveAsText approach. Then run that code on the two
database,
and use a word-processor or file comparison program to compare the
two
flat-file outputs! This approach gives you an easy "heads up"

of
the

differences between the two databases. But it would not give you
programattic< visibility of those differences.

Unless, maybe, you structured the flat file output in such a way
that
you
could then compare two versions >using VBA< - then you >would< get
programattic visibility of what objects were different, & how they
wetre
different.

But this would all be a big ask, unless you were >very< familiar
with
VBA,
and all the relevant object models. IOW there is no easy way

to

do

 

[Mark]

Hi TC,

I tried each of these options for the Forms section and got the same error
message:
"Method of data member not found", with either "Remove" or "Delete"
highlighted in the debug window. I don't know if it's relevant, but
Help/Documents lists only "Refresh Method(DAO) under "Methods" -- which I
assume means that that is the only method associated with Documents.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Mark.

Hi Mark

Sorry for the delay, I've been busy for the last few days.

Comments interspersed.

Cheers,
TC


out,

*** It's no problem at all. When you export a form (for example), you can
think of it as only exporting a source-code definition of the form. Before
that form can actually >run<, the db (to which it was exported) must compile
it into a runnable state. The db to which it was exported, becomes
uncompiled until that occurs. All compilation actions occur automatically,
as & when required. You do not need to worry about this at all.



*** You should re-insert the ON ERROR GOTO 0 line that you deleted here.
Otherwise, the ON ERROR RESUME NEXT will apply to all following statements
in the whole module. That is definitely not what you want! You want it to
apply just to the .Delete statement. So you need ON ERROR GOTO 0 after that
statement, to cancel the ON ERROR RESUME NEXT.


*** Again, if you do not use the ON ERROR statements here, you are assuming
that there >is< a query of that name in the other database. Without the ON
ERROR statements, if thgere is >not< a query of that name, the .Delete
statement will fail. It is pointless to take that risk. Re-add the two ON
ERROR statements!


*** Be careful with the following loop variables (frmThis, rptThis, mcrThis
and modThis). You need to define them all 'As Document' - not 'As Form', 'As
report' and so on. A Container object contains Document objects - not Form,
Report, Script (macro) or Module objects as such.

As for the delete commands, I feel uncomfortable about the following
suggestion, for some reason, but I don't have Access on this PC, so you'll

just have to give it a blast & see if it works. Try these for the frmThis
loop. The changes for the other loops should be self-evident.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Sorry to be vague on this, but I just can't remember with any certainty.

Cheers,
TC

For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

export/replace in
the name
does of
the

tdThis

In

dbThis":
Run-time error '3251 -- Operation is not supported for this type of
object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The

next
code

should delete, from the "other" database, every table that has the same
name
as a table in the >current< database. I've put the changes in UPPER
CASE,
to
make them stand out. Instead of checking to see whether the table does
or
does not exist in the other database, I just delete it unconditionally,
&
trap the error if the delete fails (ie. it >is not< in the other
database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef '
TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for
queries,
using Querydef instead of Tabledef. If you get the queries
working,
ask again, & I'll show you how to do Forms, Macros & Modules. (You'll
need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you will
clearly want to be sure that you have backup copies of the two
databases,
just in case the code is wrong and deletes the wrong objects, or deletes
from the wrong database!


Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef,
tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the other
one"
endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object

in

th
is
database, whcih is also in the other database, is actually
identical<.
Mathing their >names<, is clearly not nearly enough.

For a table, you'd need to check every field; all the

properties
of

every
field; the table description; every index & their properties;

and

so
on,
an
on, and on. For forms, it would be even worse: you'd have to iterate
through
an unknown number of levels of subforms!, and so on.

If I personally wanted to match two databases, this is what I would
do.
Write code to dump all properties of each object into a flat file -
a
kind
of homegrown SaveAsText approach. Then run that code on the two
database,
and use a word-processor or file comparison program to compare the
two
flat-file outputs! This approach gives you an easy "heads up"

of
the

differences between the two databases. But it would not give you
programattic< visibility of those differences.

Unless, maybe, you structured the flat file output in such a way
that
you
could then compare two versions >using VBA< - then you >would< get
programattic visibility of what objects were different, & how they
wetre
different.

But this would all be a big ask, unless you were >very< familiar
with
VBA,
and all the relevant object models. IOW there is no easy way

to

do

 

[TC]

Hi Mark

I may have screwed up then. I'll check it tonight on a PC where I do have
Access, & reply tomorrow. Sorry for any confusion caused.

TC

Hi TC,

I tried each of these options for the Forms section and got the same error
message:
"Method of data member not found", with either "Remove" or "Delete"
highlighted in the debug window. I don't know if it's relevant, but
Help/Documents lists only "Refresh Method(DAO) under "Methods" -- which I
assume means that that is the only method associated with Documents.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Mark.

Hi Mark

Sorry for the delay, I've been busy for the last few days.

Comments interspersed.

Cheers,
TC


out,

*** It's no problem at all. When you export a form (for example), you can
think of it as only exporting a source-code definition of the form. Before
that form can actually >run<, the db (to which it was exported) must compile
it into a runnable state. The db to which it was exported, becomes
uncompiled until that occurs. All compilation actions occur automatically,
as & when required. You do not need to worry about this at all.



*** You should re-insert the ON ERROR GOTO 0 line that you deleted here.
Otherwise, the ON ERROR RESUME NEXT will apply to all following statements
in the whole module. That is definitely not what you want! You want it to
apply just to the .Delete statement. So you need ON ERROR GOTO 0 after that
statement, to cancel the ON ERROR RESUME NEXT.


*** Again, if you do not use the ON ERROR statements here, you are assuming
that there >is< a query of that name in the other database. Without the ON
ERROR statements, if thgere is >not< a query of that name, the .Delete
statement will fail. It is pointless to take that risk. Re-add the two ON
ERROR statements!


*** Be careful with the following loop variables (frmThis, rptThis, mcrThis
and modThis). You need to define them all 'As Document' - not 'As Form', 'As
report' and so on. A Container object contains Document objects - not Form,
Report, Script (macro) or Module objects as such.

As for the delete commands, I feel uncomfortable about the following
suggestion, for some reason, but I don't have Access on this PC, so

you'll

just have to give it a blast & see if it works. Try these for the frmThis
loop. The changes for the other loops should be self-evident.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Sorry to be vague on this, but I just can't remember with any certainty.

Cheers,
TC

For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

Hope it works for you.

Cheers,
TC


TC,
Thank you so much -- as much for the lesson, as for the solution. I'll
work
on this this weekend.
Mark


TC,


1st: Thank you.

No probs


2nd: I'm already assuming that the objects with matching names in
dbOther
are NOT identical in terms of structure, fields, etc. That's the
whole
point -- to eventually replace those with the newer objects in
dbThis.
But
first, I need to delete those from dbOther before I can export from
dbThis.
I'm using Access97 and will get an error if I try to export/replace
an
object with the same name (see Q160875) -- especially if it's a
Form.
But,
I
don't wish to delete ALL the objects in dbOther -- only those that
match
the
names of those in dbThis.

Understood. I missed that point. It makes it way simpler!


3rd: I'm having difficulty understanding what's happening in your
code.
I
think the code is saying: "For each tabledef in this database ("For
Each
tdThis In dbThis"),

Yes, except that I should have said dbThis.TABLEDEFS. Oops!


set the tabledef in dbOther to the same name as the
tabledef in dbThis ("set tdOther = dbOther.tabledefs
(tdThis.name)").

Yes. In other words, "see if there is a table with the same name in
the
other database". I do that by trying to establish a reference
(tdOther)
to
that table in the other database. If that table does not exist, an
error
occurs (Err.Number <> 0). If that table >does< exist, no error occurs,
and
tdOther now references that table in the other database. In my
original
code, I did not do anything with tdOther. I just wanted to see

if

I

purpose

of

the
error
statement -- that an error will occur for each table in dbOther
which
does
NOT match that in dbThis -- an error because supposedly there's
already
a
table in dbOther by that name -- and the error will result in the
procedure
skipping over each table in dbOther until the procedure gets

to
the

table
of
the same name? And, at this point, instead of "debug.print" I could
delete
that table in dbOther. If this is what is happening in the
procedure,
then
there might be a problem if there IS'NT a table in dbOther by that
name --
it would simply rename the first table it came to. Right?

See better code below (now that I understand your need properly).


4th: Also, I get an error in the procedure step: "For Each

tdThis

In
dbThis":
Run-time error '3251 -- Operation is not supported for this

type
of

object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The next
code
should delete, from the "other" database, every table that has the
same
name
as a table in the >current< database. I've put the changes in UPPER
CASE,
to
make them stand out. Instead of checking to see whether the

table
does

or
does not exist in the other database, I just delete it
unconditionally,
&
trap the error if the delete fails (ie. it >is not< in the other
database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef '
TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code for
queries,
using Querydef instead of Tabledef. If you get the queries
working,
ask again, & I'll show you how to do Forms, Macros & Modules. (You'll
need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you
will
clearly want to be sure that you have backup copies of the two
databases,
just in case the code is wrong and deletes the wrong objects, or
deletes
from the wrong database!


Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef,
tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the other
one"
endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an

object
in

th
is
database, whcih is also in the other database, is actually
identical<.
Mathing their >names<, is clearly not nearly enough.

For a table, you'd need to check every field; all the properties
of
every
field; the table description; every index & their

properties;
and

so
on,
an
on, and on. For forms, it would be even worse: you'd have to
iterate
through
an unknown number of levels of subforms!, and so on.

If I personally wanted to match two databases, this is what I
would
do.
Write code to dump all properties of each object into a flat
file -
a
kind
of homegrown SaveAsText approach. Then run that code on the two
database,
and use a word-processor or file comparison program to

compare
the

two
flat-file outputs! This approach gives you an easy "heads

up"

of would< how
they to 6
Apr IN
db2

 

[Mark]

Certainly no problem TC. I'm in no hurry here and certainly appreciate the
help.
Mark.

Hi Mark

I may have screwed up then. I'll check it tonight on a PC where I do have
Access, & reply tomorrow. Sorry for any confusion caused.

TC

Hi TC,

I tried each of these options for the Forms section and got the same error
message:
"Method of data member not found", with either "Remove" or "Delete"
highlighted in the debug window. I don't know if it's relevant, but
Help/Documents lists only "Refresh Method(DAO) under "Methods" -- which I
assume means that that is the only method associated with Documents.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Mark.

Hi Mark

Sorry for the delay, I've been busy for the last few days.

Comments interspersed.

Cheers,
TC


Hi TC,

This works so far. I believe I just need the delete command for the forms,
reports, macros, and modules. I had to alter one of your "block...if"
lines
(I don't remember which), and in the process of trying to figure

that
out,

I
just eliminated the error-handling code.

If you could show me the DeleteObject statement for one of these other
objects, I would sure appreciate it. Thanks again for the assistance
you've
already provided.

And, at the risk of overstaying my welcome, I have an additional question
regarding compiled and uncompiled states. If exporting some of these
objects
results in an uncompiled state in the target database, how much of a
problem
is that and what should I do about it?

*** It's no problem at all. When you export a form (for example), you can
think of it as only exporting a source-code definition of the form. Before
that form can actually >run<, the db (to which it was exported) must compile
it into a runnable state. The db to which it was exported, becomes
uncompiled until that occurs. All compilation actions occur automatically,
as & when required. You do not need to worry about this at all.


code thus far:

Private Sub Command0_Click()
Dim dbThis As Database, dbOther As Database
Dim tblThis As TableDef
Dim qryThis As QueryDef
Dim frmThis As Document
Dim rptThis As Document
Dim modThis As Document
Dim mcrThis As Document

Set dbThis = CurrentDb()
Set dbOther = DBEngine.OpenDatabase("C:\Access97\Test.mdb")

'TABLES
For Each tblThis In dbThis.TableDefs
If Not (tblThis.Name Like "MSYS*") Then
' NOT A SYSTEM TABLE.
On Error Resume Next
dbOther.TableDefs.Delete tblThis.Name

*** You should re-insert the ON ERROR GOTO 0 line that you deleted here.
Otherwise, the ON ERROR RESUME NEXT will apply to all following statements
in the whole module. That is definitely not what you want! You want it to
apply just to the .Delete statement. So you need ON ERROR GOTO 0 after that
statement, to cancel the ON ERROR RESUME NEXT.

End If
Next

'QUERIES
For Each qryThis In dbThis.QueryDefs

*** Again, if you do not use the ON ERROR statements here, you are assuming
that there >is< a query of that name in the other database. Without

the

ON

ERROR statements, if thgere is >not< a query of that name, the .Delete
statement will fail. It is pointless to take that risk. Re-add the two ON
ERROR statements!

dbOther.QueryDefs.Delete qryThis.Name
Next

'FORMS

*** Be careful with the following loop variables (frmThis, rptThis, mcrThis
and modThis). You need to define them all 'As Document' - not 'As

Form',
'As

report' and so on. A Container object contains Document objects - not Form,
Report, Script (macro) or Module objects as such.

As for the delete commands, I feel uncomfortable about the following
suggestion, for some reason, but I don't have Access on this PC, so

you'll

just have to give it a blast & see if it works. Try these for the frmThis
loop. The changes for the other loops should be self-evident.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Sorry to be vague on this, but I just can't remember with any certainty.

Cheers,
TC


For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

Hope it works for you.

Cheers,
TC


TC,
Thank you so much -- as much for the lesson, as for the

solution.
I'll

work
on this this weekend.
Mark


TC,


1st: Thank you.

No probs


2nd: I'm already assuming that the objects with matching

names
in

dbOther
are NOT identical in terms of structure, fields, etc. That's the
whole
point -- to eventually replace those with the newer objects in
dbThis.
But
first, I need to delete those from dbOther before I can export
from
dbThis.
I'm using Access97 and will get an error if I try to
export/replace
an
object with the same name (see Q160875) -- especially if

it's

a those
that name
in

exist,

an

error
occurs (Err.Number <> 0). If that table >does< exist, no error
occurs,
and
tdOther now references that table in the other database. In my
original
code, I did not do anything with tdOther. I just wanted to see

if

I
could
get< tdOther. This serves to tell you whether a table of that name
does
or
does not exist in the other database.


But, I
don't understand where the matching occurs. Is that the

purpose

of
the
error
statement -- that an error will occur for each table in dbOther
which
does
NOT match that in dbThis -- an error because supposedly there's
already
a
table in dbOther by that name -- and the error will result

in
the

procedure
skipping over each table in dbOther until the procedure gets to
the
table
of
the same name? And, at this point, instead of "debug.print" I
could
delete
that table in dbOther. If this is what is happening in the
procedure,
then
there might be a problem if there IS'NT a table in dbOther

by
that

name --
it would simply rename the first table it came to. Right?

See better code below (now that I understand your need properly).


4th: Also, I get an error in the procedure step: "For Each tdThis
In
dbThis":
Run-time error '3251 -- Operation is not supported for this type
of
object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The next
code
should delete, from the "other" database, every table that has the
same
name
as a table in the >current< database. I've put the changes in UPPER
CASE,
to
make them stand out. Instead of checking to see whether the table
does
or
does not exist in the other database, I just delete it
unconditionally,
&
trap the error if the delete fails (ie. it >is not< in the other
database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as

tabledef
'

TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar

code
for

queries,
using Querydef instead of Tabledef. If you get the queries
working,
ask again, & I'll show you how to do Forms, Macros & Modules.
(You'll
need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my head, you
will
clearly want to be sure that you have backup copies of the two
databases,
just in case the code is wrong and deletes the wrong objects, or
deletes
from the wrong database!


Thanks again, TC, for your help.
Mark

Matching objects by name is easy. For example:

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef,
tdOther
as
tabledef
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis
on error resume next
set tdOther = dbOther.tabledefs (tdThis.name)
if err.number = 0 then
debug.print tdThis.name; " is in both databases"
else
debug.print tdThis.name; " is in this db but not the
other
one"
endif
on error goto 0
next
' then do the same, "in reverse", to find any tables that are
' in the other database but not in this one.

The problem, however, will be in determining whether an object
in
th
is
database, whcih is also in the other database, is actually
identical<.
Mathing their >names<, is clearly not nearly enough.

For a table, you'd need to check every field; all the properties
of
every
field; the table description; every index & their properties;
and
so
on,
an
on, and on. For forms, it would be even worse: you'd have to
iterate
through
an unknown number of levels of subforms!, and so on.

If I personally wanted to match two databases, this is

what

I the
two up" give
you a
way way
to

Inc

or

Tue,

6

type

IN reading
the

 

[TC]

Hi Mark

Sorry, I boobed on this one!

As I should have remembered: The tabledefs & querydefs objects are

collections<. You can directly add & remove items to & from collections,

using simple Add and Delete methods. But the forms, reports, macros &
modules are stored in >containers<. You can >not< directly add & remove
items to & from containers using simple Add and Delete methods. Containers
do not have those methods.

To delete an item (say, a module) from a container, you can use
DeleteObject. However, DeleteObject is a method of the DoCmd object, which
belongs to the Application object - not to the Database object. So,
DeleteObject will delete from the database that is currently open in Access.
You can not say dbOther.DeleteObject, or dbOther.DoCmd.DeleteObject, or
anything similar using DeleteObject, to delete things from some >other<
database referenced as dbOther.

So here is what wyou have have to do. Open another instance of Access
"behind the scenes". Tell that other instance to open the other database, as
its current database. Then use the DeleteObject method of the DoCmd object

of that other instance of Access<, to delete things from that other

database.

For example:

' open another instance of Access.
dim oApp as object
set oapp = createobject ("Access.Application")

' open the other database using that instance.
oapp.opencurrentdatabase "<full path to other database>"

' delete module BLAH from that other database.
on error resume next
oapp.docmd.deleteobject acModule, "BLAH"
if err.number <> 0 then debug.print err.description
on error goto 0

' close the other database & instance of Access.
oapp.close
set oapp = nothing

Here's how I'd proceed. Retain the existing code for deleting the tables and
queries. After doing the queries, close the other database (dbOther.Close:
set dbOther = nothing). Then start the other instance of Access & tell it to
open the other database (as above). Then iterate through the current
database's Forms, Reports, Scripts & Modules collections to find the names
of the forms, reports, macros & modules in the current database. For each
one found, use DeleteObject (as shown above) to delete that object from the
other database. Remember to use acForm, acReport & so on, as appropriate, on
the DeleteObject call. Then close the other instance of Access when you have
finished.

OR - it would probably be way neater to junk the previous code, & use the
same approach for everything (tables & queries included). I'm sure you could
use acTable to delete the tables. Not sure about queries - either use
acQuery(?), or perhaps acTable will do queries also.

Are you doing this just for the purpose of being able to TransferDatabase
some object without it falling over because the object already exists? If
so, the neatest solution might be to write a procedure TransferObject
(..parameters..) to encapsulate the process of deleting the specified object
(if necessary) from the other database, and then also doing the transfer; a
"one-stop shop", as it were! The only downside would be the repeated
starting & stopping of another instance of Access. But if it runs fast
enough - who cares?

HTH,
TC

Certainly no problem TC. I'm in no hurry here and certainly appreciate the
help.
Mark.

Hi Mark

I may have screwed up then. I'll check it tonight on a PC where I do have
Access, & reply tomorrow. Sorry for any confusion caused.

TC

which

I

assume means that that is the only method associated with Documents.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Mark.


Hi Mark

Sorry for the delay, I've been busy for the last few days.

Comments interspersed.

Cheers,
TC


Hi TC,

This works so far. I believe I just need the delete command for the
forms,
reports, macros, and modules. I had to alter one of your "block...if"
lines
(I don't remember which), and in the process of trying to figure that
out,
I
just eliminated the error-handling code.

If you could show me the DeleteObject statement for one of these other
objects, I would sure appreciate it. Thanks again for the assistance
you've
already provided.

And, at the risk of overstaying my welcome, I have an additional
question
regarding compiled and uncompiled states. If exporting some of these
objects
results in an uncompiled state in the target database, how much of a
problem
is that and what should I do about it?

*** It's no problem at all. When you export a form (for example),

you
can

think of it as only exporting a source-code definition of the form. Before
that form can actually >run<, the db (to which it was exported) must
compile
it into a runnable state. The db to which it was exported, becomes
uncompiled until that occurs. All compilation actions occur automatically,
as & when required. You do not need to worry about this at all.


code thus far:

Private Sub Command0_Click()
Dim dbThis As Database, dbOther As Database
Dim tblThis As TableDef
Dim qryThis As QueryDef
Dim frmThis As Document
Dim rptThis As Document
Dim modThis As Document
Dim mcrThis As Document

Set dbThis = CurrentDb()
Set dbOther = DBEngine.OpenDatabase("C:\Access97\Test.mdb")

'TABLES
For Each tblThis In dbThis.TableDefs
If Not (tblThis.Name Like "MSYS*") Then
' NOT A SYSTEM TABLE.
On Error Resume Next
dbOther.TableDefs.Delete tblThis.Name

*** You should re-insert the ON ERROR GOTO 0 line that you deleted here.
Otherwise, the ON ERROR RESUME NEXT will apply to all following statements
in the whole module. That is definitely not what you want! You want

it
to

apply just to the .Delete statement. So you need ON ERROR GOTO 0 after
that
statement, to cancel the ON ERROR RESUME NEXT.

End If
Next

'QUERIES
For Each qryThis In dbThis.QueryDefs

*** Again, if you do not use the ON ERROR statements here, you are
assuming
that there >is< a query of that name in the other database. Without

the

ON
ERROR statements, if thgere is >not< a query of that name, the ..Delete
statement will fail. It is pointless to take that risk. Re-add the

two
ON

ERROR statements!

dbOther.QueryDefs.Delete qryThis.Name
Next

'FORMS

*** Be careful with the following loop variables (frmThis, rptThis,
mcrThis
and modThis). You need to define them all 'As Document' - not 'As Form',
'As
report' and so on. A Container object contains Document objects - not
Form,
Report, Script (macro) or Module objects as such.

As for the delete commands, I feel uncomfortable about the following
suggestion, for some reason, but I don't have Access on this PC, so you'll

just have to give it a blast & see if it works. Try these for the frmThis
loop. The changes for the other loops should be self-evident.

maybe:
dbOther.Containers![Forms].Documents(frmThis.name).Remove
or:
dbOther.Containers![Forms].Documents.Remove frmThis.name
or:
dbOther.Containers![Forms].Documents.Delete frmThis.name

Sorry to be vague on this, but I just can't remember with any certainty.

Cheers,
TC


For Each frmThis In dbThis.Containers!Forms.Documents
'enter delete command here
Debug.Print frmThis.Name 'temporary filler
Next

'REPORTS
For Each rptThis In dbThis.Containers!Reports.Documents
'enter delete command here
Debug.Print rptThis.Name 'temporary filler
Next

'MACROS
For Each mcrThis In dbThis.Containers!Scripts.Documents
'enter delete command here
Debug.Print mcrThis.Name 'temporary filler
Next

'Modules
For Each modThis In dbThis.Containers!Modules.Documents
'enter delete command here
Debug.Print modThis.Name 'temporary filler
Next
End Sub


-- Mark.

Hope it works for you.

Cheers,
TC


TC,
Thank you so much -- as much for the lesson, as for the solution.
I'll
work
on this this weekend.
Mark


TC,


1st: Thank you.

No probs


2nd: I'm already assuming that the objects with matching names
in
dbOther
are NOT identical in terms of structure, fields, etc.

That's
the

whole
point -- to eventually replace those with the newer

objects

in it's exist, see
if gets
to

"debug.print"

I

could
delete
that table in dbOther. If this is what is happening in the
procedure,
then
there might be a problem if there IS'NT a table in dbOther by
that
name --
it would simply rename the first table it came to. Right?

See better code below (now that I understand your need properly).


4th: Also, I get an error in the procedure step: "For Each
tdThis
In
dbThis":
Run-time error '3251 -- Operation is not supported for

this
type

of
object.

Oops! Should have been "For Each tdThis In dbThis.TABLEDEFS".

Let's amend the original code to suit your needs properly. The
next
code
should delete, from the "other" database, every table that

has
the

same
name
as a table in the >current< database. I've put the changes in
UPPER
CASE,
to
make them stand out. Instead of checking to see whether the table
does
or
does not exist in the other database, I just delete it
unconditionally,
&
trap the error if the delete fails (ie. it >is not< in the other
database).

(UNTESTED)

dim dbThis as database, dbOther as database, tdThis as tabledef
'
TDOTHER
DELETED.
set dbThis = currentdb()
set dbOther = dbengine.opendatabase ("path to other database")
for each tdThis in dbThis.TABLEDEFS
IF NOT ( TDTHIS.NAME LIKE "MSYS*" ) THEN
' NOT A SYSTEM TABLE.
on error resume next
DBOTHER.TABLEDEFS.DELETE TDTHIS.NAME
if err.number = 0 then
DEBUG.PRINT "DELETED TABLE "; TDTHIS.NAME
else
DEBUG.PRINT "COULDN'T DELETE "; TDTHIS.NAME; " - ";
ERR.DESCRIPTION
endif
on error goto 0
next

See if that code works ok for tables. If so, write similar code
for
queries,
using Querydef instead of Tabledef. If you get the queries
working,
ask again, & I'll show you how to do Forms, Macros & Modules.
(You'll
need
to use "containers" and "documents" for those.)

NOTE. As all of this is UNTESTED code, off the top of my

head,
you

will
clearly want to be sure that you have backup copies of the two
databases,
just in case the code is wrong and deletes the wrong

objects,

or that
are

have

to what "heads
up"

such

a &
how Inc Tue, type of
the

 

[TC]

' close the other database & instance of Access.
oapp.close
set oapp = nothing

Oops:

oApp.Quit ' not Close

TC

 

[Mark]

Thanks TC
I'll need some time to process this. Thanks for the time you took to write
this.
Mark.

 

[TC]

No probs. I'll be busy for the next few days anyway. Good luck!

TC

 

[Mark]

Hi TC,
With your help and some code I found posted by ""Shamil", I believe I have
it licked. I just need to add the standard routine to copy the new objects
now that the older ones have been deleted -- I should be OK with that. Thank
you for all your patience and guidance!
Mark.

Dim Dbs As Database
Dim app As New Access.Application
Dim con As Container
Dim doc As Document
Dim qdf As QueryDef
Dim lngObjType As Long

Set Dbs = CurrentDb
app.OpenCurrentDatabase "C:\Access97\Test.mdb"

For Each con In Dbs.Containers
Select Case con.Name
Case "Tables", "Forms", "Reports", "Scripts", "Modules":
For Each doc In con.Documents
If Left(doc.Name, 4) <> "Mysys" Then
Select Case doc.Container
Case "Tables":
On Error Resume Next
Set qdf = Dbs.QueryDefs(doc.Name)
If Err <> 0 Then
lngObjType = acTable
Else
lngObjType = acQuery
End If
On Error GoTo 0
Case "Forms": lngObjType = acForm
Case "Reports": lngObjType = acReport
Case "Scripts": lngObjType = acMacro
Case "Modules": lngObjType = acModule
Case Else: lngObjType = -1
End Select

If lngObjType <> -1 Then
On Error Resume Next
app.DoCmd.DeleteObject lngObjType, doc.Name
End If
End If
Next
Case Else
End Select
Next
app.Quit
Set doc = Nothing
Set con = Nothing
Set qdf = Nothing
Set app = Nothing