Results 1 to 9 of 9

Linked Table - The Microsoft Access database engine could not find the object "tbl****".

  1. #1
    Shortmeister1 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    London
    Posts
    5

    Linked Table - The Microsoft Access database engine could not find the object "tbl****".

    Hi there.



    First time caller!

    I have a peculiar problem and I'm stumped.

    I'm writing a training database for a local charity that works in drug rehabilitation and training. For a whole load of reasons, Access is the preferred option over SQL server, so quickly reminded myself how to think Access. Since this isn't truly a professional project (for me), I've been developing it at home on my Windows 10 PC with Access 2016. There shouldn't be any permission issues, but...

    I have split my Access database into a FE and BE.

    It worked fine in development, but I asked a friend to test it and gave him a slightly redacted version of the BE (thinking GDPR!) and that's when the problems started; when I relinked the tables.

    The linked tables are all fully accessible in the FE (and the BE) but running the following code does something really weird...

    Code:
    DoCmd.TransferSpreadsheet acExport, 10, "tblStudents", CurrentProject.Path & "\TCERD" & CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date)), True
    DoCmd.TransferSpreadsheet acExport, 10, "tblCourseRecord", CurrentProject.Path & "\TCERD" & CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date)), True
    DoCmd.TransferSpreadsheet acExport, 10, "tlkpCourse", CurrentProject.Path & "\TCERD" & CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date)), True
    DoCmd.TransferSpreadsheet acExport, 10, "tlkpGender", CurrentProject.Path & "\TCERD" & CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date)), True
    DoCmd.TransferSpreadsheet acExport, 10, "tblDummyStudentsMap", CurrentProject.Path & "\TCERD" & CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date)), True
    The first two lines export perfectly but the following three throw an error - "The Microsoft Access database engine could not find the object "tlkp****". I don't get it!

    I also note that trying to Compact and Repair the BE throws an error itself - "Could not use D:\********\ERD_BE.accdb; file already in use.

    Lots of searches later and I give up. You all seem like nice people - any help greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,675
    does "tlkpCourse" and "tlkpGender" exist and can be opened?

    also
    CurrentProject.Path & "\TCERD" & CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date))

    can be shown as:
    CurrentProject.Path & "\TCERD" & format(date,"yyyymmdd")

  3. #3
    Shortmeister1 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    London
    Posts
    5
    Absolutely "tlkpCourse" and "tlkpGender" exist (as linked tables) and can be opened in the FE.

    Good point about format! I think I was tired....

  4. #4
    Shortmeister1 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    London
    Posts
    5
    Also to reiterate, there's no difference between them and "tblStudents" and "tblCourseRecord", which is what makes this so strange.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,617
    A Decompile/Compile process corrected some oddball issues for me. No idea if that will help you.

    Otherwise, would need to have both files for analysis and testing. To provide files, follow instructions at bottom of my post.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  6. #6
    Shortmeister1 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    London
    Posts
    5
    I'd never used decompile and had to look it up - http://datagnostics.com/dtips/decompile.html

    Worked like a dream. Thank you very much.

    Incidentally, it then threw up a second error "3274 External Table is not in the expected format". This randomly affected one or many of the export lines of code; different each time.

    I solved it by adding the following error handler to force it to retry the code up to 10 times before giving up and exiting the sub.

    Code:
    cmdExportTables_Click_Err:    i = i + 1
        Debug.Print i
        If i < 10 Then
            Resume
        End If
    
     MsgBox Err.Description & vbCr & Err.Number
    If anyone thinks my error handling is pants and there's a better way to handle race conditions, I'm all ears...!

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,617
    According the MS: "The format of the table has changed since it was linked. Link the table again, and then retry the operation."
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  8. #8
    Shortmeister1 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    London
    Posts
    5
    That was the first thing I did!

    It was a race error with a crappy error message.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,617
    Don't know what you mean by 'race' error.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-30-2018, 08:18 AM
  2. Replies: 13
    Last Post: 01-26-2018, 12:27 PM
  3. Replies: 5
    Last Post: 10-27-2014, 06:59 PM
  4. Replies: 1
    Last Post: 04-24-2014, 02:49 AM
  5. Replies: 1
    Last Post: 09-03-2011, 06:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums