Using data from other data bases in a "main data base"..any problems

[namliam]

Still doing a relink action is better/cleaner, but renaming excisting linked tables will work too

 

[Mike375]

I think renaming is fast, it is instant.

I just made a macro and for quick testing repeated it to equal doing about 30 tables and it is just "right now".

I will do them tomorrow and then move back to link changing for other purposes.

The renaming will take a bit longer to do than first appears because you have to rename both tables to a second pair and then rename that pair

I only though about using Rename when I was fiddling with that Link manager. I made a copy of one of the tables so I couldlink to 2 dbs and I deleted a bundle of records from one so I physically see it was working when I changed the link.....and then it hit me with Rename

If you had not mentioned changing the linking and if were not going backwards and forwards on this thread I would not have thought of the Renaming. If you were in Australia I would buy you a beer

 

[namliam]

The switching is going to be a bit of a bother... but... again coding can be your friend (freindly advice: Dont use a macro, come on you can do better )

The beer is on you if you are ever in the NL

 

[Mike375]

I did it this way

MainTable............what queries run off
MainTableA

MainTable to MainTableTemp
MainTabkeA to MainTableATemp

MainTableTemp to MainTableA
MainTableATemo to MainTable

I will one macros and stick it on two labels and when the macro is run turn that label invisible and make the other label visible. I think that should do it.

Is there any way to print out a list of the linked tables?

 

[Mike375]

DoCmd.Rename "MainTableTemp", acTable, "MainTable"
DoCmd.Rename "MainTableATemp", acTable, "MainTableA"
DoCmd.Rename "MainTableA", acTable, "MainTableTemp"
DoCmd.Rename "MainTable", acTable, "MainTableATemp"

 

[namliam]

That is one way of doing it... I would hate to do that with 100+ tables tho

 

[Mike375]

That is one way of doing it... I would hate to do that with 100+ tables tho

Me too, which is why I will get someone else to do it tomorrow. I will do one in Word, copy and paste it down and give them the list of table names and then paste the result into code.

Would link changing be in an instant. I filled a macro with copy/paste and a macro has 1000 actions lines so it would be the equal to changing 250 table names. The hour glass was up for about a second. With about 300 action lines worth the hour glass did not come up.

 

[namliam]

How about storing the table names into a table or even just using something like:

dim T as dao.tabledefs 'Make sure to reference DAO
For each T in Currentdb.Tabledefs
    debug.print t.name
next T

This will loop thru all the tables that excist in your DB.
Then you can do something like...

If right(t.Name,2) <> "_1" then
     rename t.name to t.name & temp
     rename  t.name & _1 to t.name
     rename t.name & temp to t.name & _1
end if

inside the loop to shuffle the stuff... sorry for the semi pseudo code but it is getting late here now too

 

[Mike375]

I got one bug I need to fix before moving to tabledefs etc and it would apply if relinking was done instead of renaming. As far as I tell if I complete it on a rename basis then if it is changed to relink everything else will be OK.

Have to fix up the call back system so it is has records from both sets of categories. That also involves things at "start up"