Link tables to specific Back Ends

[Eljefegeneo]

I have a front end that is linked to two different back ends. To work on changes of this DB, I work from home thus have to put it on my Dropbox, remotely go to a work computer, copy it from there onto the server then re-link all the tables. Fortunately there are only 11 tables. But I have to be very careful on which backend I select to re-link them.

I've done some research on this but most "re-linking" code does not seem to address this. What I am looking for is some code that says re-link table1 to back end 1, table 2 to back end 2, table 3 to back end 1, table 4 to back end 1, table 5 to back end 2, etc.

My work copy at home links to two tables whose path does not coincide with that of the server.

I hope I have explained this correctly. I do not know how to identify each table and how to identify the correct link to re-link each one.

 

[Cronk]

A search should give you several variations on this theme. One is http://www.theaccessweb.com/tables/tbl0009.htm

 

[arnelgp]

You can also make a new table with fields:

WorkPlace (string)
LinkedTable (string)
Connection (string)


Fill this table with info such as if the db is in home, put ib the field workplace "Home".
Relink the table manually first so you will get the connection string for each link table.
Then add the linked table name and its connextion string into tgis table.

Youll also need a code in vba that will loop through this table:

Public Funtion fncRelink()
Dim td as dao.tabledef
With currentdb.openrecordset("thenewtable")
.movefirst
While not .eof
Set td=currentdb.tabledefs(!LinkedTable)
td.Connection=!Connection
set td=nothing
.movenext
Wend
End with
Currentdb.tabledefs.refreshlink
End function


Call this from a button in a form.

 

[RMart]

I was just thinking that perhaps you can have a function that exports the tables back to your 2 work server back ends. I can try to supply you with a link that exports to another database. Not sure if that would apply here. Maybe you can find a way to modify it?


Unfortunately I cannot give you the link. Search Devhut.net

 

[The_Doc_Man]

My work copy at home links to two tables whose path does not coincide with that of the server.

Are you using drive-letter:\path\... for linking or UNC (\\server\path\...)?

 

[Eljefegeneo]

Thanks to all, will look at this tonight.

 

[RMart]

Don't thank me. The two gentlemen deserve the thanks. I was just throwing an idea up in the air. Just trying to help out.

 

[Pat Hartman]

I thought I uploaded this last night but I don't see it.

Here is a form I use that is used to relink when there are multiple BE databases. Rather than listing individual tables, the form lists individual BE's. Then you get to brows to a new BE and click the relink button next to the BE you want to relink.

UPDATED -- I removed the relink example since you didn't seem to be interested in it and it isn't something I generally give out.

 

[Eljefegeneo]

Still working on this. First have to find an hour or two to digest it all, but have new puppy in house, Labradoodle or as I say Crazydoodle.

 

[Eljefegeneo]

arnelp: I tried your code in Post #3 and I get an error message at the line:

tb.connection = !Connection

Error Message is: Compile Error, method or data member not found

Perhaps I am lacking a reference? Or?

 

[RMart]

Congrats on your new puppy!

 

[Eljefegeneo]

OK, I changed the code to read:

td.Connect = !Connection

And no longer get the error message on this line.

Now I get the same error message on the last line.

Currentdb.tabledefs.refreshlink

Any suggestions?