If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Is it possible to / and how do I / copy only some tables from a db
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 |
#3
|
|||
|
|||
Is it possible to / and how do I / copy only some tables from
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 |
#4
|
|||
|
|||
Is it possible to / and how do I / copy only some tables from
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 |
#5
|
|||
|
|||
Is it possible to / and how do I / copy only some tables from
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 |
#6
|
|||
|
|||
Is it possible to / and how do I / copy only some tables from
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 |
#7
|
|||
|
|||
Is it possible to / and how do I / copy only some tables from
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 |
Thread Tools | |
Display Modes | |
|
|