Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag 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.
Avatar of SteveL13
Flag of United States of America image


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.
Avatar of PatHartman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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;

Am getting syntax error (missing operator) in query expression '"" AS NewDBName
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
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.
Glad you were able to make use of the form.