Refreshing Links From Two Different Data Sources 1

[jett88]

I have a database that has linked tables in it from two different data sources. The tables from the first data source begin with SAC_ the tables from the second data source begin with STO_. What I have go so far is:

Function refreshLinks() As Boolean

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = "ODBC;DSN=;UID=;PWD="
Err = 0
On Error Resume Next
tdf.RefreshLink
If Err <> 0 Then
refreshLinks = False
MsgBox &quot;Error..... '&quot; & tdf.Name & &quot;'&quot;, vbOKOnly Or vbExclamation, &quot;Error&quot;
Exit Function
End If
End If
Next tdf

refreshLinks = True

End Function

My problem is that I need to connect to a different DSN (same username and password) for each table &quot;prefix&quot;. Any suggestions?

 

[scking]

Your tdf.Connect property should contain the tablename. Just check that the table starts with either of the two valid tablename types and set the connection. Your function not only refreshes but also sets the value. You might need to write a parsing routine to preserve the parts of the connect property which need preserving and then build a new strConnect value to use.

If InStr(1, tdf.Connect, &quot;STO_&quot; Then
' Refresh one type
ElseIf InStr(1, tdf.Connect, &quot;SAC_&quot Then
' Refresh the second
End If
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[jett88]

That did not work. I even broke it into two separate functions for &quot;STO_&quot; and &quot;SAC_&quot;. Every time it links all linked tables to last DSN listed in tdf.Connect. Any ideas?

 

[scking]

It would be best if you set a breakpoint at the 'If' statement and watched the value of variables and which logic path it took. For instance, what does the value of tdb.Connect look like? Did it match my assumed &quot;_STO&quot; etc. If the value contained the strings we were looking for did it then go into the if branck and set the values of tdb.connect? If it did, did it then refresh? What was the value of the tdf.Connect property just prior to the end of the loop? If you know the answers to these questions you, yourself, should be able to determine the solution.

At the beginning of the loop put 'Debug.Print &quot;Found &quot; & tdf.Connect' and at the end of the loop put 'Debug.Print &quot;Changed to: &quot; & tdf.Connect'.

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[AppStaff]

Useful info thanks

 

[jett88]

I did that as well, and I think I have something wrong elsewhere in the system. I put the correct connect strings in the MSysObjects table and the tables still open incorrectly, however if I leave out the UID and PWD and have the system prompt me for these values it works fine.

 

[scking]

What did you do in the MSysObjects system table? This table is not meant for a user to enter data. Access manages these totally independent of the user. So if you add a table the system will automatically make the correct entries in the system tables. There are many other ways to affect the values of the fields in the system tables besides entering values.

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------