Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automate linked table manager process with a macro (Access 2003)

    I have a macro that creates individual databases for each of our sales reps from several linked tables. Excel pivot table reports are based on the databases. I want to to automate the process as much as possible so that when I am out of the office my sub can push one or two buttons that will update the linked tables and run the macro. I really don't want someone unversed in Access to have to go through all of the motions necessary to locate the linked table manager, select the tables and update the links. Is it possible to run the linked table manager from a macro?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: automate linked table manager process with a macro (Access 2003)

    Are you referring to a code routine or an Access macro? In the rest of Office, macro has a different meaning than in Access. Could you explain what you mean by "creates individual databases ... from several linked tables"? Are you simply exporting data, creating queries, or what?
    Charlotte

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automate linked table manager process with a macro (Access 2003)

    My main Access database is linked to five of the underlying tables that make up our front end system. I have Access queries in that database that create new tables in individual databases for each of our sales reps. The individual databases are the basis for Excel pivot tables. I have an Access macro that runs each of the queries in turn that create these individual database tables. Frankly, I'm not even sure that I need to run the Linked Table Manager before pushing out the data each day. But if I do need to run Linked Table Manager each time, I want to automate that process. My goal is to have one button for my back up person to click. I don't want them to have to go through the whole process.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: automate linked table manager process with a macro (Access 2003)

    <hr>My main Access database is linked to five of the underlying tables that make up our front end system.<hr>
    Are you saying that your database is piggybacked on your "front end system"? You should only need to run linked table manager if the linked database may be in a different location or you've changed the links somehow or the database containing the tables is unavailable. Do any of these apply? Network issues can make it prudent to refresh the links, so if that is the case, what would your backup person be expected to do about it?
    Charlotte

  5. #5
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automate linked table manager process with a macro (Access 2003)

    I don't think my databases are piggy-backed. I am simply pulling data from the front end database in order to do reporting. The canned reports we got with our system are not adequate for the detail we want, so we use their data to create our own reports.

    We rarely have network issues, but if we did, my back up person would have to notify IT and work with them and the Controller to get the queries re-pathed.

    It probably is not necessary to use the Linked Table Manager on a daily basis. That being the case, I think I'm good to go. However, I am in the process of comparing numbers to see if the pivot table reports accurately reflect current numbers without running Linked Table Manager. Believe it or not, this exchange has been helpful simply because I received your perspective on the problem. Thanks!

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: automate linked table manager process with a macro (Access 2003)

    I wasn't being critical. I was merely asking if your database was separate from the "front end". A well-designed front end doesn't usually contain tables at all, merely links, and the data should always be fresh as long as both front and back end are accessible. An issue might arise if those "tables" are something other than Access tables, such as xml files, Excel worksheets, or other flavors of database table.

    The Linked Table Manager doesn't refresh the data in the tables, it refreshes the connection to the tables. If your database contains links to tables local to the front end, it will refresh those links when you load. If you are linking to *linked* tables in the front end, the Linked Table Manager won't insure that the data is current because it only refreshes the link to the front end link.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •