Solved Linked Backend (1 Viewer)

[Eddie Mason]

I have a front-end database, “FinCode”, linked to a back-end database, “Finance”. Annually I make a copy of Finance, renaming it to Finance{year). Then delete the records in Finance. Occasionally I need to link to one of the old databases to check the historical data. If I forget to relink back to Finance, is there a way to automatically relink it when I open the Access program?

Kind regards,

Eddie

 

[pbaldy]

See if Daniel's method works for you:

MS Access - VBA Relink Tables | DEVelopers HUT

A better MS Access table relinking mechanism. Reduce table relinking times by 60+% with a new twist on the entire process.

www.devhut.net

 

[theDBguy]

Hi. How big (as in file size) does your BE file get every year? If you can afford it, perhaps you don't have to make those yearly archive and reset files, then it would be easier to check historical data. Also, when you want to check historical data by linking to the old BE, that sounds like you are replacing the links to the current BE. Is that correct? If so, then one possible approach for you is create a temp copy of the FE and use that one to link to the historical data. After you're done, you can simply delete that temp FE and continue to use the original FE, which should still be linked to the current BE.

 

[MajP]

Or link to the current and all other back ends. No limit to size of tables linked.

 

[CJ_London]

just curious - you delete all the data? What about customers, suppliers, opening balances?

 

[Eddie Mason]

Hi. How big (as in file size) does your BE file get every year? If you can afford it, perhaps you don't have to make those yearly archive and reset files, then it would be easier to check historical data. Also, when you want to check historical data by linking to the old BE, that sounds like you are replacing the links to the current BE. Is that correct? If so, then one possible approach for you is create a temp copy of the FE and use that one to link to the historical data. After you're done, you can simply delete that temp FE and continue to use the original FE, which should still be linked to the current BE.

They are not big on average about 1,100 KB and at present there are 16 of them. During general use it is always linked to Finance only if I need to look at data from a historical year do I change the link.

My problem occurs that if after viewing historical data I forget to update the link, the next time I use the database it will be linked to whatever year I was last viewing.

Regards,

Eddie

 

[Eddie Mason]

just curious - you delete all the data? What about customers, suppliers, opening balances?

Sorry I may have mislled you the table that are deleted are the Payments and Receipts.

 

[theDBguy]

They are not big on average about 1,100 KB and at present there are 16 of them. During general use it is always linked to Finance only if I need to look at data from a historical year do I change the link.

My problem occurs that if after viewing historical data I forget to update the link, the next time I use the database it will be linked to whatever year I was last viewing.

Regards,

Eddie

So, have you given any thought about the two things I mentioned?

1. Maybe you can stop creating archive files, since the files are not big anyway, and just keep all the data in one BE file, or
2. Use a temporary FE to link to the previous years BE and just delete the temp FE when you're done.

 

[Eddie Mason]

So, have you given any thought about the two things I mentioned?

1. Maybe you can stop creating archive files, since the files are not big anyway, and just keep all the data in one BE file, or
2. Use a temporary FE to link to the previous years BE and just delete the temp FE when you're done.

Thank you I will give it some thought.

 

[Eddie Mason]

See if Daniel's method works for you:

MS Access - VBA Relink Tables | DEVelopers HUT

A better MS Access table relinking mechanism. Reduce table relinking times by 60+% with a new twist on the entire process.

www.devhut.net

I have a front-end database, “FinCode”, linked to a back-end database, “Finance”. Annually I make a copy of Finance, renaming it to Finance{year). Then delete the records in Finance. Occasionally I need to link to one of the old databases to check the historical data. If I forget to relink back to Finance, is there a way to automatically relink it when I open the Access program?

Kind regards,

Eddie

Many thanks to everyone who has helped me today

Kindest regards,

Eddie

 

[Pat Hartman]

After 16 years of doing it this way, I'm sure you are not interested in changing but don't you ever want to run year over year reports? That would be ever so much easier if all the data was in the current database. If the app doesn't have natural filters that would select only current year data, you can add a form that opens when the app opens and asks for the year. The queries can then use this year as a filter so the app continues to work as it always has. This will require adding a form and modifying the necessary queries but is far less dangerous than your current method since each time you open the app it prompts. Another thing you can do is to add a control to each form. that displays the year in big red letters but is visible ONLY if the year is <> to the current year. Something like that in your face should help you to not use the old data inappropriately. And finally, you can add a feature that marks a year as closed so that no updates are allowed after that point which is another safeguard.