I
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
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