• 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?
0
TimHudspith
Asked:
TimHudspith
  • 2
  • 2
1 Solution
 
als315Commented:
You can use query (create table) for exporting tables from backend.
0
 
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
    '
    rst.Close
    Set rst = Nothing
  End If
 
  dbs.Close
  Set dbs = Nothing
  Set wks = Nothing
  
End Function

Open in new window

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

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

Open in new window

0
 
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.

/gustav
0

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