asked on

I would like to know the source of my access table

Hi,

I have an access database with tables created from different data sources like excel spreadsheet or other access tables. The access database was created sometime back. Now I am trying to find a way to find the source of the tables created in the database. Is there a way to find it?

Thanks

ASKER CERTIFIED SOLUTION

Paul MacDonald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

If the data was imported into Access tables, there is no way to determine the source of the data. If the tables are linked from some other data source, you should be able to determine the source of the data several ways.

1. Mouse over the linked table in the database window.

2. look at the tables Connect property in the tabledef object:

currentdb.TableDefs("YourTableName").connect

3. Look in the system table (mSysObjects). Easiest way to do this is in a query:

SELECT Name, Connect, Database, Type from mSysObjects
WHERE Database IS NOT NULL

Jeffrey Coachman

Dale,

I usually just pull
WHERE Type=6 (...Linked tables)

Is: WHERE Database IS NOT NULL
...more full proof?

...Just for my own info...
;-)

Jeff

Dale Fye

Jeff,

I used to use Type = 6 (eons ago).

Then I read something in one of the Access magazines that indicated that there was no guarantee that MS would not change the values or the meanings of the values in the Type column, and it recommended just using the [Database] IS NOT NULL syntax; so I changed.

I've never had it fail on me, so I've never switched back.

Jeffrey Coachman

thanks for the info...