MSysObjects - database field

[vanillapod]

I know the answer is probably no but is there any way you can change the value in the database field MSysObjects table, if not whats the easiest way of automatically re-linking linked tables on start-up?

Cheers

 

[jebry]

Hi!

You can use the Delete method of the TableDef collection to delete the links in question from a public function. You can then relink using the TransferDatabase Method in the same public function. You can then call the function from the autoexec macro using the RunCode Action. Having said that, I am curious as to why you want to relink every time the database is opened? Maybe there is a better way to accomplish what you want to do.

hth
Jeff Bridgham

 

[vanillapod]

There's two databases. One is a backend and will never only be accessed by users using active server pages. The other database will always be open on one PC as it uses the timer event to check for new records and do certain things, output reports ect. I don't know where the users of the system are gonna install the databases to or in the case of the database that always has opne instance open whether it will be opened locally, over a network ect. Re-linking the tables, i was thinking, could help me get around this....

If you know of a better way of doing it i'm all ears!
Cheers

gary

 

[UncleJack]

Set strFileName to a table you know to be linked and then call the following function at startup. The function loops through all the tables and refreshes the links to the database where strFileName resides.


Public Function RefreshTableLinks(strFileName As String) As Boolean
' Refresh links to the supplied database. Return True if successful.
MsgBox "refreshing links"
Dim tdf As TableDef

' Loop through all tables in the database.
Set DB = CurrentDb
For Each tdf In DB.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
RefreshTableLinks = False
Exit Function
End If
End If
Next tdf

RefreshTableLinks = True ' Relinking complete.

End Function


Uncle Jack

 

[vanillapod]

Cheers Uncle jack! I'll give it a go

Gary

 

[vanillapod]

can't get it to work! Doesn't recognise the &quot;DB&quot; in Set DB = Current DB

Any ideas?
Cheers

 

[Zorro1265]

In the sub you are calling the procedure from make sure you have....
Dim db As DAO.Database
Dim rst As DAO.Recordset

or it wont know what a DB or rst is. Zorro

 

[MichaelRed]

Have you considered using URL addressing in the liking process? It can easily overcome the local (front end) issue of having different 'paths' to the back end.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jebry]

Hi!

I see that MichaelRed has given you the same answer I would. After the back end is placed, make sure the link path is not given by logical drives, but by the actual server name. BTW, the method provided by Uncle Jack still assumes that you know the path to the back end. And if you know that, it is easier just to use the URL when linking.

Jeff Bridgham

 

[vanillapod]

cheers got it working!