Is it possible to / and how do I / copy only some tables from a db

We have a slit check database that uses some tables from our enterprise
resource software to help populate records in the Slitcheck database. We
don't want to link to the live dataset in the ERP software (Access 2003), but
we don't want to copy and link to unecessary tables. Right now, when we click
the launch cmd to open the slitcheck database, we also paste a new copy of
the ERP tables into a folder that is linked to this slitcheck database. (This
is in order to link to WorkOrders and a handful of other tables in the ERP
dataset.) I'd like to speed up the copy/paste process by only copying
certain tables from the ERP dataset instead of all of them. Can anyone tell
me how to write a batch command that selects out certain tables and copies
them?

Thanks in advance
Bob Waggoner

Hi Bob,

Do I take it correctly that all the databases involved are some version
of Access? There can be good reasons for importing instead of linking.
However, I am wondering why you do not want to just link to the tables if
they are only used to assist with the populations of records in the slit
check database? Seems like that would be the fastest/easiest thing to do.
Some of what you are saying about copying the ERP tables into a folder does
not make a lot of sense to me. Why a folder instead of directly into the
slit check database? Maybe if you post the current code that does the
"copy/paste process" that will clear some things up as well as provide a
starting point for telling you how to change it to work as you desire.

Clifford Bass

"Bob Waggoner" wrote:

We have a slit check database that uses some tables from our enterprise
resource software to help populate records in the Slitcheck database. We
don't want to link to the live dataset in the ERP software (Access 2003), but
we don't want to copy and link to unecessary tables. Right now, when we click
the launch cmd to open the slitcheck database, we also paste a new copy of
the ERP tables into a folder that is linked to this slitcheck database. (This
is in order to link to WorkOrders and a handful of other tables in the ERP
dataset.) I'd like to speed up the copy/paste process by only copying
certain tables from the ERP dataset instead of all of them. Can anyone tell
me how to write a batch command that selects out certain tables and copies
them?

Thanks in advance
Bob Waggoner

Clifford,
Thanks for your question. I wrote back but now that I check, I don't see my
posted reply - so please forgive me for appearing not to have responded. Let
me explain: We have a quality system database that extracts (mostly) current
information from the ERP databse - both access 2003. We use the linked tables
from the ERP to find current sales orders, etc., so we can perform slit
checks, create CofC's, etc., (using current work order information contained
in the ERP). [We need to link to keep the information current]. We copy the
"live" ERP dataset once every 12 hours (that's current enough) and post it in
a separate folder. That dataset is what we link the slit check and quality
databases to. However, we don't need all of the tables from the ERP - and as
they grow, it takes longer to copy and paste them. That's why I'm asking if
it is possible to specify which tables we want to copy and past into the
linked tables folder.

Hope this provides enough information. I know we're probably talking batch
command here, but I'm hoping someone has faced this before and can provide an
answer.
Thanks
Bob

"Clifford Bass" wrote:

Hi Bob,

Do I take it correctly that all the databases involved are some version
of Access? There can be good reasons for importing instead of linking.
However, I am wondering why you do not want to just link to the tables if
they are only used to assist with the populations of records in the slit
check database? Seems like that would be the fastest/easiest thing to do.
Some of what you are saying about copying the ERP tables into a folder does
not make a lot of sense to me. Why a folder instead of directly into the
slit check database? Maybe if you post the current code that does the
"copy/paste process" that will clear some things up as well as provide a
starting point for telling you how to change it to work as you desire.

Clifford Bass

"Bob Waggoner" wrote:

We have a slit check database that uses some tables from our enterprise
resource software to help populate records in the Slitcheck database. We
don't want to link to the live dataset in the ERP software (Access 2003), but
we don't want to copy and link to unecessary tables. Right now, when we click
the launch cmd to open the slitcheck database, we also paste a new copy of
the ERP tables into a folder that is linked to this slitcheck database. (This
is in order to link to WorkOrders and a handful of other tables in the ERP
dataset.) I'd like to speed up the copy/paste process by only copying
certain tables from the ERP dataset instead of all of them. Can anyone tell
me how to write a batch command that selects out certain tables and copies
them?

Thanks in advance
Bob Waggoner

Hi Bob,

No problem. The discussion group has been problematic at times, at
least as accessed by Microsoft.

The quick answer is, yes, you can export just selected tables. Easiest
would be to use the DoCmd.TransferDatabase command. Or maybe the
DoCmd.OutputTo command. You would do it once for each of the desired tables.

However, I think my question still remains: Why not use tables links
to the live system? No copying needed. As you stated, copying and pasting
gets longer and longer. And each time you are copying the entire database or
even entire tables, you are placing a significant performance hit on the
database. Queries that use indexes would typically be much more efficient.
I also have another question: Do you really need all of the data in the
tables you want? Or will a subset work? The answer to those two questions
will impact what you choose to do.

Clifford Bass

"Bob Waggoner" wrote:

Clifford,
Thanks for your question. I wrote back but now that I check, I don't see my
posted reply - so please forgive me for appearing not to have responded. Let
me explain: We have a quality system database that extracts (mostly) current
information from the ERP databse - both access 2003. We use the linked tables
from the ERP to find current sales orders, etc., so we can perform slit
checks, create CofC's, etc., (using current work order information contained
in the ERP). [We need to link to keep the information current]. We copy the
"live" ERP dataset once every 12 hours (that's current enough) and post it in
a separate folder. That dataset is what we link the slit check and quality
databases to. However, we don't need all of the tables from the ERP - and as
they grow, it takes longer to copy and paste them. That's why I'm asking if
it is possible to specify which tables we want to copy and past into the
linked tables folder.

Hope this provides enough information. I know we're probably talking batch
command here, but I'm hoping someone has faced this before and can provide an
answer.
Thanks
Bob

We can certainly just update the data in the tables with whatever has been
added. I'm not sure how to do that, however.

Bob

"Clifford Bass" wrote:

Hi Bob,

No problem. The discussion group has been problematic at times, at
least as accessed by Microsoft.

The quick answer is, yes, you can export just selected tables. Easiest
would be to use the DoCmd.TransferDatabase command. Or maybe the
DoCmd.OutputTo command. You would do it once for each of the desired tables.

However, I think my question still remains: Why not use tables links
to the live system? No copying needed. As you stated, copying and pasting
gets longer and longer. And each time you are copying the entire database or
even entire tables, you are placing a significant performance hit on the
database. Queries that use indexes would typically be much more efficient.
I also have another question: Do you really need all of the data in the
tables you want? Or will a subset work? The answer to those two questions
will impact what you choose to do.

Clifford Bass

"Bob Waggoner" wrote:

Clifford,
Thanks for your question. I wrote back but now that I check, I don't see my
posted reply - so please forgive me for appearing not to have responded. Let
me explain: We have a quality system database that extracts (mostly) current
information from the ERP databse - both access 2003. We use the linked tables
from the ERP to find current sales orders, etc., so we can perform slit
checks, create CofC's, etc., (using current work order information contained
in the ERP). [We need to link to keep the information current]. We copy the
"live" ERP dataset once every 12 hours (that's current enough) and post it in
a separate folder. That dataset is what we link the slit check and quality
databases to. However, we don't need all of the tables from the ERP - and as
they grow, it takes longer to copy and paste them. That's why I'm asking if
it is possible to specify which tables we want to copy and past into the
linked tables folder.

Hope this provides enough information. I know we're probably talking batch
command here, but I'm hoping someone has faced this before and can provide an
answer.
Thanks
Bob

Hi Bob,

You still have not answered why linked tables will not work for your
situation? So I am wondering: Do you know what a linked table is? Do you
know how to create a link to a table in another database? Do you understand
that using linked tables eliminates the need to copy ANY data?

There are of course situations where linked tables are not the best way
to go. So, if you situation is such that they are not, or if you are just
plain dead set against using linked tables, it is time to be specific instead
of general. Give me an example of a table from which you want to copy.
Include the name of the table, the primary key field(s) of the table and if
there are any fields that make if easy to identify newer records if the
primary key field(s) do(es) not. So, if you have a primary key that is a
sequential autonumber field, that is all that is needed. If not, is there
maybe a date-added or a date-last-modified column? Do you need to deal with
updated records? How about deleted records? How many rows are typically
added/modified/deleted between update times?

Clifford Bass

"Bob Waggoner" wrote:

We can certainly just update the data in the tables with whatever has been
added. I'm not sure how to do that, however.

Bob

Thank you for you response. The answer to your questions is yes. I know what
a linked table is and how to create a link between databases. The reason we
don't link to the live ERP is because our policy is to avoid doing anything
that could corrupt the ERP system's data. So, what we do is have a batch
command that copies the ERP.mdb tables, renames them and pastes them into the
LinkedTables folder. We link the QS database to the tables in that folder.
We don't need all the tables (some of them are huge) - so my question was,
how do we just copy the tables we need to link to? I hope that clarifies the
question.

Again, thanks.


"Clifford Bass" wrote:

Hi Bob,

You still have not answered why linked tables will not work for your
situation? So I am wondering: Do you know what a linked table is? Do you
know how to create a link to a table in another database? Do you understand
that using linked tables eliminates the need to copy ANY data?

There are of course situations where linked tables are not the best way
to go. So, if you situation is such that they are not, or if you are just
plain dead set against using linked tables, it is time to be specific instead
of general. Give me an example of a table from which you want to copy.
Include the name of the table, the primary key field(s) of the table and if
there are any fields that make if easy to identify newer records if the
primary key field(s) do(es) not. So, if you have a primary key that is a
sequential autonumber field, that is all that is needed. If not, is there
maybe a date-added or a date-last-modified column? Do you need to deal with
updated records? How about deleted records? How many rows are typically
added/modified/deleted between update times?

Clifford Bass

"Bob Waggoner" wrote:

We can certainly just update the data in the tables with whatever has been
added. I'm not sure how to do that, however.

Bob