How can i control objects of another DB thru code?

[Mota]

Hi;
Can i copy a table of another Database in the same database?(Except for
using a 'select...into' query that doesnt keep field properties of that
table).
For example,can i affect the DoCmd methods on another DB,while im using it
in the current database modules?This means reffering to DoCmd object of
another Application,or something like it...
Thank you so much for ur help.

 

[Marshall Barton]

Can i copy a table of another Database in the same database?(Except for
using a 'select...into' query that doesnt keep field properties of that
table).
For example,can i affect the DoCmd methods on another DB,while im using it
in the current database modules?This means reffering to DoCmd object of
another Application,or something like it...

Well, you probably can do whatever you trying to do using
Automation. But, it's more likely you do it without opening
another instance of Access for the other database.

If all you want to do is retrieve data from a table in the
other database, why don't you just link to it? If that
misses the point of what you are trying to do, it's probably
because you have not explained what the objective is.

 

[Mota]

Dear Marshall;
I want to make a copy of a table of a foreighn DB,into that DB and under
another name,of course.The copy will not be in the current Database that im
coding in.So,i dont want to retrive that table,and dont need to make a link
to it.This is equal to opening that MDB file and copy and paste 1 table of
it there.Can it be possible thru code?
Thank you for ur help.

 

[Marshall Barton]

If the other mdb is not being used by anyone, why not copy
the whole file as a backup? If other's might be using it,
then this whole idea is suspect.

As long as you can open the other database in exclusive
mode, you can try using Automation to open it in another
instance of Access and run the TransferDatabase method. I
don't have enough experience with this to provide details if
you should decide to go this route.

Another way is for you to use DAO to open a database object
to the other file and create a copy of the TableDef along
with code to create each field and all of their properties
and the indexes (the table's Relationships have no meaning
in a copy so don't worry about that). Then you can run a
query to copy the data. This is rather long, tedious thing
to code (there are examples of each needed method in Help),
but it is the most flexible and it avoids opening another
instance of Access.

OTOH, I do have to wonder what the purpose of this is. If
it's just to make a backup of the table then you don't need
all the idexes and properties. Use your maketable query to
copy the data, then if needed, delete the mangled data from
the original table and use an append query to restore the
saved data.

 

[Mota]

Using DAO is a good idea for me.Because it can inherit Field
Properties.But,how to copy a TableDef in DAO?
Do u mean using CreateTableDef method?I would be so much grateful to you if
show me a code snippet to do this.
Thank you.

If the other mdb is not being used by anyone, why not copy
the whole file as a backup? If other's might be using it,
then this whole idea is suspect.

As long as you can open the other database in exclusive
mode, you can try using Automation to open it in another
instance of Access and run the TransferDatabase method. I
don't have enough experience with this to provide details if
you should decide to go this route.

Another way is for you to use DAO to open a database object
to the other file and create a copy of the TableDef along
with code to create each field and all of their properties
and the indexes (the table's Relationships have no meaning
in a copy so don't worry about that). Then you can run a
query to copy the data. This is rather long, tedious thing
to code (there are examples of each needed method in Help),
but it is the most flexible and it avoids opening another
instance of Access.

OTOH, I do have to wonder what the purpose of this is. If
it's just to make a backup of the table then you don't need
all the idexes and properties. Use your maketable query to
copy the data, then if needed, delete the mangled data from
the original table and use an append query to restore the
saved data.
--
Marsh
MVP [MS Access]

I want to make a copy of a table of a foreighn DB,into that DB and under
another name,of course.The copy will not be in the current Database that
im
coding in.So,i dont want to retrive that table,and dont need to make a
link
to it.This is equal to opening that MDB file and copy and paste 1 table of
it there.Can it be possible thru code?