Dear members,

I need help with code to delete the link after update is completed. Currently the script works fine. (Link added and tables updated).


Thanks in advance.

Irshad

Private Sub CommandButton1_Click()

Dim strTemp As String
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSrceDB As String
Dim strDBInput As String
Dim db As Object
Dim mdb As Object
Dim tbl As DAO.TableDef

strSrceDB = "C:\WSS_Khatlon.mdb"
strDBInput = "C:\Rehabilitated_Water_Supply_Kulyob.mdb"


Set db = DBEngine.OpenDatabase(strSrceDB)
Set tbl = db.CreateTableDef("System")

tbl.Connect = ";DATABASE=" & strDBInput
tbl.SourceTableName = "System"
db.TableDefs.Append tbl
Set tbl = Nothing
Set db = Nothing

On Error GoTo ErrorHandler


'Make sure it is there
If Dir(strSrceDB) = "" Then
Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")

Else
strSQL = "UPDATE System INNER JOIN Water_System " & "" & ""
strSQL = strSQL & "ON System.System_ID = Water_System.System_ID " & "" & ""
strSQL = strSQL & "SET System.Latitude = Water_System.Latitude, " & "" & ""
strSQL = strSQL & "System.Longitude = Water_System.Longitude, " & "" & ""


strSQL = strSQL & "System.Oblast_Name = Water_System.Oblast_Name," & "" & ""
strSQL = strSQL & "System.District_Name = Water_System.District_Name," & "" & ""
strSQL = strSQL & "System.Jamoat_Name = Water_System.Jamoat_Name," & "" & ""
strSQL = strSQL & "System.Village_Name = Water_System.Village_Name, " & "" & ""
strSQL = strSQL & "System.System_Type = Water_System.System_Type, " & "" & ""
strSQL = strSQL & "System.Is_Working = Water_System.Is_Working, " & "" & ""
strSQL = strSQL & "System.Dependance_Factor = Water_System.Dependance_Factor, " & "" & ""
strSQL = strSQL & "System.Date_Not_Working = Water_System.Date_Not_Working, " & "" & ""
strSQL = strSQL & "System.Storage_Capacity = Water_System.Storage_Capacity, " & "" & ""
strSQL = strSQL & "System.Power_Consumption = Water_System.Power_Consumption " & "" & ";"


Set mdb = DBEngine.OpenDatabase(strSrceDB)
Call mdb.Execute(strSQL)
mdb.Close
Set mdb = Nothing
DoEvents
MsgBox ("The Rehabilitated Water Infrastructure Database has been successfully updated!")
End If
Exit Sub
ErrorHandler:
strTemp = Err.Description & " [Update_SystemTab]"
Call MsgBox(strTemp, vbCritical, "Contact Help Desk")
End Sub