Using Transfer Database Macro from LInked Database

Posted on 2009-04-24
Last Modified: 2013-12-25
I have a database that exports and appends just the contents of the tables into another database and this is emailed and imported into Head Office.  It has worked perfectly for years.  However we have now split the original database and when we use the Transfer Database command in a macro (built into a button that the user just clicks), it transfers the link rather than the contents of the linked table.  I just cant seem to get my head around how to solve this.    

It would work perfectly if I did the macro from the Back End, but this is usually done by an End User who has no idea what a Back End is and I dont want them going near the Back End if I can help it.
Any help would be appreciated
Question by:AnnetteDavid
    LVL 65

    Expert Comment

    Do you know the list of tables you have? or do you iterate thru them?

    you can maybe trying opening the backend using access object then transferring

            Set acc = New Access.Application
            acc.OpenCurrentDatabase "c:\mybackenddb.mdb", False
            acc.DoCmd.TransferDatabase acexport, "Microsoft Access", "c:\myotherdb.mdb", acTable, "tablename", "tablename"
            Set acc = Nothing

    LVL 77

    Accepted Solution

    You will have to modify what you do.
    Instead of exporting the table, export a query based on the table.
    In your macro, use the query name as the source but leave everything else unchanged (it must still say Table as the objectType)

    Author Closing Comment

    Thank you so much, so simple when I look at it like this, thanks again

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now