Hello,
I have an Access 2003 database with about 49 tables. I want to delete
some records based on a column value ("StudyID") on all the tables in this
database using ADO with VB6. The cosde supposedly should do this. But it
doesn't work. First there is temporary table starting with ~. I had to trap
that. Then if there are tables where there is no "StudyID" field, the code
doesn't work. And secondly if there are no records containing "StudyID=xxx"
then Delete command bombs with an error code 3265. So how can I do this?
Thank you.
Athena
Code
-----------------------------------------------------------------
Private Sub cmdDeleteStudy_Click()
Dim res As Long
Dim sDatabase As String
On Error GoTo ErrHandler
StudyName = TreeView1.SelectedItem.text
sDatabase = TreeView1.SelectedItem.Parent.text
res = MsgBox("Do you want to delete " & StudyName & " from " & sDatabase
& "?", vbOKCancel, "Delete Study")
If res = vbOK Then
res = MsgBox(StudyName & " will be deleted from " & sDatabase & "?",
vbOKCancel, "Confirmation")
If res = vbOK Then
Screen.MousePointer = vbHourglass
' Open new database
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dBaseName & ";" & _
"Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open
Set RS = conn.OpenSchema(adSchemaTables, Array(Empty, Empty,
Empty, "Table"))
Do While Not RS.EOF
If InStr(RS!Table_Name, "~") = 0 Then
sSql = "DELETE * FROM " & RS!Table_Name & " WHERE
StudyID='" & StudyName & "'"
Set RS = conn.Execute(sSql, , adCmdText)
End If
Debug.Print RS!Table_Name
RS.MoveNext
Loop
TreeView1.Nodes.Remove (TreeView1.SelectedItem.Index)
' MDIMain!stbMain.Panels(1).text = "Study " & StudyName & " is
deleted from " & sDatabase & "."
Screen.MousePointer = vbDefault
End If
End If
Exit Sub
ErrHandler:
If Err.Number = -2147217904 Then
Resume Next
Else
MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbCritical
End If
End Sub
|
Similar Threads
