Deleting Access linked tables via VB outside Access

[ijamal]

Dear members,

I am trying to delete a link after a table has been updated. When I run this code I get "Error 91"- Object variable or with block variable not set. The code works fine without the "IF" statement. But I would like to have the link deleted automatically after the update has been performed.

Please note this code is being run outside of an Access DB.

Any help on this will be most appreciated.

Thanks in advance.

Irshad

Private Sub CommandButton1_Click()

Dim strTemp As String
Dim strSQL 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)

If tbl.Name = "System" Then
db.TableDefs.Delete tbl
Else
Set tbl = db.CreateTableDef("System")
tbl.Connect = ";DATABASE=" & strDBInput
tbl.SourceTableName = "System"
db.TableDefs.Append tbl
Set tbl = Nothing
Set db = Nothing
End If


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.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)
Debug.Print (strSQL)
Call mdb.Execute(strSQL)
mdb.Close
Set mdb = Nothing
DoEvents
MsgBox ("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

 

[RuralGuy]

Try changing this:
If Dir(strSrceDB) = "" Then
to:
If Len(Dir(strSrceDB) & "") = 0 Then

 

[ijamal]

Deleting Access Table link after update outside an Access DB Version: 2003

Hi RuralGuy,

The problem is at this line:

If tbl.Name = "System" Then

etc....

Thanks,

Irshad

 

[RuralGuy]

I believe you are missing a Set command:
Set db = DBEngine.OpenDatabase(strSrceDB)
Set tbl = db.CreateTableDef(strDBInput)

 

[ijamal]

Deleting link from Access DB via code

Dear RuralGuy,

I actually have used the "Set" statement, with the name of the table to link to (System table).

The "update" takes place fine but I need help with the code to delete the link after update has been completed.

Appreciate your help.

Thanks.

Irshad

Set tbl = db.CreateTableDef("System")

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)
Debug.Print (strSQL)
Call mdb.Execute(strSQL)
mdb.Close
Set mdb = Nothing
DoEvents
MsgBox ("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

 

[RuralGuy]

Is this what you are looking for?
'Remove the reference to the System Table
CurrentDb.TableDefs.Delete "System"

 

[ijamal]

Thanks

Hi RuralGuy,

Thanks, that is what I needed! It works.

Best regards,

Irshad