Exporting Tables from Access

Posted on 2012-08-20
Last Modified: 2012-08-21
Using VBA I want to create a procedure that creates a new database and exports some tables, forms and VBA modules. The problem is that some of the tables are linked in from another Access db acting as a back-end.

Given that this procedure is running from the front-end, how do I export these tables, other than creating an export procedure in the backend database and calling it from the front-end?
Question by:TimHudspith
    LVL 39

    Expert Comment

    You can use query (create table) for exporting tables from backend.
    LVL 48

    Expert Comment

    by:Gustav Brock
    No, it is much easier to open the database of the linked table.
    This function is for running a Seek on a linked table:
    Function SeekTable()
      Const cstrTable As String = "tblValue01"
      Const cstrAttached As String = ";DATABASE="
      Dim wks As Workspace
      Dim dbs As Database
      Dim tdf As TableDef
      Dim rst As Recordset
      Dim strConnect As String
      Dim strTablename As String
      Set wks = DBEngine(0)
      Set dbs = wks(0)
      Set tdf = dbs.TableDefs(cstrTable)
      strConnect = tdf.Connect
      strTablename = tdf.SourceTableName
      Set tdf = Nothing
      If InStr(1, strConnect, cstrAttached, vbBinaryCompare) = 1 Then
        strConnect = Mid(strConnect, Len(cstrAttached) + 1)
        ' Open database shared and read-only.
        Set dbs = wks.OpenDatabase(strConnect, False, True)
        Set rst = dbs.OpenRecordset(strTablename)
        ' Perform Seek operation. Example.
        rst.index = "ID"
        rst.Seek "=", 10010
        Debug.Print rst!Value
        Set rst = Nothing
      End If
      Set dbs = Nothing
      Set wks = Nothing
    End Function

    Open in new window

    You modify this to pick the tdf object and export it.


    Author Comment

    Thanks, but can you be more specific?
    LVL 39

    Accepted Solution

    Mytable - linked backend table:
    SELECT * INTO MyTable IN 'C:\export\DBbexp.accdb'
    FROM MyTable;

    Open in new window

    LVL 48

    Expert Comment

    by:Gustav Brock
    Look up the on-line help on:

    Workspace.CreateDatabase Method

    Then study:

    DoCmd.TransferDatabase Method

    on how to export objects (acTable) from your current or your linked database (the function above) to the new database.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now