I'm a terrible self-taught access administrator.
I developed a DB that works off imported data from an excel worksheet that is actively wrote to - so it cannot be linked otherwise it has caused issues with the Excel table auto-write/save.
To circumvent this I used the code below - which works beautifully - until I split the DB into a front-end and a back-end and now I'm royally hooped with a deadline. Aye aye aye.
If anyone can offer any guidance I'd be extremely grateful.
Option Compare Database
Public Sub UpdatePDFs()
Dim strSql As String
Dim n As Integer
Dim db As DAO.Database
Set db = CurrentDb
'Update SARF Table
DoCmd.RunSQL "DELETE tblSARFSubmissions.* FROM tblSARFSubmissions;"
DoCmd.TransferSpreadsheet acImport, , "tblSARFSubmissions", "xyzfilepath\exceldoc.xlsm", True, Table1
DoCmd.SetWarnings True
For n = db.TableDefs.Count - 1 To 0 Step -1
' loop through all tables
If InStr(1, db.TableDefs(n).Name, "ImportError") > 0 Then
' if table is import errors table
DoCmd.DeleteObject acTable, db.TableDefs(n).Name
' delete table
End If
Next n