• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

Exporting Tables from Access

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?
  • 2
  • 2
1 Solution
You can use query (create table) for exporting tables from backend.
Gustav BrockCIOCommented:
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.

TimHudspithAuthor Commented:
Thanks, but can you be more specific?
Mytable - linked backend table:
SELECT * INTO MyTable IN 'C:\export\DBbexp.accdb'
FROM MyTable;

Open in new window

Gustav BrockCIOCommented:
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.


Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now