(500 pts) Determine which import spec is being used on a linked table

Hello all,
Does anyone know of a way to find out which import spec is being used on a linked table?  Basically I have an existing Access 2003 database with several previously linked tables in it.  Apparently whoever did the initial linking tried several iterations of creating input specs until they got the right one.  They neglected to delete the unused specs, so now if I need to re-link the file for any reason, it takes a ton of time determining which spec to use.  Any help would be greatly appreciated and rewarded with 500pts in hopes of a quick resolution.
Thanks,
Dave
LVL 2
Cristal638Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dannywarehamCommented:
When you import or link using a specification, the specification is "stored" in a system table.
Click Tools, Options, click View tab, check System Objects.
When you now look at Tables, you should see a table - MSysIMEXSpecs.

If you don't, then the originaldb  builder must have taken it with them.
jerryb30Commented:
If the linking was done via code, the spec should be indicated.  Even if the code is not active, it may still exist.   Go to module tab, click on code button, and do a find in project for 'link', indicating a link doing vba.

Or, create a query joining msysimexspecs to mysysimexcollumns on SPECID, so you can see which specs most closely match what you want to see.
Cristal638Author Commented:
The system table is full of specs all having similar names, such as:

CD Openorders Link Specification
CD Openorders Link Specification1
CD Openorders Link Specification2
CD Openorders Link SpecificationA
and so on and on.  That's the problem.  If for some reason you need to re-link the file, you have to figure out which of the Spec's is the right one.  I just thought that there must be somewhere that says the XYZ linked table is using the XYZ input spec.

As for Jerry's comment, the link tables were not linked via code.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jerryb30Commented:
Well ,thee link spec names by default include the linked table name, but that is alterable.  Sounds like 1 & 2 were just added on by trying to copy the spec from one database to another.  
The spec date can be inferred, perhaps, by the specID
If you link the table, go into msysobjects, and find the name of the linked table.
It will tell you in the connect column which spec is being used.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cristal638Author Commented:
Thanks Jerry...

Here's what I did:
Using the MSysObjects system table I wrote a query that contains the following and filtered out connect not null to get only the linked tables with input specs associated to them.

Name = the name of the linked table
ForeignName = the source file
Connect = the Input spec used

For more info on the MSysObjects table goto: http://www.accessdatabasetips.com/msysobjects.html
jerryb30Commented:
Glad to help.  I like your solution in the query.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.