Import Excel Sheet to Back-End Database

Question

I have an application that is split; forms, queries and code in FrontEnd.mdb and the data tables in BackEnd.mdb. When I import an Excel spreadsheet in code using DoCmd.TransferSpreadsheet, it puts the table in the FrontEnd.mdb. I know I can the the table the BackEnd.mdb and then delete it from FrontEnd.mdb, but it is a large table and it explodes the size of my FrontEnd.mdb. How do I import so the new table goes directly into the BackEnd.mdb?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: Microsoft Access Database Discussion List <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Import to Back-End Database
Date: 5 May 1998 14:22

Jeff,

I think you can use this code fragment (Acc97) to solve your task:

Dim dbs As Database
Dim strSql As String
Dim strBackEndMdbPath As String
Dim strImportedTableName As String
Dim strWorkBookPath As String
Dim strWorkSheetName As String
    
strBackEndMdbPath = "c:\temp\db2.mdb"
strImportedTableName = "Sheet1"
strWorkBookPath = "c:\temp\Book1_test.xls"
strWorkSheetName = "Sheet1"
    
Set dbs = DBEngine(0).OpenDatabase(strBackEndMdbPath)
strSql = "Select * into [" & strImportedTableName & "] from [" & _
             strWorkSheetName & "$] in '' [" & "Excel 8.0;" & "HDR=YES;IMEX=2;DATABASE=" & _
             strWorkBookPath & "]"
    
On Error Resume Next
dbs.TableDefs.Delete strImportedTableName
dbs.TableDefs.Refresh
    
On Error GoTo 0
dbs.Execute strSql, dbFailOnError

HTH,
Shamil