Access 07 VBA Get Table by Path

Question

Is it possible to reference (in VBA) a non-linked Access table by its full path?

For instance, say you're building a form that draws from a set of tables, but your user needs to add tables as time goes on; you might start with tableA, tableB, and tableC, but a year down the line tableZ might exist.

The goal then becomes finding a way to reference the newly-added tables without needing to add them as external data sources if possible; [how] can this be done? (My particular case involves using the tables as RowSource values, if that's significant.)

I was thinking something along the lines of

control1.RowSource = "X:\database\databaseName.accdb" & [???]

might work, but I really have no idea what would go in the brackets.

Answer 1

Within an Access query, there are 2 ways to reference an unlinked table in a different Access database.

SELECT YourTable.some_field
FROM YourTable IN 'X:\database\databaseName.accdb';

or ...

SELECT YourTable.some_field
FROM [X:\database\databaseName.accdb].YourTable;

I think you can get what you want if you use a similar query as your RowSource. (Control.RowSource = "SELECT ...")