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

SteveL13
SteveL13 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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.
Distinguished Expert 2017
Commented:
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

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
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;

Author

Commented:
Pat,

Am getting syntax error (missing operator) in query expression '"" AS NewDBName
Distinguished Expert 2017

Commented:
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

Author

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.
Distinguished Expert 2017

Commented:
Glad you were able to make use of the form.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial