Hi All

I have a querytable that's linked to a table in an access database, I'm using ADO to delete a record from the table from an Excel form - problem is when I use VBA to refresh the querytable the record remains.

I can manually right click the querytable and refresh and the record will be removed, any idea's where I'm going wrong 

 

Private Sub CommandButton4_Click() 

      Dim conn As ADODB.Connection

      Dim sPath As String

      Dim strSQL As String

10       On Error GoTo CommandButton4_Click_Error

20    sPath = ThisWorkbook.path

30    If MsgBox("This will delete the record, are you sure?", vbYesNo Or vbCritical Or vbDefaultButton1, "Admin") = vbYes Then

40        Set conn = New ADODB.Connection 

50        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _

                  "Data Source=" & sPath & "\Elective_Staffing.mdb;" 

60        conn.Open 

70        strSQL = "DELETE Tbl_Elective_Budget.*, Tbl_Elective_Budget.PrimaryKey" _

              & " FROM Tbl_Elective_Budget WHERE (((Tbl_Elective_Budget.PrimaryKey)=" & Me.txtPrimaryKey.Value & "));" 

80        conn.Execute strSQL

90    End If 

100       Unload Me 

tidyup:

110       On Error Resume Next

120       conn.Close 

          Dim qt As QueryTable 

130       Set qt = ActiveSheet.QueryTables(1) 

140       With qt

150           .Connection = Array( _

              "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & sPath & "\Elective_Staffing.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False")

160           .Refresh BackgroundQuery:=False

170       End With 

180      On Error GoTo 0

190      Exit Sub

CommandButton4_Click_Error:

200       MsgBox "Error: (" & Err.Number & ") " & Err.Description & vbCrLf & _
                 "Error Line: " & Erl & vbCrLf & _
                 "in procedure CommandButton4_Click of Form UserForm1", vbCritical 

          Resume tidyup

End Sub
Thanks!