Hi
I have seen the following post which works great.
https://www.accessforums.net/showthr...+access+tables
Code:
Private Sub cmdbImportData_Click()
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String
Dim strPathFile As String
Dim oTable As DAO.TableDef
On Error GoTo ImportXLSheetsAsTables_Error
strPathFile = "C:\BP-RADS-One-Source_LIVE.xlsx"
CurrentDb.Execute "DELETE * FROM Tbl_Apr 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_May 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Jun 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Jul 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Aug 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Sep 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Oct 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Nov 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Dec 2016;"
CurrentDb.Execute "DELETE * FROM Tbl_Jan 2017;"
CurrentDb.Execute "DELETE * FROM Tbl_Feb 2017;"
CurrentDb.Execute "DELETE * FROM Tbl_Mar 2017;"
Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open(strPathFile)
For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_" & sh.Name, strPathFile, True, sh.Name & "!"
Next
wb.Close
appExcel.Quit
For Each oTable In CurrentDb.TableDefs 'Lets Clean up the tables.
If oTable.Name Like "*ImportErrors*" Then
CurrentDb.TableDefs.Delete oTable.Name
End If
Next oTable
MsgBox "12 months RADS Tables created."
On Error GoTo 0
Exit Sub
ImportXLSheetsAsTables_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub
The problem is I have a split Db and when I run this Macro the Tbl_..... are created in the front end and not the back end.
I can not run the macro in the BackEnd as I don't want anyone to access it owing to locking issues. The macro code has to live in the front and and populate backend tables.
Another most important twist is that the tables then need to be linked into the front end. I suppose I can do that as a one off.
I should imagine that I need to point to the BackEnd Db in order to create Tables but not quite sure syntactically this can be done ?
Would appreciate some guidance.
Thanks in advance