Change Tables depending on the year chosen

[Giorgos Louloudis via AccessMonster.com]

Hello there!
I want to think that i post this thing to the right category.Well what i want
to do is to have as many backend databases as i want depending on the year i
work (say 1 db for 2005,another one for 2006 etc.) and i want to let the user
decide in which one he will use.I want him to give the year and afterwards
programmaticaly i want to say the linked table manager to connect the front
end with that db file!
Thanks for your help!
Great posting place this accessmonster!!!

 

[Allen Browne]

Why use separate databases for each year?

The only valid reason I can imagine is that you have so much data (probably
tens of millions of records) that your file size is well over a gigabyte if
you don't split them into different years. If that is the case, I suggest
you consider using SQL Sever as the back end instead of JET. If that is not
the case, I suggest you keep all the data in one mdb and avoid the problem.

If you are detemined to do it anyway, you will need to write code to receive
the user's input, and then loop through the TableDefs collection,
identifying and ignoring system tables, temp tables, and local tables, and
then setting the Connect property of the TableDef to the correct back end
database file, remembering to RefreshLink.

BTW, the site you posted from is not responsible for these groups. It is
merely a web interface into Usenet, which is much more easily read and
managed using a newsreader such as Free Agent or even Microsoft Outlook
Express (Tools | Accounts | News, and enter the server news.microsoft.com.)

 

[Giorgos Louloudis via AccessMonster.com]

Dear Allen,
I want to thank you for your answer!
You made clear everything i wanted to do.
I wonder whether there exists some code explaining how to do the connection.
My thought was that i could have one mdb file for every year so that the
users close the 2005 and when they use the program for 2006 they don't have
to see the old records!
The only thing i need is how when i read the year from the user ( i ask him
to give the year) to be able to connect to the "year".mbd file! for example
2005.mdb if he chooses 2005 , 2006.mdb if he chooses 2006 etc.
Everything u said to me is pretty clear!!!!

Why use separate databases for each year?

The only valid reason I can imagine is that you have so much data (probably
tens of millions of records) that your file size is well over a gigabyte if
you don't split them into different years. If that is the case, I suggest
you consider using SQL Sever as the back end instead of JET. If that is not
the case, I suggest you keep all the data in one mdb and avoid the problem.

If you are detemined to do it anyway, you will need to write code to receive
the user's input, and then loop through the TableDefs collection,
identifying and ignoring system tables, temp tables, and local tables, and
then setting the Connect property of the TableDef to the correct back end
database file, remembering to RefreshLink.

BTW, the site you posted from is not responsible for these groups. It is
merely a web interface into Usenet, which is much more easily read and
managed using a newsreader such as Free Agent or even Microsoft Outlook
Express (Tools | Accounts | News, and enter the server news.microsoft.com.)

Hello there!
I want to think that i post this thing to the right category.Well what i

[quoted text clipped - 7 lines]

front
end with that db file!

 

[Allen Browne]

Here's a couple of articles with relinking code:
http://www.mvps.org/access/tables/tbl0009.htm
http://www.mvps.org/access/tables/tbl0012.htm

Or download solutions.mdb from:
http://msdn.microsoft.com/library/officedev/bapp2000/mdbdownload.htm
and check out the module for refreshing links.

(Personally, I still would not use a different mdb for every year.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
I want to thank you for your answer!
You made clear everything i wanted to do.
I wonder whether there exists some code explaining how to do the
connection.
My thought was that i could have one mdb file for every year so that the
users close the 2005 and when they use the program for 2006 they don't
have
to see the old records!
The only thing i need is how when i read the year from the user ( i ask
him
to give the year) to be able to connect to the "year".mbd file! for
example
2005.mdb if he chooses 2005 , 2006.mdb if he chooses 2006 etc.
Everything u said to me is pretty clear!!!!

Why use separate databases for each year?

The only valid reason I can imagine is that you have so much data
(probably
tens of millions of records) that your file size is well over a gigabyte
if
you don't split them into different years. If that is the case, I suggest
you consider using SQL Sever as the back end instead of JET. If that is
not
the case, I suggest you keep all the data in one mdb and avoid the
problem.

If you are detemined to do it anyway, you will need to write code to
receive
the user's input, and then loop through the TableDefs collection,
identifying and ignoring system tables, temp tables, and local tables, and
then setting the Connect property of the TableDef to the correct back end
database file, remembering to RefreshLink.

BTW, the site you posted from is not responsible for these groups. It is
merely a web interface into Usenet, which is much more easily read and
managed using a newsreader such as Free Agent or even Microsoft Outlook
Express (Tools | Accounts | News, and enter the server
news.microsoft.com.)

Hello there!
I want to think that i post this thing to the right category.Well what i

[quoted text clipped - 7 lines]

front
end with that db file!

 

[Douglas J Steele]

Unless you've got huge amounts of data, I'd use a single MDB, and put a
WhatYear field on your table. To only show the data for the current year,
create a query that uses Year(Date) as the criteria against that field, so
that only records for the current year are returned. (Note: Don't name the
field Year: that's a reserved word!)

To sample code to relink tables, see
http://www.mvps.org/access/tables/tbl0009.htm if you're strictly linking to
MDBs, or http://www.mvps.org/access/tables/tbl0010.htm if you're using ODBC.
Another option if you're using ODBC and don't want the hassles of having to
create DSNs is presented at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Allen,
I want to thank you for your answer!
You made clear everything i wanted to do.
I wonder whether there exists some code explaining how to do the connection.
My thought was that i could have one mdb file for every year so that the
users close the 2005 and when they use the program for 2006 they don't have
to see the old records!
The only thing i need is how when i read the year from the user ( i ask him
to give the year) to be able to connect to the "year".mbd file! for example
2005.mdb if he chooses 2005 , 2006.mdb if he chooses 2006 etc.
Everything u said to me is pretty clear!!!!

Why use separate databases for each year?

The only valid reason I can imagine is that you have so much data (probably
tens of millions of records) that your file size is well over a gigabyte if
you don't split them into different years. If that is the case, I suggest
you consider using SQL Sever as the back end instead of JET. If that is not
the case, I suggest you keep all the data in one mdb and avoid the problem.

If you are detemined to do it anyway, you will need to write code to receive
the user's input, and then loop through the TableDefs collection,
identifying and ignoring system tables, temp tables, and local tables, and
then setting the Connect property of the TableDef to the correct back end
database file, remembering to RefreshLink.

BTW, the site you posted from is not responsible for these groups. It is
merely a web interface into Usenet, which is much more easily read and
managed using a newsreader such as Free Agent or even Microsoft Outlook
Express (Tools | Accounts | News, and enter the server news.microsoft.com.)

Hello there!
I want to think that i post this thing to the right category.Well what

i
[quoted text clipped - 7 lines]

front
end with that db file!