Reconneecting to BE after network drops out

[MS]

A97

At work we have a badly set up network. The boss won't spend any money.
Basically there are about 10 computers on a LAN. One computer is used to
store most of our data - it is not a true server.

I have made a heap of FE/BE databses with the data being stored on this
central computer. Because of the undisciplined networking, connections are
frequently broken resulting in the FE's being used at the time having to be
closed and reopened when the network resource is available again.

Is there a way of (say) closing all the forms of the FE application being
used, reconnecting to the BE and continuing without having to close the
Access application in task manager?

Cheers!

[dreadnought8]

Here are some functions to close all forms, disconnect all tables,
connect them all again. I haven't tried them after a broken network
connection - I use them to switch between a development and a
production environment - but think they should work.
Assuming you can trap the error when it occurs and call them.
Your boss's attitude is false economy.
The big worry with faulty networks is that they will eventually corrupt
your backends.
If you're lucky you'll be able to repair them; if you're not, your boss
will lose his data.
Terry Bell
Melbourne
Function CloseAllForms(Optional except)
Dim frm As Form, intI As Integer, i As Integer
Dim intJ As Integer, ExceptForm
Dim intControls As Integer, intforms As Integer
intforms = Forms.Count ' Number of open forms.
If IsMissing(except) Then
ExceptForm = ""
Else
ExceptForm = except
End If

For i = Forms.Count - 1 To 0 Step -1
If Forms(i).Name <> ExceptForm Then
DoCmd.Close acForm, Forms(i).Name
End If
Next i
End Function

Public Function DetachAllTables()
Dim db As Database, BackEndDb As Database, tbl As TableDef, x
SysCmd acSysCmdSetStatus, "Detaching Tables"
Set db = CurrentDb()
For Each tbl In db.TableDefs
If tbl.Attributes = dbAttachedTable Then
SysCmd acSysCmdSetStatus, "Detaching " & tbl.Name
DropTable tbl.Name
End If
Next
db.TableDefs.Refresh
End Function

Public Function AttachAllTablesIn(DBPath)
Dim db As Database, BackEndDb As Database, tbl As TableDef, x
'Open the backend database and connect all the tables
Set BackEndDb = DBEngine(0).OpenDatabase(DBPath)
For Each tbl In BackEndDb.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then 'Don't attach hidden system
tables
SysCmd acSysCmdSetStatus, "Attaching " & tbl.Name
AttachTable tbl.Name, CurrentDb(), DBPath
End If
Next
CurrentDb().TableDefs.Refresh
SysCmd acSysCmdClearStatus
End Function

Public Sub DropTable(TableName)
Dim db As Database
Set db = CurrentDb()
On Error Resume Next 'Ignore errors
db.TableDefs.Delete TableName
'DoCmd.DeleteObject acTable, TableName SLOW!!!!!
On Error GoTo 0 'Resume normal error handling
db.TableDefs.Refresh
End Sub

Private Sub AttachTable(tblx As String, db As Database, DBPath)
Dim tbl As TableDef
db.TableDefs.Refresh
Set tbl = db.CreateTableDef(tblx)
tbl.Connect = ";DATABASE=" & DBPath
tbl.SourceTableName = tblx
db.TableDefs.Append tbl
db.TableDefs.Refresh
End Sub

[MS]

<dr**********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Here are some functions to close all forms, disconnect all tables,
connect them all again. I haven't tried them after a broken network
connection - I use them to switch between a development and a
production environment - but think they should work.
Assuming you can trap the error when it occurs and call them.
Your boss's attitude is false economy.
The big worry with faulty networks is that they will eventually corrupt
your backends.
If you're lucky you'll be able to repair them; if you're not, your boss
will lose his data.
Terry Bell
Melbourne
Function CloseAllForms(Optional except)
Dim frm As Form, intI As Integer, i As Integer
Dim intJ As Integer, ExceptForm
Dim intControls As Integer, intforms As Integer
intforms = Forms.Count ' Number of open forms.
If IsMissing(except) Then
ExceptForm = ""
Else
ExceptForm = except
End If

For i = Forms.Count - 1 To 0 Step -1
If Forms(i).Name <> ExceptForm Then
DoCmd.Close acForm, Forms(i).Name
End If
Next i
End Function

Public Function DetachAllTables()
Dim db As Database, BackEndDb As Database, tbl As TableDef, x
SysCmd acSysCmdSetStatus, "Detaching Tables"
Set db = CurrentDb()
For Each tbl In db.TableDefs
If tbl.Attributes = dbAttachedTable Then
SysCmd acSysCmdSetStatus, "Detaching " & tbl.Name
DropTable tbl.Name
End If
Next
db.TableDefs.Refresh
End Function

Public Function AttachAllTablesIn(DBPath)
Dim db As Database, BackEndDb As Database, tbl As TableDef, x
'Open the backend database and connect all the tables
Set BackEndDb = DBEngine(0).OpenDatabase(DBPath)
For Each tbl In BackEndDb.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then 'Don't attach hidden system
tables
SysCmd acSysCmdSetStatus, "Attaching " & tbl.Name
AttachTable tbl.Name, CurrentDb(), DBPath
End If
Next
CurrentDb().TableDefs.Refresh
SysCmd acSysCmdClearStatus
End Function

Public Sub DropTable(TableName)
Dim db As Database
Set db = CurrentDb()
On Error Resume Next 'Ignore errors
db.TableDefs.Delete TableName
'DoCmd.DeleteObject acTable, TableName SLOW!!!!!
On Error GoTo 0 'Resume normal error handling
db.TableDefs.Refresh
End Sub

Private Sub AttachTable(tblx As String, db As Database, DBPath)
Dim tbl As TableDef
db.TableDefs.Refresh
Set tbl = db.CreateTableDef(tblx)
tbl.Connect = ";DATABASE=" & DBPath
tbl.SourceTableName = tblx
db.TableDefs.Append tbl
db.TableDefs.Refresh
End Sub

Tnak you, I'll try them out.

I keep a daily copy of the BEs just in case.