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 .

 

[Pat Hartman]

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

There isn't because this is not a relational design. It is a spreadsheet design.

There are two potential solutions.

1. If you never want to use the data from multiple BEs at one time, then use a single FE and relink the tables. It doesn't matter what the name of the database is, as long as the table names are identical in the two versions, you can link to 2023 when you want to see data for 2023 and you can link to 2024 when you want to see data for 2024. No coding or object changes are required.

2. If you want to be able to use the data from the two BEs at the same time, then you need to actually fix the design problem. To do that, you will need to merge the two databases. If there are relationships, this can be a bit complicated but you can still do it in a fairly short amount of time. But, before you merge the data, you need to modify your queries so that you can use forms with date fields to filter the data. That way you can get one year or multiple years depending on the start and end dates in your parameters.

I would fix the problem. You seem to have two years now. Next year, there will be three unless you decide to throw away the application due to the design flaw. We can give you details and help you to make a plan to merge the data. It is a one time effort but since it is not trivial, you probably want to automate it so you will build append queries that are controlled by VBA so that they run in the correct order and you don't miss anything. One thing that will make the conversion easier is to assign new PKs and FKs for the merged database. But, to facilitate the merge, you will need two additional columns temporarily. You need the source year and the old PK/FK. Once the conversion is complete and you're sure everything is accounted for - reports that run in the old db are identical to reports run from the new db using date ranges, then you can remove the two temp columns and you're good to go. One BE that holds ALL the data now and forever

 

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