Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-12-2018, 05:30 PM   #1
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 660
Thanks: 234
Thanked 27 Times in 27 Posts
Eljefegeneo is on a distinguished road
Link tables to specific Back Ends

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.

__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 06-12-2018, 10:21 PM   #2
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,741
Thanks: 2
Thanked 369 Times in 365 Posts
Cronk will become famous soon enough
Re: Link tables to specific Back Ends

A search should give you several variations on this theme. One is http://www.theaccessweb.com/tables/tbl0009.htm
Cronk is offline   Reply With Quote
Old 06-12-2018, 10:56 PM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,132
Thanks: 54
Thanked 1,966 Times in 1,879 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Link tables to specific Back Ends

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following 2 Users Say Thank You to arnelgp For This Useful Post:
Gasman (06-12-2018), RMart (06-14-2018)
Old 06-13-2018, 10:59 AM   #4
RMart
Newly Registered User
 
Join Date: Jun 2018
Location: Toronto, ON Canada
Posts: 12
Thanks: 8
Thanked 0 Times in 0 Posts
RMart is on a distinguished road
Re: Link tables to specific Back Ends

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

Last edited by RMart; 06-13-2018 at 04:35 PM.
RMart is offline   Reply With Quote
Old 06-13-2018, 12:55 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,710
Thanks: 42
Thanked 1,039 Times in 948 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Link tables to specific Back Ends

Quote:
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\...)?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-13-2018, 02:50 PM   #6
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 660
Thanks: 234
Thanked 27 Times in 27 Posts
Eljefegeneo is on a distinguished road
Re: Link tables to specific Back Ends

Thanks to all, will look at this tonight.
__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 06-13-2018, 03:17 PM   #7
RMart
Newly Registered User
 
Join Date: Jun 2018
Location: Toronto, ON Canada
Posts: 12
Thanks: 8
Thanked 0 Times in 0 Posts
RMart is on a distinguished road
Re: Link tables to specific Back Ends

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.

RMart is offline   Reply With Quote
Old 06-13-2018, 08:03 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,094
Thanks: 13
Thanked 1,340 Times in 1,279 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Link tables to specific Back Ends

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.
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 06-15-2018 at 09:09 AM.
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
RMart (06-14-2018)
Old 06-14-2018, 06:18 PM   #9
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 660
Thanks: 234
Thanked 27 Times in 27 Posts
Eljefegeneo is on a distinguished road
Re: Link tables to specific Back Ends

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.
__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 06-14-2018, 08:46 PM   #10
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 660
Thanks: 234
Thanked 27 Times in 27 Posts
Eljefegeneo is on a distinguished road
Re: Link tables to specific Back Ends

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?
__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Old 06-15-2018, 05:10 AM   #11
RMart
Newly Registered User
 
Join Date: Jun 2018
Location: Toronto, ON Canada
Posts: 12
Thanks: 8
Thanked 0 Times in 0 Posts
RMart is on a distinguished road
Re: Link tables to specific Back Ends

Congrats on your new puppy!
RMart is offline   Reply With Quote
Old 06-15-2018, 04:41 PM   #12
Eljefegeneo
Still trying to learn
 
Join Date: Jan 2011
Location: Saratoga, California
Posts: 660
Thanks: 234
Thanked 27 Times in 27 Posts
Eljefegeneo is on a distinguished road
Re: Link tables to specific Back Ends

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?

__________________
“To err is human, but to really foul things up you need a computer.”
Eljefegeneo is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Front/Back ends khurram7x General 7 11-02-2015 11:36 AM
Question how to link one front end with many back ends nileflower General 2 03-09-2013 12:02 AM
front and back ends swin General 6 07-26-2007 07:23 AM
Multiple Back-ends Jonas G General 1 10-16-2006 06:33 AM
Queries in the back-ends??? Newman General 6 03-07-2005 07:30 AM




All times are GMT -8. The time now is 03:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World