Solved Linked Backend

[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