By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

Relinking Access Linked Tables (Multiple Back-Ends)

P: 7
Hello, I'm a total newbie here, but I have been searching for days for the answer to this without any luck.

I used your function to refresh my linked tables upon opening the front end of my database. It worked like a charm for linked tables that reside in only one SQL Server database. Then I moved on to my next front end, that has linked tables that reside in 3 separate SQL Server databases, and no joy, of course. Is it possible to expand this code to look to the 3 different databases?

Thanks in advance for any help you can provide.

Jennifer
Mar 4 '10 #1

✓ answered by NeoPa

@sherryj2
  1. Cool. Just what the doctor ordered :)
  2. This looks to me like the connection string is not required to be changed at all. Simply a retry of the link. Please confirm.
  3. Yes. Absolutely that is my question. Irrelevant if the response to #2 above is to confirm though.
  4. Cool. I saw that thanks.
As to your final question, I see this as relating directly to #2. If you have no wish to change the details of the .Connect string, but simply to attempt a relink of what is already specified, then that can be accomplished most easily (of all the possible scenarios). Bear in mind I can test, therefore guarantee, nothing. However, you could try out the following simpler code if this describes your situation :
Expand|Select|Wrap|Line Numbers
  1. Sub relinkTables()
  2.  
  3. Dim db As DAO.Database
  4. Dim tdf As DAO.TableDef
  5.  
  6.     Set db = CurrentDB
  7.     For Each tdf In db.TableDefs
  8.         If Len(tdf.Connect) > 0 Then Call tdf.RefreshLink
  9.     Next
  10.  
  11. End Sub

Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Hi Jennifer.

I've split this from Relinking ODBC Tables using VBA as that is an article and you need a question thread.

Administrator
Mar 4 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
I don't have any such database to play with at the moment, but if you can post some details about the .Connect strings of the tables you want to change, and what about them you want to change, then we can look into it for you.

A copy of the current state of your code related to this would be helpful too.
Mar 4 '10 #3

P: 7
Here is the code I mentioned I was using above:

http://bytes.com/topic/access/insigh...bles-using-vba

It works for a single back-end, but not for multiple backends
Mar 4 '10 #4

P: 7
Here is how I have modified the code that Mary provided (I only added the ODBC connection string):
Expand|Select|Wrap|Line Numbers
  1. Function relinkTables()
  2.  
  3. Dim tdf As DAO.TableDef
  4.  
  5.      For Each tdf In CurrentDb.TableDefs
  6.         If Len(tdf.Connect) > 0 Then
  7.              tdf.Connect = "DRIVER=SQL Server;SERVER=OurServer;DATABASE=sdeTrails;Trusted_Connection=Yes"
  8.              tdf.RefreshLink
  9.         End If
  10.     Next
  11.  
  12. End Function
It goes through the list of linked tables and refreshes them all for my single back-end Access front-end. But when I try to run this code in an Access front-end that has linked tables in 3 different SQL server databases (all on the same server instance), it won't work. I understand why (because the table I want to refresh is not in the database specified in the single connect string I provided), but I just don't know how to say in code "either look for these tables in Database #1 or look for these tables in Database #2 or look for these tables in Database #3, and then refresh the link".

Does that make any sense?

Thanks-
Jennifer
Mar 4 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
@sherryj2
Jennifer,

I'm afraid you'll have to do better than that.

I asked for your code. Not a link (that I've already posted anyway) to a thread which contains multiple posts which include code.

I also asked for the .Connect information you start with, and an explanation of what you want them to be changed to.

If there's anything here you don't understand then please say so. Otherwise I would expect answers before I spend more time trying to help you.
Mar 4 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
I've now seen your more recent post and am going through it. It looks more helpful & I will post a response shortly.
Mar 4 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
That's much clearer and is something we can certainly work with.

I have some more questions though (1 a repeat but some new ones as we have moved forward) :
  1. What is the current .Connect string set to for each table (or some examples if there are many)?
  2. What is the rationale behind the relink? Is there something we can work with intelligently? Or is it largely indeterminate?
  3. Is there anything which indicates which of the 3 databases any table should be found in?
  4. What are the names of the other 2 databases (One appears to be sdeTrails)?
Mar 4 '10 #8

NeoPa
Expert Mod 15k+
P: 31,186
For want of any immediate answers (That's not a problem. Immediate is not something to expect when dealing via a web site) you can try this template code. You'll need to put your own names in, but it should be close at least :

Expand|Select|Wrap|Line Numbers
  1. Sub relinkTables()
  2.  
  3. Dim db As DAO.Database
  4. Dim tdf As DAO.TableDef
  5. Dim strConnect As String
  6.  
  7.     strConnect = "DRIVER=SQL Server;SERVER=OurServer;DATABASE=%D;" & _
  8.                  "Trusted_Connection=Yes"
  9.     On Error Resume Next
  10.     Set db = CurrentDB
  11.     For Each tdf In db.TableDefs
  12.         With tdf
  13.             If Len(.Connect) > 0 Then
  14.                 .Connect = Replace(strConnect, "%D", "sdeTrails")
  15.                 Call .RefreshLink
  16.                 If Err > 0 Then
  17.                     .Connect = Replace(strConnect, "%D", "db2")
  18.                     Call .RefreshLink
  19.                     If Err > 0 Then
  20.                         .Connect = Replace(strConnect, "%D", "db3")
  21.                         Call .RefreshLink
  22.                     End If
  23.                 End If
  24.             End If
  25.         End With
  26.     Next
  27.  
  28. End Sub
NB. There is no value returned so a subroutine procedure is more appropriate than a function one - hence the change.
Mar 4 '10 #9

P: 7
NeoPa:

1: here are three connection strings used in the multiple back-end scenario:

DRIVER=SQL Server;SERVER=OurServer;APP=Microsoft Data Access Components;WSID=OSC-SHERJ1-9794;DATABASE=Vegetation;Network=DBMSSOCN;Trusted_ Connection=Yes

DRIVER=SQL Server;SERVER=OurServer;APP=Microsoft Data Access Components;WSID=OSC-SHERJ1-9794;DATABASE=Monitoring;Network=DBMSSOCN;Trusted_ Connection=Yes

DRIVER=SQL Server;SERVER=OurServer;APP=Microsoft Data Access Components;WSID=OSC-SHERJ1-9794;DATABASE=sdeMonitoring;Network=DBMSSOCN;Trust ed_Connection=Yes

2. I want to force the refresh because my users often get the "ODBC call failed" message and most are savvy about refreshing the links in the linked table manager, but I would like it to be more seamless. Somehow it's always my fault when things don't work properly.

3. Do you mean is there anything in the name of the table that might be able to categorize it into one of the three databases?

4. other database names provided in connection strings above.

When I look in the MSysObjects table, I see the connections strings for each table. Is there a way to just use that info for the refresh?

Thank you for your patience/help.

Jennifer
Mar 4 '10 #10

P: 7
Great. I will give that a try right now.
Mar 4 '10 #11

P: 7
NeoPa:

Wow, that totally worked. Thanks very much for your input! I knew it was simple, but I had no idea how to do it.
Mar 4 '10 #12

NeoPa
Expert Mod 15k+
P: 31,186
@sherryj2
  1. Cool. Just what the doctor ordered :)
  2. This looks to me like the connection string is not required to be changed at all. Simply a retry of the link. Please confirm.
  3. Yes. Absolutely that is my question. Irrelevant if the response to #2 above is to confirm though.
  4. Cool. I saw that thanks.
As to your final question, I see this as relating directly to #2. If you have no wish to change the details of the .Connect string, but simply to attempt a relink of what is already specified, then that can be accomplished most easily (of all the possible scenarios). Bear in mind I can test, therefore guarantee, nothing. However, you could try out the following simpler code if this describes your situation :
Expand|Select|Wrap|Line Numbers
  1. Sub relinkTables()
  2.  
  3. Dim db As DAO.Database
  4. Dim tdf As DAO.TableDef
  5.  
  6.     Set db = CurrentDB
  7.     For Each tdf In db.TableDefs
  8.         If Len(tdf.Connect) > 0 Then Call tdf.RefreshLink
  9.     Next
  10.  
  11. End Sub
Mar 4 '10 #13

NeoPa
Expert Mod 15k+
P: 31,186
@sherryj2
No worries Jennifer. When you got the answers to the questions going it became a whole lot easier to work with.

Can you let me know how you get on with the code in the previous post (#13) please. I'm curious to see if that works for you.
Mar 4 '10 #14

P: 7
Wow. That was much simpler even. Yes you are right that all I needed to do was refresh (relink) the link for each table. I had searched all over Google for a simple way (that I could understand) which is how I came across msquared's "insight" on this site.

It amazes me that I spent the better part of two days trying to find the answer to that. I suppose it's time I just learned how to write some code, no?

Thanks again.
Mar 4 '10 #15

NeoPa
Expert Mod 15k+
P: 31,186
@sherryj2
Only you can know the answer to that one Jennifer.

Me, I can recommend it as fun, but not everyone finds fun in the same places. How much it would help you, in your situation, again only you can say. I can say that we are generally able to provide assistance for those who want to learn. If that's you, then why not give it a try? The worst that can happen is you hate it. You're still only back where you started. No reason to pursue it if you decide you'd rather not. In other words - What's a wet toe between friends?
Mar 4 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.