Hi Sir's Good Day I have a 3 Ms Access DB named DB2019, DB2020 & DB2021 they are all identical in Tables, queries forms, reports etc..
what i want to achieve is it possible to pull out Data, example those 3 DB have a Search Form if im in DB2021 and use that Search Form i can only Search the Data that is in DB2021. what i need is i can also search Data from DB2020 and DB2019 while im in DB2021 SearchForm and Also Vice Versa....
is there a way to connect those 3 using a Query or something?
ive been banging my head in the past week thinking how to do this hehe Please Help... Thank you..
Why? Why do you have three databases of the same thing. Is each database near 2 Gig?
If there is a legitimate reason for this then make a single front end and link to the tables in the three databases. Then use Union queries on to search through all records.
Please explain the why?
There's no reason all that data should be in 3 seperate databases. Year is just a piece of data--if its relevant it shouldn't be stored in a database name, but in a field. If you are building a sales database you don't have a seperate database for every product you sell--you have a field in your Sales table which lets you know which product was sold. Same principle with year.
You need to get all your databases' data into 1 database. Do that and your Search form will work with all your data.
I find it interesting how people commonly see time as being non-contiguous, or that it is a distinctly different kind of stuff than other scalar data points. Nobody builds a separate table for financial transactions in excess of $1000, for instance, or a separate table for temperatures that fall below zero. But add one second to 12/31/2020 23:59:59, and it is not uncommon that people want to store everything that follows in a completely different container.
I fully concur with the other responders, this should all be in one db, all you are doing is building a headache for yourself. But to answer this question to get you out of a hole for now or this is a one off, the following should work to list records if you are only talking about one table. If more than one and tables are related then probably won't work
Assume your table is called 'tblData'
1. in the db2021 database, create a linked table to tblData in each of the other db's - when created they will have a numeric suffix added
2. create a union query along these lines (this assumes you have worked backwards through the years when linking
Code:
SELECT 2021 as dbYear, * FROM tblData
UNION ALL SELECT 2020 as dbYear, * FROM tblData_1
UNION ALL SELECT 2019 as dbYear,* FROM tblData_2
etc
Another option that will apply indexing is to ally your search criteria to each element of the union query
Code:
SELECT 2021 as dbYear, * FROM tblData WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2020 as dbYear, * FROM tblData_1 WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2019 as dbYear,* FROM tblData_2 WHERE somefield=forms!myform!searchcombo
etc
A way that does not involve linked tables is to use a union query with the path to the db - something like
Code:
SELECT *
FROM tblData
UNION ALL SELECT *
FROM tblData IN 'folderpath\db2020.accdb'
UNION ALL SELECT *
FROM tblData IN 'folderpath\db2019.accdb'
etc
or
Code:
SELECT 2021 as dbYear, *
FROM tblData
UNION ALL SELECT 2020 as dbYear, *
FROM [MS Access;DATABASE=folderpath\db2020.accdb].tblData
UNION ALL SELECT 2019 as dbYear, *
FROM [MS Access;DATABASE=folderpath\db2019.accdb].tblData
I'll leave you to adapt to allow the use of indexing
In any event, having found your records, what are you going to do? Open another form to display all the information? If all in one db, not a problem, but now which table is the recordsource going to be?
Don't agree - will work for any single table. But going into a more complex area you can include joins to bring both records through providing you use one of the two versions that use the IN method
for example
Code:
SELECT 2021 as dbYear, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK
UNION ALL SELECT 2020, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2020.accdb]
UNION ALL SELECT 2019, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2019.accdb]
and you can still include a where clause
Code:
SELECT 2021 as dbYear, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2020, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2020.accdb] WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2019, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2019.accdb] WHERE somefield=forms!myform!searchcombo
We don't know what the OP's data looks like, whether it is normalised or not and clearly if the user is in 2019 the query would be structured differently to provide the same result
agree there are duplicate parent records but the only reason for including a join within the union query is to interrogate the child record and be able to reference the parent. As I stated it is to get out of a hole quickly, it is not a permanent or ideal solution - which is to combine the data into one db.
Thank you so much Guys i just temporarily solve this problem of mine by linking the previous DB tables from the current DB then use a Union query. i appreciate all your help if have another question please help me again hehe thanks again take care be safe always God Bless!
