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

Export linked table from MS Access to another Access file

I'd like to export a SQL Server linked table (view) to another MS Access file.  The new table should not be a linked table, but native in Access.

The basic process would be:
1- drop Access file if exists
2- create Access file
3- Export table/data from current Access file to new Access file
4- import data from attached table into native table
5- repeat for other tables

Here is what I have so far, but the result is an attached table

 Dim i As Integer
    
   
   Dim sDir As String
   Dim sFilename As String
   Dim sDirFile As String
   
   Dim sTables() As String
   Dim sExpTable() As String
   
   
   Dim ws As Workspace
   Dim db As Database
   
   
    ReDim sTables(0)
    ReDim sExpTable(0)
    
    ReDim Preserve sTables(3)
    ReDim Preserve sExpTable(3)
    
   sTables(1) = "dbo_v_pub_exp_mall_usa"
   sTables(2) = "dbo_v_pub_exp_stores_all_usa"
   sTables(3) = "dbo_v_pub_exp_stores_anchor_usa"
   
   sExpTable(1) = "mall_usa"
   sExpTable(2) = "stores_all_usa"
   sExpTable(3) = "stores_anchor_usa"
   
   'Get default Workspace
   Set ws = DBEngine.Workspaces(0)
   
   'Path and file name for new mdb file
   sDir = "C:\Projects\Direct\export-data-partners\exports\"
   sFilename = "NewDB.mdb"
   
   sDirFile = sDir & sFilename
   
   'Make sure there isn't already a file with the name of the new database
   If Dir(sDirFile) <> "" Then Kill sFilename
   
   'Create a new mdb file
   Set db = ws.CreateDatabase(sDirFile, dbLangGeneral)
   
    For i = 1 To UBound(sTables)
        'For lookup tables, export both table definition and data to new mdb file
        DoCmd.TransferDatabase acExport, "Microsoft Access", sDirFile, acTable, sTables(i), sExpTable(i) & "_imp", False
        
    Next i
   db.Close
   Set db = Nothing

Open in new window

0
dougfosterNYC
Asked:
dougfosterNYC
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
< Here is what I have so far, but the result is an attached table >

change this part

For i = 1 To UBound(sTables)
        'For lookup tables, export both table definition and data to new mdb file
        DoCmd.TransferDatabase acExport, "Microsoft Access", sDirFile, acTable, sTables(i), sExpTable(i) & "_imp", False
        
    Next i

Open in new window


with
   Dim sql As strimg
       For i = 1 To UBound(sTables)

        sql = "SELECT " & sTables(i) & ".* INTO " & sExpTable(i) & "_imp" & " IN '" & sDirFile & "'" _
                & " FROM " & sTables(i)

        CurrentDb.Execute sql
    Next i

Open in new window

0
 
dougfosterNYCAuthor Commented:
ahh, ok, i'll try this in the morning.. thanks.
0
 
dougfosterNYCAuthor Commented:
Thanks Rey, this worked perfectly.
0
 
dougfosterNYCAuthor Commented:
Thanks for writing the exact code change.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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