Verify Connection Exists

[SAP2]

Scenario:
FE database on laptop that has linked tables to the server. AutoExec macro
initiates the links. My problem is that if not connected to the server the
db fails because it cannot find the links.

1. How can it check for a proper link prior to running the AutoExec macros?

2. If a connection IS NOT found then ignore linking the tables.

3. If a connection IS found then continue with the links.

Thanks for any help.

 

[Daniel Pineault]

I've never had to do what you need to, but a thought came to mind. Why not
test for the existing of the network drive. Simply use the Dir() function.
Try somthing like

If Len(Dir("z:"))>0 Then
'Connection to server established

Else
'No connection established

End If

assuming z: is your network drive that you need to check your connection to.

 

[Tom van Stiphout]

On Sat, 12 Jan 2008 02:33:00 -0800, SAP2

That's why you have an error handler in your startup code when you
check your links:
on error goto ErrHandler
'Test if you can refresh a link
currentdb.tabledefs("SomeBETable").RefreshLink
....
exit function
ErrHandler:
Msgbox "Yo! No back end found here: " &
currentdb.tabledefs("SomeBETable").Connect
end function

 

[SAP2]

Tom,
I did not set up an error handler. Not very adept with programming. I will
try this out.

Thanks

 

[SAP2]

Another question came to mind:

What if I only want the link to happen when someone is at the office (not
connected through vpn)? Can this be distinguished?

Thanks in advance.

 

[Tom van Stiphout]

On Sat, 12 Jan 2008 10:21:02 -0800, SAP2

Not easily. Someone who is connected via VPN is in fact a member of
the LAN, just over a slow link.
-Tom.

 

[Biz Enhancer]

Hi,

Instead of linking with an autoexec macro I use a form to authentic
against the server and then link the tables. That way the server must be
there before linking begins.

Create a table in the FE called ODBCTables one column with the names of
the tables you wish to link.

Create your login form and under a command button call LinkTables
Note that in the following code; server, dbase, user, pass are all input
fields on the login form. Also make sure you specify the ODBC driver. In
the code below is a MySQL driver you will need to replace it with the
appropriate driver for the server you are connecting to.



Public Sub LinkTables()
On Error GoTo ErrTrp

Dim db As Database, rs As Recordset, tdf As TableDef
Dim dbODBC As Database, strConnect As String
Dim strMsg As String
Dim SrvrNm As String
Dim DbNm As String
Dim UsrNm As String
Dim Pswd As String
ServerName = Me.server
DatabaseName = Me.dbase
UserName = Me.user
Password = Me.pass

Call DelODBC

strConnect = "DRIVER={MySQL ODBC 3.51 Driver};DATABASE=" & _
DbNm & ";SERVER=" & SrvrNm & _
";Uid=" & UsrNm & _
";Pwd=" & Pswd & ";"
Set db = CurrentDb
Set rs = db.OpenRecordset("ODBCTables")
Set dbODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdf = db.CreateTableDef(rs![TableName], dbAttachSavePWD)
tdf.Connect = dbODBC.Connect
tdf.SourceTableName = dbODBC.TableDefs(rs![TableName]).Name
db.TableDefs.Append tdf
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbODBC = Nothing
Set db = Nothing

ExitTrp:

Exit Sub

ErrTrp:
If Err.Number = 94 Then
strMsg = "User name or Password is blank. Re-enter details or
contact the system administrator."
If MsgBox(strMsg, vbRetryCancel, "Oh Bother!! Locked out again") =
vbCancel Then
DoCmd.Quit
End If
GoTo ExitTrp

End If

End Sub

Public Sub DelODBC()
On Error GoTo ErrTrp

Dim db As Database, tdf As TableDef, i As Integer
Set db = CurrentDb
For i = db.TableDefs.Count - 1 To 0 Step -1
Set tdf = db.TableDefs(i)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next i

db.Close
Set db = Nothing

ExitTrp:
Exit Sub

ErrTrp:
Resume ExitTrp

End Sub

HTH,
Regards,
Nick.

 

[Tony Toews [MVP]]

What if I only want the link to happen when someone is at the office (not
connected through vpn)? Can this be distinguished?

There is an API call over at vpnet.mvps.org that will tell you the
speed of the link. This was useful for one person in determining if
the link was a wireless or wired link. (You really, really don't want
to use Access over wireless as Access is easily corrupted.)

My experience with using OpenVPN is that it shows up as a LAN
connection speed of 10 mpbs in Windows Task Manager. And my wireless
connection shows up as 54 mpbs. So this should work for you as well.

I think the API call was the following:

IsDestinationReachable: Determine Network QOC Info
http://vbnet.mvps.org/index.html?code/network/isnetworkalive.htm

The code should be easily ported to Access removing the VB specific
text boxes and such.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/