Link tables to specific Back Ends (1 Viewer)

Eljefegeneo · Jun 13, 2018

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 · Jun 13, 2018

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

arnelgp · Jun 13, 2018

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 · Jun 13, 2018

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 · Jun 13, 2018

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 · Jun 13, 2018

Thanks to all, will look at this tonight.

RMart · Jun 14, 2018

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 · Jun 14, 2018

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 · Jun 15, 2018

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 · Jun 15, 2018

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

Code:

tb.connection = !Connection

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

Perhaps I am lacking a reference? Or?

RMart · Jun 15, 2018

Congrats on your new puppy!

Eljefegeneo · Jun 16, 2018

OK, I changed the code to read:

Code:

td.Connect = !Connection

And no longer get the error message on this line.

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

Code:

Currentdb.tabledefs.refreshlink

Any suggestions?

Link tables to specific Back Ends (1 Viewer)

Eljefegeneo

Still trying to learn

Cronk

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

RMart

Registered User.

The_Doc_Man

Immoderate Moderator

Eljefegeneo

Still trying to learn

RMart

Registered User.

Pat Hartman

Super Moderator

Eljefegeneo

Still trying to learn

Eljefegeneo

Still trying to learn

RMart

Registered User.

Eljefegeneo

Still trying to learn

Similar threads

Users who are viewing this thread