CODEWITHSUNDEEP
ms-accessvbams-access-2007
Katana24
Katana24

Reputation: 8959

Where are these tables coming from? Access VBA

I have created a procedure that stores the names of all the tables in an external database inside an array. The reason for this is that, ultimately, I will be using this as a reference point for determining what tables need to be relinked.

The code below returns a total of 13 tables:

For Each tb In db.TableDefs

    If Left(tb.Name, 4) <> "MSys" Then
        'Store these accepted table names in an array
        astrTableNames(intArryPosition) = tb.Name
        intArryPosition = intArryPosition + 1
    End If

Next tb

and stores their names in an array. Here is a list of the results, when I print the array:

1: DispenseStaging
2: DispenseSummary_All
3: DrugBrand
4: NDC
5: Programs
6: StateCodes
7: StoreInfo
8: tblCompany
9: tblGetProgramDispense
10: Users
11: Users1
12: Version
13: Zipcodes

Here's the problem - when I open the database - it only has 4 tables. There are no more - no references to links or anything.

So where are these tables actually coming from? Does this mean that they were once there but then were deleted but the reference is still there?

Thanks

P.S. This is the procedure I'm using to print the array:

'Procedure to print the contents of a string array
 Public Sub PrintArrayContents(ArryStrg() As String)

    Dim i As Integer

    For i = LBound(ArryStrg) To UBound(ArryStrg)
        Debug.Print i & ": "; ArryStrg(i)
    Next i

End Sub

Upvotes: 0

Views: 148

Answers (3)

Katana24
Katana24

Reputation: 8959

Just thought I'd add this answer to cover MS Access 2003. To view hidden objects, do the following:

  1. Tools
  2. Options
  3. Select the View tab
  4. Under the Show option, check the 'Hidden Objects' option

You should now beable to see hidden tables etc in the database.

Upvotes: 0

user2299169
user2299169

Reputation:

Those tables can be garbage or linked tables invisble on UI as either link is broken or garbage and linked tables at the same time.
Try a "compact and repair" on the DB (backup DB file first!) and re-exec your function to see if you get the same result set. If you do, read table properties' to try to figure out where the tables belong to and what are they (linked or base table). Check LastUpdated, Updatable, SourceTableName, maybe RecordCount properties to get some info about the weirdos :)
If you still can't see what's going on, first, read system tables to figure out the "extra tables'" meta-data (e.g. MSysObjects tells you the obj.type which can help) and second, try to execute a query against those tables, values -or error- returned may inform you about where those guys belong to.
I doubt but it's possible (as it's access :P) that some tables are just "hidden". You can turn on/off 'show hidden tables' in Navigation pane.

Please come back with your findings, I'm very curious about the results.

Upvotes: 1

Roger Rowland
Roger Rowland

Reputation: 26259

These are probably hidden tables.

You can display them in Access 2007 by going to the Navigation pane, then right-click on the All Access Objects and select Navigation options.

That will open a dialog for you to show hidden objects.

Upvotes: 2

Related Questions