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

[Bob Waggoner]

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 Bass]

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]

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]

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]

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

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

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

 

[Bob Waggoner]

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]

Hi Bob,

Reading data from tables is no more likely to cause corruption than
copying the tables themselves. Be that as it may, here is what you can do.
Copy and paste the following into a regular (not class, not form, not report)
module in your destination (LinkedTables) database.

==========================================
Public Function RefreshMyTables() As Boolean

Const cstrSourceDatabase As String = _
"C:\ERP.mdb"

Dim intIndex As Integer
Dim strTableName As String
Dim varTableNames As Variant

On Error Resume Next

RefreshMyTables = True
varTableNames = Array("tblSome_Table", "tblAnother_Table")

For intIndex = LBound(varTableNames) To UBound(varTableNames)
strTableName = varTableNames(intIndex)
DoCmd.DeleteObject acTable, strTableName
Err.Clear
DoCmd.TransferDatabase acImport, "Microsoft Access", _
cstrSourceDatabase, acTable, _
strTableName, strTableName
If Err.Number <> 0 Then
' Log the error, perhaps in an error table
Err.Clear
RefreshMyTables = False
End If
Next intIndex

End Function
==========================================

Modify the location of the ERP.mdb file. Modify the list of tables in
the "varTableNames = Array("..." line. Next create a macro. In it do a
RunCode command with "RefreshMyTables()" for the parameter; without the
quotes. Add a Quit command. This will run the function and then quit out of
Access. Save the macro, giving it the name "AutoExec". Change the settings
for the database so that it does a compact-on-close so as to avoid bloating.
Next, on the computer that is to run the refresh periodically create a
Windows Scheduled Task that opens up the database on the desired frequency.
You are done. When the scheduled task runs it will open the database in
Access. The database will then go through the list of tables, deleting and
then reimporting them. For improvements, you could add a logging table and
log each run that is done along with any errors encountered. Also, I did not
deal with the issues of other people accessing the database at the time of
refresh so you will need to decide what, if anything, you want to do about
that.

Hope that helps,

Clifford Bass