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

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!

Answer 1

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...

Answer 2

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

Answer 3

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