Connect two databases without changing the table names

[Falcon88]

I have an Access database named HTBSD2023 and another HTBSD2024.they contains lots of the same tables, The problem is that I want to link to the first database by importing tables. The problem is that the names of the tables in the two databases are the same, which affects the dependencies of the linked objects. Is there a way to view the data of the two databases in another way?

 

[theDBguy]

In Access, table names have to be unique. I don't understand what you mean by object dependencies are being affected. What do you mean by that?

If you use SQL Server, you can store the tables in separate schemas. You would then be able to link to them in Access without creating duplicate table names.

 

[MajP]

I don't understand what you mean by object dependencies are being affected. What do you mean by that?

My interpretation of that is you have queries and rowsources throughout the DB like
Select * from Table1

So all of those would have to be duplicated
Select * from Table1_2023 And Select * from Table1_2024.

 

[MajP]

You could add the two databases as references to the main database. And from the main database open forms in the two other databases. This assumes these are complete databases and not just backends with tables. This way from the main you can look at data in the two other databases.

The other question is can you simply union the tables? May not be possible if you repeated foreign keys.

 

[Falcon88]

You could add the two databases as references to the main database. And from the main database open forms in the two other databases. This assumes these are complete databases and not just backends with tables. This way from the main you can look at data in the two other databases.

The other question is can you simply union the tables? May not be possible if you repeated foreign keys.

Please gives me an example or sample db.

 

[LarryE]

I have an Access database named HTBSD2023 and another HTBSD2024.they contains lots of the same tables, The problem is that I want to link to the first database by importing tables. The problem is that the names of the tables in the two databases are the same, which affects the dependencies of the linked objects. Is there a way to view the data of the two databases in another way?

Are you creating a new HTBSD database for each year?

 

[isladogs]

I have an Access database named HTBSD2023 and another HTBSD2024.they contains lots of the same tables, The problem is that I want to link to the first database by importing tables. The problem is that the names of the tables in the two databases are the same, which affects the dependencies of the linked objects. Is there a way to view the data of the two databases in another way?

Use aliases for the linked table names in one or both databases

 

[Josef P.]

Or access the file directly in the query:

select * from [;Database=C:\your\data\HTBSD2023.accdb].TableName

 

[Falcon88]

You could add the two databases as references to the main database. And from the main database open forms in the two other databases. This assumes these are complete databases and not just backends with tables. This way from the main you can look at data in the two other databases.

The other question is can you simply union the tables? May not be possible if you repeated foreign keys.

Please gives me an example or sample db

 

[Falcon88]

Is there a way to build form to choose the database that is the data source db. By choosing the year ?
But what if the user needs to exploring all ?

 

[Gasman]

Please gives me an example or sample db

You have just been given one in post #8 ?

 

[MarkK]

If you have one purpose, and two databases that distinguish contained data by year, you have a fundamental design problem.

 

[GPGeorge]

Please gives me an example or sample db

YOU own the two databases in question and the problem is all about working with those two databases. It might be possible for someone to spend some time creating "sample" databases with some similarities to yours, and by "some time" I mean a few hours and a lot of guess work.

Could you upload your accdbs? That would make it more feasible to try to work out a solution on your behalf, using the actual databases themselves/

 

[Falcon88]

YOU own the two databases in question and the problem is all about working with those two databases. It might be possible for someone to spend some time creating "sample" databases with some similarities to yours, and by "some time" I mean a few hours and a lot of guess work.

Could you upload your accdbs? That would make it more feasible to try to work out a solution on your behalf, using the actual databases themselves/

I mean if there a ready to use sample or a url explain that idea, you can support me with .

 

[LarryE]

If you can upload just the 2023 database, we could look at your design structure and make better recommendations.

 

[Falcon88]

I couldn't able to upload

 

[LarryE]

I couldn't able to upload

Do you know how to create relationships between tables using Primary Keys and Foreign Keys?

 

[Falcon88]

Do you know how to create relationships between tables using Primary Keys and Foreign Keys?

Of course .

 

[LarryE]

Of course .

Then create a Data_Year table and create a Foreign Key relationship between that table and the appropriate table in your 20023 database. Once that is done, you can import all the data from your 2024 tables into the appropriate 2023 database tables. Update the new imported data with the 2024 Data_Year Primary Key. You should have one database with two years of data. In 2025, create a new year and go on from there.

 

[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.