Copying Excel Tracker into Access - Endless Issues with INSERT INTO statement

Hello everyone :)

My colleagues use an Excel tracker to track tickets they are resolving and I want to implement a system where they can all upload their trackers to a central access database stored in a shared folder.
It's not doing anything fancy, all of the fields in the excel tracker are replicated in the access database, it's literally just copying the data across.
I have the macro set up and it works fine for a little while, but them seemingly for no reason it stops working with the error message:

Run-time error '-2147217900 (80040e14)':
The INSERT INTO statement contains the following unknown field name: 'F13'. Make sure you have typed the name correctly, and try the operation again.

I have verified time and time again that all of the fields in my excel table match up with the fields in my Access DB and are spelled correctly (in fact I don't even have a field name 'F13' in either the excel table or the DB, and if I introduce one to satisfy the error message it instead says there is an unknown field name 'F14', and so on), and strangely the exact same macro works again if I copy and paste the code into a new excel document, but then encounters the same issue again after it is run a few times.


Here is my code:

Code:

---

Public Sub UploadToAccessDB()

'Skip operation if table contains no data
If ActiveSheet.ListObjects(1).ListRows.Count = 1 Then
    MsgBox ("There is no data to upload")
    GoTo Skip
    Else
    End If
     

'Import tracker table rows to access
  Set cn = CreateObject("ADODB.Connection")
  dbPath = " Redacted to protect the innocent :-P "                'filepath of target access DB
  dbWb = Application.ActiveWorkbook.FullName
  dbWs = Application.ActiveSheet.Name
  scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
  dsh = "[" & Application.ActiveSheet.Name & "$]"
  cn.Open scn


  ssql = "Tracker ([Ticket URL], [Item / Reason], [Date Created], [Date Resolved / HandOff], [Handed Off to], [Keeper's Login], [Category], [Site], [Processing Time], [Tracker Upload Date], [Uploaded By], [Team] ) "      'Field names from tracker
  ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh


  cn.Execute ssql            ' < Where the error is occurring


MsgBox ("Tracker uploaded to database")


Skip:

End Sub

---

Due to the inconsistent nature of the problem I think it's a bug in Access, does anybody know of a workaround?

My Database is an Access 2007 - 2016 file format (.accdb), and the excel tracker is Excel Macro Enabled Format (.xlsm), both are from the same Office Professional Plus 2016 product.

Thanks in advance for any help

Try changing the code like this:

Code:

---

dsh = "[" & Application.ActiveSheet.Name & "$" & ActiveSheet.ListObjects(1).range.address(0, 0) & "]"

---

Technically you should also use 'Excel 12.0 Macro' and not 'Excel 8.0', but since the workbook is open it shouldn't matter. (Although using ADO against an open workbook is known to cause memory leaks and should be avoided really).