asked on

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?

als315

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

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
    '
    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

TimHudspith

ASKER

Thanks, but can you be more specific?

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

/gustav