Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    bobfmartin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4

    What is the best way to copy (or mirror) large ODBC linked tables into another database?

    I currently use make table queries to create copies of 30 ODBC linked (live) tables into another database. I do this weekly so I can create custom access reports from the data in these tables. I created this process 10 plus years ago. Originally, this process only took minutes. Now, this process is very time consuming and I think it is due to the number of existing records in several of the tables in the ODBC linked tables (one million plus). Is there a better, more time efficient, way to create mirror images of these large ODBC linked tables?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you can do it on the server side, make a copy.
    if not, run a make table or append query to add to the 'copy' db.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by bobfmartin View Post
    I currently use make table queries to create copies of 30 ODBC linked (live) tables into another database.
    What is the "another database"? Access? SQL Server? Excel? A text file?

  4. #4
    bobfmartin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    I don't understand your response. Can you expand or clarify your guidance?

  5. #5
    bobfmartin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    The live ODBC data is in an Oracle database and I am moving it to an Access 2016 database.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Aside from moving them into what many would consider an inferior container, do you really need 10 years and a million records in one back end that you'll be querying against from now on? You could split into multiple back ends based on time spans? Record count isn't a good indicator of file size, which is more important.

    Your performance problem might be from repeatedly deleting and recreating tables, which is something I wouldn't do. Better to just update and append what you have.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How long must you archive/store/hold the data? Is it a legal issue or???
    Since the data resides in Oracle, perhaps you could archive the Oracle tables and/or use some Oracle utilities to archive the data to a format you can use/restore. You might consider what the archived data is/will be/has shown to be used for and separate the data into some applicable files. You might even take that "probable use" scenario and have some archive reporting utility. Is there a specific reason to have you keep the archive in Access?
    You could make a copy of the backend database for archive, then delete the rows that you no longer require from the active one to make it the current production database.

    It's difficult to be specific since there are many unknowns and you know the environment intimately.
    Hopefully some of the responses will help you decide.

    Good luck with your project.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Since I know next to nothing about your dB/process, it is question time....

    Yes, the time it takes to copy the Oracle tables to an Access dB is dependent on the number of records.

    Why do you have to make a copy of the BE to create "custom reports" each week? If you create a custom report, won't the report work week after week?


    Is there a "Management Studio" (like SQL Server) that you have access to where you can do backups and manage the Oracle dB?
    If yes, could you make a copy of the "Live" dB, create another ODBC connection, link the copy of the (Oracle) BE to link to a new Access FE for the custom reports?


    And Welcome to the forum...

  9. #9
    bobfmartin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    THANKS to all that responded to my make tables question. I really appreciate the quick responses on what I was hoping was a simple request. Since I am a self taught access user, I will take some time and evaluate each response and hopefully resolve the issue or maybe revise the process. Some of the responses that I will evaluate are creating a (or multiple) backend database (no clue what that involves), and appending the new data to the table rather that making a mirror of the entire table (my concern with this approach is the possibility of creating duplicate records).


    One thing to note: I initially created this database for my own use to create custom operation and maintenance reports from the data in our Asset/Maintenance Management program. After some of the other managers saw the value of my custom reports, they began using the database as well. It has been a great management tool for quite some time and we do regularly evaluate the historical data. So I need to make sure if I change the process it's an easy change for all users.

    Thanks again!

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    my concern with this approach is the possibility of creating duplicate records
    You decide on what constitutes duplicate data (i.e. 1 single field or a combination of fields) and either create composite PK's or composite indexes if it involves more than one field. Then you run the append query and either a) ignore the warnings about duplicates, b) use the .Execute method of the CurrentDb object to run action queries, c)turn off warnings but ensure they get turned back on. a will probably cause confusion. c requires a properly constructed error handler to ensure warnings get turned back on. b is considered the best approach by most developers. Regarding indexes, I have yet to see a response from anyone who says "don't use composite PK's" when I invited them to provide evidence to back their claim, and I've searched and never found any. In the absence of that, I'd say it's your call. All I can say is I've tried both methods, and the aforementioned source of the ODBC tables used only composite PK's. That was an in house built CMMS system that had $10 million expenditure for support and development by (roughly) the year 2000, so take from that what you wish. As for update queries, you would know best if the possibility exists that an update query could also violate a composite index/PK.

    I find it odd that you need a complete copy of each of 30 tables if that's what you're doing. I used MT queries to build my native tables, but only one time. Also, my tables only contained the fields necessary for the db I built as opposed to copying ODBC tables with 20, 30, 40 or whatever fields along with decades of data. After that, they were automatically updated/appended nightly using Task Scheduler (that is another topic all together) and composite indexes took care of preventing duplicates.

    I'm a bit suspicious that your db is not split. The lack of you mentioning this fact makes me think no, otherwise you'd know it would be of vital importance in any discussions around your problem.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Micron View Post
    <snip> Regarding indexes, I have yet to see a response from anyone who says "don't use composite PK's" when I invited them to provide evidence to back their claim, and I've searched and never found any. <snip>
    Apologies if I am misunderstanding.. I use an autonumber type field as the PK field and compound indexes if I need to prevent duplicate records.
    This is based on this article on the FMS website Microsoft Access Tables: Primary Key Tips and Techniques


    Quote Originally Posted by Micron View Post
    <snip>I'm a bit suspicious that your db is not split.<snip>
    Again, maybe I am misunderstanding, but the OP said he was using linked tables from an Oracle database. Doesn't that imply a split dB? Access FE and Oracle BE??

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Indexes:
    Sometimes people justify composite (multi-field) primary keys to avoid duplicates. This is the wrong approach. To prevent duplicates, create a secondary index for those fields and define it as unique. That lets the secondary index handle that job while the single field primary index retains its efficiencies.
    I have read that before. Unfortunately, it is undated, so does the "efficiency" really matter that much any more? That is the only reason given, and while it's easy to buy in to the statements posted there, there's no proof or citation that quantifies what's really just an opinion otherwise. Unless you've had to deal with tables set up this way (as have I) you probably wouldn't be as skeptical as me. The db I'm referring to was probably sql server. Out of about 7,000 employees, there might have been 2 to 3 thousand concurrent users. The CMMS app was built in Power Builder. Speed was more than acceptable over a vast network. And, God forbid, there were dozens of text PK fields, many of them composite. The biggest upside to that would be that you could, for example, get data from a table where DEPT was a foreign key without having to include the departments table because you didn't have to deal with DeptID being 124, for example. Think about that for a sec.

    Well, I'm not here to convert anyone, and I do use autonumber PK's regularly in Access development. It's just that I've seen both sides, so I would need more than unfounded statements. Thanks for the link though. I don't mean to disparage the author - just saying there's no proof offered. Might as well have been spoken by a politician!

    As for the other point, no. You can link to ODBC tables without splitting a db.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron
    Not to get too far off topic, but I looked at a friends dB (years ago) where he had a compound PK in a table. At the time, it took me a while to figure the design out. He had to have both PK fields in the foreign table - seemed more complex when trying to create queries. So I went with the autonumber as the PK field and used compound indexes.


    As for the other point, no. You can link to ODBC tables without splitting a db.
    Oops. I sit corrected. I saw the OP had posted
    The live ODBC data is in an Oracle database and I am moving it to an Access 2016 database
    and quit thinking.

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wasn't sure my limited response re Oracle was clear either. I meant you can create a new Access db and link to an Oracle db in what essentially would then be your fe. You might have a few native tables in your db to boot, but that would be irrelevant, and whether or not the linked tables were updatable would depend entirely on permissions. To validate, go to a toy db you have (that isn't split if that would help, but I don't think it matters) and go New Data Source > From Other Sources > ODBC > Link. I don't suppose you'd have any handy, but aside from choosing either a File or Machine source, you can do this in any un-split db.
    Sorry if that's not what you're comment was about, but that's how I interpreted it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry to jump in so late in this discussion, just thought that maybe the free utility I have (http://forestbyte.com/ms-access-utilities/fba-fuze/) would help the OP. Once the proper parameters are set it an be scheduled using the Windows scheduler to run on its one at it will "fuze" any front-end with the various back-end tables it might be linked to. So in the OP's case he could create a front-end with his custom reporting interface linked to the live Oracle tables and in the morning he will find a fresh copy of an all-in-one (unsplit) db waiting for his reporting.

    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 03-21-2018, 01:45 PM
  2. Update from very large ODBC linked table
    By kagoodwin13 in forum Queries
    Replies: 2
    Last Post: 05-25-2016, 08:29 PM
  3. Replies: 13
    Last Post: 10-04-2015, 09:52 AM
  4. ODBC Linked tables Error
    By tpcervelo in forum Access
    Replies: 0
    Last Post: 02-10-2011, 01:40 PM
  5. ODBC Linked Tables Become Unupdateable
    By dollardeveloper in forum Access
    Replies: 0
    Last Post: 04-08-2009, 07:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums