Consulting

Results 1 to 2 of 2

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

  1. #1
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    1
    Location

    Angry 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:
    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

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Try changing the code like this:

    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).
    Be as you wish to seem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •