Is this possible?

[ryetee]

I have a small database that has, as you would expect, links to tables (back end), queries, forms, sub forms, reports,macros, and modules.

I won't go into it but I have a need to have an identical system, same tables, forms etc etc everything the same except for the data.

For certain reasons I can't hold the data in the same database.

Now I could just have 2 front ends pointing to 2 identical (except for the data) backends. Only trouble is if there are any changes I will have to deploy 2 front ends every time and potentially change 2 databases as well.

Is it possible from the position I'm in now (fully functioning system) to one where I can dynamically change the set of tables a user is using?

 

[James Deckert]

Yes, you could change the links dynamically within the front end.

 

[Minty]

Without going into too much detail, why / what's the distinction between the datasets?
There may be a more elegant solution.

 

[gemma-the-husky]

This is what I do.

I have a text file (csv) with details of the available backends.

id, name, folder

In the front end I store a list of the tables to link

when the dbs starts up, I load in the text file, and ask the users to select the backend they want.

If they pick a different one to the current connection, I drop all the tables and reconnect to the new back end.

reconnection takes a couple of seconds.

 

[ryetee]

Yes, you could change the links dynamically within the front end.

So if main menu had 2 buttons one was for backend a and one was for back end b I could effectively in each selection choose where I wanted to link to?

OK I'll google that then.

 

[ryetee]

Without going into too much detail, why / what's the distinction between the datasets?.

There may be a more elegant solution.

OK there are 2 databases in 2 different countries (A and B). They will have a common set of 'reference' files. When I say common set I mean the data will be more or less identical except for local nuances.

The rest of the data will be different.
At certain periods (weekly monthly, not yet decided) country B will export it's data for completed work (ie a subset of the total database). This will be imported into country A's 2nd database. It can't be imported directly into the main database as there are links between tables using the ID which would not be preserved if copied into the main database. Does that make sense?

 

[ryetee]

This is what I do.

I have a text file (csv) with details of the available backends.

id, name, folder

In the front end I store a list of the tables to link

when the dbs starts up, I load in the text file, and ask the users to select the backend they want.

If they pick a different one to the current connection, I drop all the tables and reconnect to the new back end.

reconnection takes a couple of seconds.

OK this is effectively the same as James Deckert suggested,ie change the links dynamically.

Not quite sure I fully understand what id in the text file is.

Would you suggest having 2 back end databases (DB1 and DB2 say in folder A and B) each containing say
table1, table2, ...,tablen
in which case the text file would have
ID,DB1, Folder A
ID,DB2, Folder B
The links are for table 1 through table n
OR
have 1 database with 2 sets of tables
table1, table2, ...,tablen
table1a, table2a, ...,tablena
where table1 is same as table1a etc etc
The text file would then just be
ID, DB1, Folder A
and you either link to table1, table2, ...,tablen or table1a, table2a, ...,tablena

or have I lost the plot?

 

[ryetee]

Whilst googling this the first thing I came across was a post from 8 years ago and it states "dynamically Linking Tables is playing with a loaded gun, ". Have we advanced since then (i'm using access 2010) and it's trouble free and reliable?

 

[gemma-the-husky]

the id in the text file was just an option number, rather than using text

(option) 1, "database A", "path"
(option) 2, "database B", "path"

 

[gemma-the-husky]

Whilst googling this the first thing I came across was a post from 8 years ago and it states "dynamically Linking Tables is playing with a loaded gun, ". Have we advanced since then (i'm using access 2010) and it's trouble free and reliable?

dynamically linking tables

db.createtabledef("tablename") etc

doesn't go wrong. If it fails for any reason you get a trappable run time error. Cannot possibly be a problem.

 

[ryetee]

dynamically linking tables

db.createtabledef("tablename") etc

doesn't go wrong. If it fails for any reason you get a trappable run time error. Cannot possibly be a problem.

am i right in saying that this creates something (a table definition) and then you set its Connect and SourceTableName properties and then Use the Append method to add it to the TableDefs collection of a Database (I copied this from some msdn site!).

well my front end already has a list of tables linked to a database.I just want to relink those to another set of tables which may or may not be in the same back end database so do i still need createtabledef? if so do have to delete any links that are already there?

 

[ryetee]

I've found some code to achieve more or less what I wanted viz,

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
Const LnkDataBase = "C:\folder\BACKEND.accdb"


For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 1 Then 'Only relink linked tables
If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
strTable = tdf.Name
dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
dbs.TableDefs(strTable).RefreshLink
End If
End If
End If
Next tdf

This works if I want to link to another data base,but I want to link each table to another table in the same database. So effectively I have 2 identical databases in the one!
The other thing that has happened is that I have all tables in custom categories. By running the above the end up in Unassigned Objects.
Any ideas what I need to do. probably suss the former out but not sure where to start with the latter!

 

[James Deckert]

I can't tell if you're going to have data for both countries in a single database, or you're only going to have data for one country and it's data is in a table corresponding to that countries properties.

If you're going to have data from both countries in the db, could you simply add a field to the table specifying which country the data is for?
If you're only going to have data from a single country, why not just have the few extra fields in a single table so it works with both countries?
Either solution bypasses the headache of maintaining to similar sets of tables.

 

[The_Doc_Man]

I'm with James Deckert on this one.

Unless there is a size / space issue or a specific security issue, you would take less time and have less headaches for data management with a single shared backend where your top-level tables have one extra field that identifies the country. Child tables that are the same for all cases would need no such country discriminator. (I.e. tables used for translation of codes or identification of locations or part numbers or person names, etc.)

I'll be more specific about why I think you should avoid the split-table situation.

If you have a properly split FE/BE case and you have a button to "connect to set of tables #1" or "connect to set of tables #2," that ability requires that the sets are structured identically. Based on concepts of normalization, you would then have a case where you are using the table set-number as a key to select from otherwise structurally identical tables. If you are familiar with the terms "necessary" and "sufficient" regarding proofs, that button would make it necessary for the structures to be the same.

Looking at your data set as a whole, that means that for at least some of your tables, the selection of a record does not depend on a field that exists in that table. This is a normalization violation. From a technical standpoint, normal analysis tools that you might find commercially on-line would not be able to help you find/fix problems in a table that is so massively denormalized. Set theory would still work but the disjunction implied in this situation would be very confusing.

Adding a single field as the country designator would be sufficient to allow you to merge the two table sets. That would make your work viable.

 

[speakers_86]

The JStreetRelinker might help you.

 

[gemma-the-husky]

ryetee.

I assumed you had two discrete database with the same named tables.

What I do is drop all linked tables, and then recreate the links to the new backend. Some people do the same process by using "refreshlinks".

Personally in your case I would not have differently named tables. I would have two or more discrete backend databases, one for each company each with the same named tables.

Alternatively use the docman's idea of having a company flag. However, if you use the company flag, then you may need to change a fair bit of functionality to work differently.

----
I suppose you could do it your way by renaming tables within the database. I haven't got time to post now, but I will come back to it later today.

 

[ryetee]

I can't tell if you're going to have data for both countries in a single database, or you're only going to have data for one country and it's data is in a table corresponding to that countries properties.

If you're going to have data from both countries in the db, could you simply add a field to the table specifying which country the data is for?
If you're only going to have data from a single country, why not just have the few extra fields in a single table so it works with both countries?
Either solution bypasses the headache of maintaining to similar sets of tables.

Not really. One country will only have it's own data. A copy of that data from that country is sent to HQ who will run update, reports etc etc etc. Data needs to be kept separate as the ID is used to link a lot of tables. Had we known when developing the database initially that there was to be another location the design would be different.
Anyhow I have this working (see above) now...to a fashion!

 

[ryetee]

I'm with James Deckert on this one.

Unless there is a size / space issue or a specific security issue, you would take less time and have less headaches for data management with a single shared backend where your top-level tables have one extra field that identifies the country. Child tables that are the same for all cases would need no such country discriminator. (I.e. tables used for translation of codes or identification of locations or part numbers or person names, etc.)

I'll be more specific about why I think you should avoid the split-table situation.

If you have a properly split FE/BE case and you have a button to "connect to set of tables #1" or "connect to set of tables #2," that ability requires that the sets are structured identically. Based on concepts of normalization, you would then have a case where you are using the table set-number as a key to select from otherwise structurally identical tables. If you are familiar with the terms "necessary" and "sufficient" regarding proofs, that button would make it necessary for the structures to be the same.

Looking at your data set as a whole, that means that for at least some of your tables, the selection of a record does not depend on a field that exists in that table. This is a normalization violation. From a technical standpoint, normal analysis tools that you might find commercially on-line would not be able to help you find/fix problems in a table that is so massively denormalized. Set theory would still work but the disjunction implied in this situation would be very confusing.

Adding a single field as the country designator would be sufficient to allow you to merge the two table sets. That would make your work viable.

If the 2 countries were accessing the same database I would agree and even then I don't think I need a country identifier anyway.Problem I have is I want to merge country a with country b (and not the other way round). As the tables use the ID to link hen there will be possible conflicts.

 

[ryetee]

The JStreetRelinker might help you.

Thanks I'll take a look.

 

[gemma-the-husky]

OK my previous idea

have a set of tables for each company.

table1_companyA
table1_companyB
table2_companyA
table2_companyB

actually use "active tables" called table1, table2 in all your queries.

You swap companies in and out by renaming the tables.

 if you want companyB THEN
      If companyB is not active then 
          rename all the tables to have the suffix _companyA
          rename all the company tables to have no suffix
      end if
 end if

It's a matter of taste which way you go. There are pros and cons with each method.