Avatar of SteveL13
SteveL13
Flag for United States of America asked on

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.
Microsoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
SteveL13

ASKER
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.
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SteveL13

ASKER
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.
PatHartman

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;
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SteveL13

ASKER
Pat,

Am getting syntax error (missing operator) in query expression '"" AS NewDBName
PatHartman

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
SteveL13

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

Glad you were able to make use of the form.