asked on

VBA script to toggle on and off database link

I have an access db front end app and I want to link to more than 1access db back end. I want my system ( Front end ) to be able to establish the link once a certain module is open ( application form ) and remove link once the form is closed. So i need to be able to write down a script ( VBA routine ) to turn the link on and off. I know that i can do it with refresh link using a connect property, but i want to know how to establish the link for the first time using VBA from a password-protected database.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Here is some code I have to do basically that LInk/Unlink:

Public Function MDMM_BuHstEvLogLinkCtl(sOp As String) As Boolean

Dim sFx As String: sFx = "MDMM_BuHstEvLogLinkCtl"

Select Case sOp
Case "UnLink"
On Error Resume Next
DoCmd.DeleteObject acTable, "YourTableNameHere"
If Err.Number = 0 Or Err.Number = 7874 Then
'ignore; Err 0 means no error occurred, Err 7874 means object not found (nothing to delete)
Else
GoTo MDMM_BuHstEvLogLinkCtl_Error
End If

Case "Link"
'be sure link does not already exist ... call this same function with the UnLink op
Me.MDMM_BuHstEvLogLinkCtl ("UnLink")

'Now create the link ...
Dim sPath As String
On Error GoTo MDMM_BuHstEvLogLinkCtl_Error
sPath = "YourFolderPathNameHere"
DoCmd.TransferDatabase acLink, "Microsoft Access", sPath & "MDMMData.mdb", acTable, "YourTableNameHere", "YourTableNameHere"
End Select

MDMM_BuHstEvLogLinkCtl_Exit:
Err.Clear
Exit Function

MDMM_BuHstEvLogLinkCtl_Error:
MsgBox "An unexpected error has occured!" & vbCrLf & vbCrLf & _
"Error Information --------------------" & vbCrLf & _
"Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Procedure: " & sFx & vbCrLf & _
"Operation: " & sOp & vbCrLf & vbCrLf & _
"PLEASE ... take a screen shot of this error message showing as much of the screen as possible and email it to the Database Administrator.", 48, "MASS Database Maintenance"
GoTo MDMM_BuHstEvLogLinkCtl_Exit

End Function

Now ... I forget exactly how to include the password ... but form Help ... it shows this:

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Authors", "dboAuthors"

So, if you can merge that in, I'm thinking it might work ...

mx

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial