Connect two databases without changing the table names

[Pat Hartman]

@Falcon88 while likes are nice, experts don't use them to keep score and they really don't help anyone who finds a thread later. There is no need to like every reply. If you decide to use the advice I offered, it would be helpful if you said that and we could move on to help you.

 

[MajP]

I never have really done this and suprised this works as well as it does. But it is doable and pretty simple.
As people have mentioned there are probably much better solutions to normalize the data or add a relinker to switch backends.

However you can open forms in the main database from either external database as if they are in the main database. For some reason I am unable to open two forms from different databases at the same time. I have to close one to open the other.

You can open queries from both databases at the same time and they appear as if they are local to the main database. You could do the same for reports. In all cases the forms, queries, and reports will use the data from whichever external db you call.

I have a main database and two external databases.
MainDB
External1
External2

1. Rename the main database Code project the same way you would rename a module. If not it defaults to "Database" and you cannot import other project references.
2 Add a reference using the Tools-References like you would add any reference, but pick your External1.
3. Rename the External1 Project from "Database" to "External1"
4. Do the same for External2
5. Add this code in a module for each External

Public Function openForm(formName, Optional View As AcFormView = acNormal, Optional filterName, Optional whereCOndition, Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, Optional windowMode As AcWindowMode = acWindowNormal, Optional openArgs) As Access.Form
    DoCmd.openForm formName, View, filterName, whereCOndition, DataMode, windowMode, openArgs
    Set openForm = Forms(formName)
End Function
Public Function openQuery(QueryName As String, Optional View As AcView = acViewNormal, Optional DataMode As AcOpenDataMode = acEdit)
    DoCmd.openQuery QueryName, View, DataMode
End Function

To open a form or query in one of the externals

Private Sub cmdExt1_Frm_Click()
external1.OpenForm "frmProductList"
End Sub

Private Sub cmdExt1_Query_Click()
  external1.OpenQuery "qryGreaterThan100"
End Sub

Private Sub cmdExt2_Frm_Click()
external2.OpenForm "frmProductList"
End Sub

Private Sub cmdExt2_Query_Click()
  external2.OpenQuery "qryGreaterThan100"
End Sub

You simply use the Projectname.ProcedureName to call the procedure

Notice how MainDb no longer says "Database (MainDb)"

Two queries with same name but different data from different databases loaded in Main

You can give the demo a try, but I do not know if the references will stay if the files are in a new directory. May need to re-add the references to the external DBs.