Can I have a linked table link to nothing?

[marlan]

Hi to all!

I would like to couse a linked table (Access 2003) to link to nothing. how can I do that? any ideas?

More in deatail:
I have a FE mdb file than can link to one of a few anual BEs: BE_2008, BE_2009, BE_2010. changing links is done be a sub that receives a BE_path, goes through all tableDefs: if .connect properties is longer than "", it is changed to ";DATABASE=" & filePath.

The point is that BE_2011 has new tables, say called tblNew. After switcing from BE_2011 to BE_2010, tblNew that has no link to BE_2010, stais linked to BE_2011 (after handling Err 3011). That is a NoNo!!!

thanx for helping!

 

[Galaxiom]

Unless you have more than a couple of million records in each year you have little to gain by separating the backends into different databases.

My "how many records are too many" experiment is just short of eight million records now and still the database is working fine.

 

[marlan]

Hi!

You may be right in general... Usung this structure loading the main working form (about 300K records + calculated fields from 14 tables, using 9 sub forms) takes about 15-30 seconds. Filtering out anual data from a larger DB would have to use 10 more tables. I think it would just take to long. Anyway, I am not going to make such a major change...
I would just like to know if there is a way to have a linked table link to nothing.
An other solution would be to have these tables link to dummy-tables. not very elegant.

any ideas?

 

[Galaxiom]

Filtering out anual data from a larger DB would have to use 10 more tables.

Having to add tables to deal with more years sounds like you have a structural anomaly. The data for multiple years should be able to go into the exact same tables as a single year's data. The form/report should simply be able to filter by date and return only the reccords for the year of interest.

I would just like to know if there is a way to have a linked table link to nothing.

I don't quite understand what you mean by this question. Do you have the tables for a year's back end with no data in it and the problem is it returns a lot of errors?

 

[gemma-the-husky]

If you knowingly add a new table to the back end in ye 2011, surely the best thing to do is to add this same table to other backends. you only have to do this once.

obviously if you find a way of only having 1 backend you dont need to do this - although this may need some changes to all your tables, and changes to the assocaited functionality, to include the year indicator.

---------------------
note that the slow "data switch" may be solved/speeded up in the following way.

after you link the first new table, open a recordset linked to that table. subsequent tables should then be connected much more quickly.

 

[Rabbie]

Hi!

You may be right in general... Usung this structure loading the main working form (about 300K records + calculated fields from 14 tables, using 9 sub forms) takes about 15-30 seconds. Filtering out anual data from a larger DB would have to use 10 more tables. I think it would just take to long. Anyway, I am not going to make such a major change...
I would just like to know if there is a way to have a linked table link to nothing.
An other solution would be to have these tables link to dummy-tables. not very elegant.

any ideas?

The simplest way to filter out data from a larger DB is to use a simple query with the criteria for the relevent date range. No need at all for extra tables to do this.

 

[marlan]

Thanks all for your replies!

I understand from this thread is that the answer is no, a linked table must allwase be linked.

As for yhe other issues: spliting data into a few DBs is never what you would do if you can help it, it causes problems, well just like the one I ran into: I would like to keep slitly diferent funcunality for diferent DBs from the same FE. you have to be an acrobat to do that, and a good DB should be more stable than an acrobat.

as for the 10 extra tables: I don't realy want to go into details, but I have 10 tables in which some of the data shuld get a registerd update once a year. usnig an anuale DB lets me keep this data as a 1:1 relation, in the same table (as the ). Adding 10 tables with 1:N relations chnges all system funcunality. My solution was, for now, go for easy acrobatics.

Adding the tables to other BEs is a fine solution, as long as I make soure it does not add new functunality to historical data. As I'v writen earler - acrobatics...

Thanks for all the replies, they were of much help!

 

[gemma-the-husky]

Thanks all for your replies!

I understand from this thread is that the answer is no, a linked table must allwase be linked.

As for yhe other issues: spliting data into a few DBs is never what you would do if you can help it, it causes problems, well just like the one I ran into: I would like to keep slitly diferent funcunality for diferent DBs from the same FE. you have to be an acrobat to do that, and a good DB should be more stable than an acrobat.

as for the 10 extra tables: I don't realy want to go into details, but I have 10 tables in which some of the data shuld get a registerd update once a year. usnig an anuale DB lets me keep this data as a 1:1 relation, in the same table (as the ). Adding 10 tables with 1:N relations chnges all system funcunality. My solution was, for now, go for easy acrobatics.

Adding the tables to other BEs is a fine solution, as long as I make soure it does not add new functunality to historical data. As I'v writen earler - acrobatics...

Thanks for all the replies, they were of much help!

it depends how you actually use the "dummy" tables. if they are linked to other tables in some way, and have no data in them, you may find you start getting strange results.

 

[accessfleet]

Having more than one database is like having more than one watch. You are never really sure what time it is.