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?
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
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.