Mechanism - relink linked tables within a client automatically - Access 97 - whe

[ImraneA]

Hi there

Problem :- All my clients (located on C:\ or h:\
(individual network share)) have linked tables. How do
you create a mechanism that allows automatic relinking of
databases, if network share been moved.

Timesheet database located (h:\) client

e.g.

Name Database
Acc_Activity \\Gwwnt08
\Databases\Common\TES-97 Timesheet Conversion
NEW\Main_Databases\Staff_tm.mdb
Acc_Projects \\Gwwnt08
\Databases\Common\TES-97 Timesheet Conversion
NEW\Main_Databases\Staff_tm.mdb
Acc_Res
\\Gwwnt08\Databases\Common\TES-97 Timesheet Conversion
NEW\Main_Databases\Staff_tm.mdb
tbl_Approvers \\Gwwnt08
\Databases\Common\TES-97 Timesheet Conversion
NEW\Main_Databases\WWL_TMSH.MDB
Tbl_Databases \\Gwwnt08
\Databases\Common\TES-97 Timesheet Conversion
NEW\Main_Databases\WWL_TMSH.MDB
Tbl_Departsments
\\Gwwnt08\Databases\Common\TES-97
Timesheet Conversion NEW\Main_Databases\WWL_TMSH.MDB
Tbl_Work_Category
\\Gwwnt08\Databases\Common\TES-
97 Timesheet Conversion NEW\Main_Databases\WWL_TMSH.MDB
tblWWL_Timesheet_Change_Control
\\Gwwnt08\Databases\Common\WWL.Time Interface\WWL_Time.mdb
tblWWL_Timesheet_WWLDocumentNo
\\Gwwnt08\Databases\Common\WWL.Time Interface\WWL_Time.mdb
WWL_TimeSheets
\\Gwwnt08\Databases\Common\TES-97 Timesheet Conversion
NEW\Main_Databases\WWL_TMSH.MDB

---> Say, share "Databases" moved from gwwnt08 to gwwnt06

Background for Change
1.Using a SAND to locate all files as basis for future
storage plus backup.

Tried
1.Use File DSN - problem, cannot handle multiple
datasource ! (see above)

2.Use central table as a reference, when user logs on,
link tables...
--------> Using TransferDatabase command locks the
database......(see last procedure)

3.Using customized security workgroup, also on network !
Any ideas ???

-----------------------------------------------------------
-----------------------------------------------------------
---------------------------------------------
Function Autoexec()
On Error GoTo Err_Autoexec

'***AIM-Function that runs at database load
Const A_WINDOWMENU = 4
Const A_HIDE = 3
....
Call glr_attach_common
...

Exit_Autoexec:
Exit Function

Err_Autoexec:
msgbox "Error " & ERR & " : " & Error$, 0, "Autoexec"
Resume Exit_Autoexec

End Function

-----------------------------------------------------------
-----------------------------------------------------------
---------------------------------------------
Sub glr_attach_common()
On Error GoTo Err_GLR_ATTACH_COMMON

'***AIM-Delete links first, if any
Call Check1_AnyAttachedTables

'***AIM-Attach links
Call Check2_AttachedTables

Exit_GLR_ATTACH_COMMON:
DoCmd.Hourglass False
Exit Sub

Err_GLR_ATTACH_COMMON:

'***AIM-Error Handler
Select Case ERR
....
Resume Exit_GLR_ATTACH_COMMON

End Sub
-----------------------------------------------------------
-----------------------------------------------------------
---------------------------------------------
Function Check2_AttachedTables()

'***AIM-Attach tables
Dim wrkJet As Workspace, dbsChange As Database,
wrkODBC As Workspace, tdfTmp As TableDef, strPrefix As
String, strNewConnect As String
Dim A As String, B As String, C As String, S As
String, F As Form, K, V, J
Dim D As Database, R As Recordset
S = "SELECT F2, F3, F4, F5, F6, F7, F8 FROM
PathTextTech WHERE F1='Retrieve' AND Database='DocControl'"
Set D = CurrentDb
Set R = D.OpenRecordset(S)
'***AIM-LOOP THROUGH RECORDS IN PathTextTech
Do Until R.EOF
DoCmd.TransferDatabase acLink, "Microsoft Access",
R!F2, acTable, R!F4, R!F5
R.MoveNext
Loop

End Function

 

[nieurig]

Hi,
i relink my tables using following code

DELETING the Table-Defs

Anzahl = db.TableDefs.Count - 1
For i = Anzahl To 0 Step -1
Set atab = db.TableDefs(i)
If (atab.Attributes And dbAttachedTable) Then
db.TableDefs.Delete atab.name
End If
Next

BUILD NEW TableDefs

Dim Tabelle As TableDef
Set Tabelle = db.CreateTableDef(newTabName)
Tabelle.Connect = strConnect
Tabelle.SourceTableName = strSourceTable
db.TableDefs.Append Tabelle

(for Syntax for .Connect see Online Help. It looks like
DATABASE=H:\MYDATA.MDB)

That works, because I know where the Client knows from with
Networkdrive it comes from (Application.CurrentDB.Name
include the path to the Database.

If your Access-Client starts from a Clientcomputer you
should use a Ini-File with tells the Client where the
Database is.

Good luck.
Niels