Tables starting with "f_" and ending with "_Data" RRS feed

  • Question

  • I'm writing a function that documents all the objects in my database and when I query MSysObjects with type = 1 I'm getting one table in all my databases that has a name of "f_xxxxxxxxx_Data" (x's are random alphanumeric chars). I filter out msys tables and tables starting with ~ but I haven't run into these "f_" tables before and wondered if someone could shed some light on them. (Granted, I've just started working in ACCDB's) If they are just another hidden or system table, can I filter on Left(varTableName,2) = "f_" and Right(varTableName,5) = "_Data" reliably? In other words, is this naming convention (minus the alphanumeric in the middle) always the same? Hopefully someone can clear this up!
    Wednesday, December 28, 2016 5:29 AM

All replies

  • Hi, I'm not in front of a computer right now to check but I believe those could be the hidden tables used for multivalue and attachment fields. Just my 2 cents...
    Wednesday, December 28, 2016 6:29 AM
  • Hi

    I had tables like this too. Dirk Goldgar found the reason:

    These tables are used to store the multiple values of the multivalue fields.

    If you open a TestDatabase with a table with multivalue fields and then look in MSysObjects, the "weirdly named" table is there.

    I you then open the table in design view and delete the multivalue field.  Then look in MSysObjects again, the "weirdly named" table is no longer there.

    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    • Edited by ForssPeterNova Wednesday, December 28, 2016 6:36 AM
    • Proposed as answer by ForssPeterNova Wednesday, December 28, 2016 3:16 PM
    • Unproposed as answer by ForssPeterNova Friday, January 13, 2017 10:24 AM
    • Proposed as answer by ForssPeterNova Friday, January 13, 2017 10:24 AM
    Wednesday, December 28, 2016 6:34 AM
  • The above comment isn't quite correct.

    Every Access ACCDB db has a hidden / system F_..._Data table which is created automatically when new databases are created

    MDB files don't have them but they also don't have multivalue fields

    If you convert an MDB to ACCDB, the table is immediately created

    I NEVER use multivalue or attachment fields but I still have the table which cannot be edited/deleted or viewed.

    The name of the table is unique to each database.

    However, all have Type = 1 (local table) and Flags = -2146828288 in MSysObjects

    That Flags value isn't used by any other Access table so you can use that to exclude it from any table list

    • Edited by isladogs52 Sunday, May 6, 2018 12:50 PM Grammar
    • Proposed as answer by isladogs52 Saturday, October 24, 2020 8:44 AM
    Sunday, May 6, 2018 2:54 AM