How create an easy way to link to two backend tables in a different Access database file

I have two BE tables that I normally link to that the rest of the BE tables reside in.  But I'm trying to figure out how to use a form to link to the two tables, (they have the same names as the ones I'm normally linked to), instead of the ones within the working BE file.

I think I'm doing a terrible job trying to explain this one.

In other words, on an every day basis I have a FE file and a BE file.  The FE file contains linked tables to the BE file.  But in some cases I may want to link two of the tables to the two tables in a different BE file.

Confusing to say the least.
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

SteveL13Author Commented:
I should have say that I really don't want to have to use the Linked Table Manager in this case.  I want to use a form instead.
0
PatHartmanCommented:
Here's a form I use in all my apps.
Locate the new BE
Choose the file to replace and press the appropriate Relink button
Relink.JPGRelinkMultipleBEUsingFormONLY.zip
0

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
SteveL13Author Commented:
Pat.  Very interesting.  I imported the form, frmRelinkJetOrACETables and the query, qLinkedTablesList.  Then I opened the form and I can see the current DB file I am linked to in the field "Old DB Name".  And also I can browse to the new DB name.  

But I can't see where I can select only the two tables I want to link to in the New DB.  I definitely don't want to link to the entire new DB.  Just two of the tables.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PatHartmanCommented:
This form was designed to relink all tables linked to a specific BE to be easier to use than the Linked Tables Manager.  You change the query to show individual tables rather than grouping them all together.  But, if you do that, I would actually make a separate version of the form/query.  That way you can use either "all" or specific tables. The code would also need to change.  Probably your best bet is to open the Linked tables manager using a button.

Changed query:


SELECT IIf(Left([Connect],5)="Excel","Excel",IIf(Left([Connect],3)="DSN","SQL Server",IIf(IsNull([Connect]),"Access",IIf(Right([ForeignName],3)="txt","Text",IIf(Right([ForeignName],3)="csv","csv","unknown"))))) AS TableType, IIf(Left([Connect],3)="DSN",Mid([Connect],5,InStr([Connect],";")-5),[Database]) AS OldDBName, "" AS NewDBName, MSysObjects.Connect, MSysObjects.ForeignName AS FileName
FROM MSysObjects
WHERE (((MSysObjects.Database) Is Not Null)) OR (((MSysObjects.Connect) Is Not Null))
ORDER BY IIf(Left([Connect],5)="Excel","Excel",IIf(Left([Connect],3)="DSN","SQL Server",IIf(IsNull([Connect]),"Access",IIf(Right([ForeignName],3)="txt","Text",IIf(Right([ForeignName],3)="csv","csv","unknown"))))), IIf(Left([Connect],3)="DSN",Mid([Connect],5,InStr([Connect],";")-5),[Database]), MSysObjects.ForeignName, MSysObjects.Database;
0
SteveL13Author Commented:
Pat,

Am getting syntax error (missing operator) in query expression '"" AS NewDBName
0
PatHartmanCommented:
I posted the query because I modified it to see if i could easily change the database to work the way you want.  It is not simple or straightforward so I stopped at that point.  You will have less work over all if you just pop open the Linked tables manager

DoCmd.RunCommand acCmdLinkedTableManager
0
SteveL13Author Commented:
I was able to make it work by nesting the query in another query and setting the criteria for the two tables in that query.
0
PatHartmanCommented:
Glad you were able to make use of the form.
0
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.