We help IT Professionals succeed at work.

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

SteveL13
SteveL13 asked
on
92 Views
Last Modified: 2017-04-13
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

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.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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

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