Loosing link to backend every night

[GPM4663]

Hi,
I have a multiuser database with a backend on our network and the front end of each users desktop. Each morning when I come in I need to go to database utilities and refresh my links to the backend otherwise all the listboxes etc are blank. I've tried to automate the process using the code to relink the tables but that doesn't do the trick. This does not happen to every user, only myself and one other - the only similarity between us is that we both have Access 2000 and Access XP on our computers. Other users only have Access2000 which is the format both the fe and be are in. Any help would really be appreciated.

 

[hermanlaksko]

I use a tecnique that handles just such a problem, as access will think that the table is there if access can see the table. Seing the table does not, however mean that the table can be opend AND show the relevant data..... Grrrr had me spooked for a while, so the trick.. is to open the table and if an error occours, delete the table and reattach.

Function IsTableChk(TableName) As Boolean ' Is table in Database
On Error Resume Next
Dim DB As DAO.Database
Set DB = DBEngine.Workspaces(0).Databases(0)

Application.Echo False
DoCmd.OpenTable TableName
If Err <> 0 Then DoCmd.DeleteObject acTable, TableName Else IsTableChk = True

Exit_IsTable:
DoCmd.Close acTable, TableName
Application.Echo True
Exit Function
Err_IsTable:
IsTableChk = False
Resume Exit_IsTable
End Function

I have a local table with names of all attached tables if I find a table is not correctly attached I reattach the whole thing via:

Function AttachDatabase(AttDatabase)
On Error GoTo Err_AttachDatabase
Dim Re As DAO.Recordset, Tal As Integer
DoCmd.Hourglass True
Set Re = CurrentDb.OpenRecordset("Select * From Z_Tabel Where TabelType='YrName'")
Re.MoveLast
SysCmd acSysCmdInitMeter, "Attaching database", Re.RecordCount
Re.MoveFirst
Do While Not Re.EOF
AttachTable AttDatabase, Re!TabelNavn
Tal = Tal + 1
SysCmd acSysCmdUpdateMeter, Re.AbsolutePosition
Re.MoveNext
Loop

Exit_AttachDatabase:
SysCmd acSysCmdRemoveMeter
DoCmd.Hourglass False
Exit Function
Err_AttachDatabase:
MsgBox Error$, , "YrApp"
Resume Exit_AttachDatabase
End Function

Sub AttachTable(AttDatabase, AttTable)
On Error GoTo Err_AttachTable
Dim MyTable As TableDef, MyField As Field, Svar
On Error Resume Next

If Left(AttDatabase, 4) = "ODBC" Then
Set MyTable = CurrentDb.CreateTableDef(AttTable, dbAttachSavePWD, AttTable, AttDatabase & AttTable)
CurrentDb.TableDefs.Append MyTable
Else
CurrentDb.TableDefs(AttTable).Connect = ";DATABASE=" & AttDatabase
'Debug.Print Err & " - " & Error(Err)
If Err = 3265 Then
Set MyTable = CurrentDb.CreateTableDef(AttTable)
MyTable.Connect = ";DATABASE=" & AttDatabase
MyTable.SourceTableName = AttTable
CurrentDb.TableDefs.Append MyTable
Else
Set MyTable = CurrentDb.CreateTableDef(AttTable)
MyTable.Connect = ";DATABASE=" & AttDatabase
MyTable.SourceTableName = AttTable
CurrentDb.TableDefs.Delete (AttTable)
CurrentDb.TableDefs.Append MyTable
End If
End If

Exit_AttachTable:
Exit Sub
Err_AttachTable:
If Err <> 3265 Then MsgBox "Attach Table: " & Err.Description, , "YrApp"
Resume Exit_AttachTable
End Sub

Not sure how much of the above code you need, but try what you like and see if this does not solve your problem.

Herman
Say no to macros

 

[GPM4663]

Hi Hermone,
At what point to I call these functions/procedures and what arguments am I supposed to be passing to them i.e. attDatabase etc. I just don't fully understand the code and was hoping you could explain it to me.

Many thanks,

GPM

 

[hermanlaksko]

Sure thing.
drop this into a module and call it from your autoexec macro

Function StartPtogram()
Dim ResStr, Re as DAO.RecordSource
If IsTableChk("YrTbl")=false then
ResStr=CurrentDb.TableDefs("YrTbl").Connect
set re=Currentdb.OpenRecordset("Select * From YrLocalTblThatHoldsAllTabelNames")
do while not re.eof
AttachDatabase re!TblName
re.movenext
loop
End function

Herman
Say no to macros