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
|
|||
|
|||
Append ODBC tables into Access Table
To All,
I am ODBC connected to a Teradata table having about 10 fields and 300,000 records. I want to append these records into my Access table and am having speed problems, not being able to get this append under 4 minutes. When I used a passthru query with an Access Append query on top of it, I got the best, 4 min performance, but I would like to get this under 60 secs if possible. My question is, in general, what is the fastest way to load access tables with data from a non-Access external database source? Thank you |
#2
|
|||
|
|||
Append ODBC tables into Access Table
I think you might have the best solution short of possibly using SSIS in SQL
Server. I would try remove all constraints and indexes from the target table to see if that makes a difference. -- Duane Hookom Microsoft Access MVP "Ross" wrote: To All, I am ODBC connected to a Teradata table having about 10 fields and 300,000 records. I want to append these records into my Access table and am having speed problems, not being able to get this append under 4 minutes. When I used a passthru query with an Access Append query on top of it, I got the best, 4 min performance, but I would like to get this under 60 secs if possible. My question is, in general, what is the fastest way to load access tables with data from a non-Access external database source? Thank you |
#3
|
|||
|
|||
Append ODBC tables into Access Table
Ross
Your 'given' is that the data has to be downloaded to your Access table (and appended). Why? What will having a copy of the Teradata table in Access allow you to do that you couldn't do if you simply connected to the Teradata table? I ask in case the need you are trying to solve this way may also be solved some other way... Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Ross" wrote in message ... To All, I am ODBC connected to a Teradata table having about 10 fields and 300,000 records. I want to append these records into my Access table and am having speed problems, not being able to get this append under 4 minutes. When I used a passthru query with an Access Append query on top of it, I got the best, 4 min performance, but I would like to get this under 60 secs if possible. My question is, in general, what is the fastest way to load access tables with data from a non-Access external database source? Thank you |
#4
|
|||
|
|||
Append ODBC tables into Access Table
Duane,
No Luck with indexes and restraints. Performance was approximately the same. Ross "Duane Hookom" wrote: I think you might have the best solution short of possibly using SSIS in SQL Server. I would try remove all constraints and indexes from the target table to see if that makes a difference. -- Duane Hookom Microsoft Access MVP "Ross" wrote: To All, I am ODBC connected to a Teradata table having about 10 fields and 300,000 records. I want to append these records into my Access table and am having speed problems, not being able to get this append under 4 minutes. When I used a passthru query with an Access Append query on top of it, I got the best, 4 min performance, but I would like to get this under 60 secs if possible. My question is, in general, what is the fastest way to load access tables with data from a non-Access external database source? Thank you |
#5
|
|||
|
|||
Append ODBC tables into Access Table
Jeff,
I Need to have this data in an access table mainly for performance purposes. The table in question will be used for many lookups by multiple users, all day. The ODBC connection is tooooo doggy, particularly if the Access Db is on put out on the network. You are right and I agree, I would prefer not to bring it in but feel that I have too. Thank you Ross "Jeff Boyce" wrote: Ross Your 'given' is that the data has to be downloaded to your Access table (and appended). Why? What will having a copy of the Teradata table in Access allow you to do that you couldn't do if you simply connected to the Teradata table? I ask in case the need you are trying to solve this way may also be solved some other way... Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Ross" wrote in message ... To All, I am ODBC connected to a Teradata table having about 10 fields and 300,000 records. I want to append these records into my Access table and am having speed problems, not being able to get this append under 4 minutes. When I used a passthru query with an Access Append query on top of it, I got the best, 4 min performance, but I would like to get this under 60 secs if possible. My question is, in general, what is the fastest way to load access tables with data from a non-Access external database source? Thank you . |
#6
|
|||
|
|||
Append ODBC tables into Access Table
I recently had to perform something similar and it took too long. I added a
last updated field to the SQL table so I could filter out records that hadn't been added or changed. -- Duane Hookom Microsoft Access MVP "Ross" wrote: Jeff, I Need to have this data in an access table mainly for performance purposes. The table in question will be used for many lookups by multiple users, all day. The ODBC connection is tooooo doggy, particularly if the Access Db is on put out on the network. You are right and I agree, I would prefer not to bring it in but feel that I have too. Thank you Ross "Jeff Boyce" wrote: Ross Your 'given' is that the data has to be downloaded to your Access table (and appended). Why? What will having a copy of the Teradata table in Access allow you to do that you couldn't do if you simply connected to the Teradata table? I ask in case the need you are trying to solve this way may also be solved some other way... Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Ross" wrote in message ... To All, I am ODBC connected to a Teradata table having about 10 fields and 300,000 records. I want to append these records into my Access table and am having speed problems, not being able to get this append under 4 minutes. When I used a passthru query with an Access Append query on top of it, I got the best, 4 min performance, but I would like to get this under 60 secs if possible. My question is, in general, what is the fastest way to load access tables with data from a non-Access external database source? Thank you . |
Thread Tools | |
Display Modes | |
|
|