Discussion Locked

MS ACCESS / VBA

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I want to put code in my ACCESS application that will automatically check the database links at startup. If they are no longer valid (db not found), have the system automatically ask for the location and then refresh the links. Is this possible, andif so, can someone show me sample code?

6 replies

by sankulpune

Hello Bill,

Here is what you need to do so that linked tables are automatically checked during the startup:

1) In a module, paste the following code:

Function CheckLinkedTables()
'
' Checks linked tables.
' Prompts if links not valid
'' Code by SanganakSakha 12/11/01
' No comprehensive data validation or error checking
Dim strLinkSource As String
Dim tbl As DAO.TableDef
For Each tbl In CurrentDb.TableDefs
If tbl.Attributes = 1073741824 Then
On Error Resume Next
tbl.RefreshLink
If Err.Number <> 0 Then
strLinkSource = tbl.Connect
strLinkSource = Right(strLinkSource, Len(strLinkSource) - InStr(1, strLinkSource, "="))
MsgBox "The table " & tbl.Name & "linked from " & strLinkSource & " has lost the link. Relink it !",vbOKOnly, "Error"
CurrentDb.TableDefs.Delete tbl.Name
DoCmd.RunCommand acCmdLinkTables
Err.Number = 0
End If
End If
Next
On Error GoTo 0
End Function

(Please note that it has to be function. A sub will not do.)

Close and save the module.
2) Create a new Macro (Access Macro, not VBA code) with following properties:

Action: RunCode
Function Name: CheckLinkedTables ()
(Note: RunCode can execute only functions, not subs).

Save the Macro with the Name 'AutoExec". Any other name will not do.

This macro executes automatically, every time you open the database.

This is all you need to do.

Your feedback, as to whether this helped or more assistance is needed, would be highly appreciated.
I would be glad to provide any more specific assistance you may need in this regard.

With best compliments,

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

This discussion is closed to new replies.