Can't delete all records from some tables - VB4/32 - NWind.mdb sample 
Author Message
 Can't delete all records from some tables - VB4/32 - NWind.mdb sample

If anyone could tell me what is going on here I appreciate it. I have
done quite a bit of Access/VB programming and just ran into a
stumbling block that I can't for the life of me figure out.
Using the sample Nwind.mdb that ships with Access 2.0, I am using the
following code:

Set DB = DBEngine.OpenDatabase(DupDB)
        For i = 0 To DB.TableDefs.Count - 1
            TheTable = DB.TableDefs(i).Name
            If Not Left(TheTable, 4) = "MSys" Then
                frmMain.lblStatus = "Clearing data from " & TheTable &
"...please wait."
                frmMain.Refresh
                DB.Execute "DELETE * from [" & TheTable & "]"
                MsgBox "There were " & DB.RecordsAffected & " records
deleted from " & TheTable & "."
            End If
        Next
        DB.Close
        Set DB = Nothing

It simply does not delete all of the records from the first three
tables! I have tried the EXECUTE method with and without the square
brackets, it works both ways except when I get to a table that has a
space in the name like 'ORDER DETAILS', then the brackets are
necessary.

First Table: CATEGORIES
There are 8 records in the table and none are deleted although the
code executes without any errors, the RecordsAffected property returns
0.

Second Table: CUSTOMERS
There are 90 records and only one gets deleted

Third Table: EMPLOYEES
There are 15 records and only 6 of them get deleted

Now, the fourth table has a space in the name: ORDER DETAILS
There are 2000+ records in here and all of them get deleted, the rest
of the tables all get cleared out fine too.

I have looked at the relationships between the tables and can't find
anything that would prevent some of the records from being deleted, if
I was using a WHERE clause in the DELETE statement I could see this
happening, but there's not a where clause in it.
I have tried this several times with the same results. Now if I open
the database in Access, I can delete everything without any problems
but I need to do it via VB code.

Any ideas or suggestions would be greatly appreciated.

Ed Phillippe



Mon, 10 May 1999 03:00:00 GMT  
 Can't delete all records from some tables - VB4/32 - NWind.mdb sample

Hello Ed,

I would assume the records cannot be deleted because the referential
integrity rules applied to the database relationships would be broken.
If you were to use dbFailOnError as the options parameter to the
execute statement, an trappable error would occur. This would prove if
this was the case or not.

To achive what you want to do, you would have to change the
referential integrity of the tables, or delete to contains of each
table in an order that would not cause this problem.

        Hope this helps

         Alan Davis, Southampton, England



Mon, 10 May 1999 03:00:00 GMT  
 Can't delete all records from some tables - VB4/32 - NWind.mdb sample

You're probably running into a referential integrity (ri) problem.  If
there are relationships from tableA to tableB and ri is turned on, you
won't be able to delete any rows in tableA that have dependencies in
tableB.

Check the relationships using Data Manager or Access.
--
Keith Benedict
Software Engineer
Triad Systems



: If anyone could tell me what is going on here I appreciate it. I have
: done quite a bit of Access/VB programming and just ran into a
: stumbling block that I can't for the life of me figure out.
: Using the sample Nwind.mdb that ships with Access 2.0, I am using the
: following code:
:
: Set DB = DBEngine.OpenDatabase(DupDB)
:         For i = 0 To DB.TableDefs.Count - 1
:             TheTable = DB.TableDefs(i).Name
:             If Not Left(TheTable, 4) = "MSys" Then
:                 frmMain.lblStatus = "Clearing data from " & TheTable &
: "...please wait."
:                 frmMain.Refresh
:                 DB.Execute "DELETE * from [" & TheTable & "]"
:                 MsgBox "There were " & DB.RecordsAffected & " records
: deleted from " & TheTable & "."
:             End If
:         Next
:         DB.Close
:         Set DB = Nothing
:
: It simply does not delete all of the records from the first three
: tables! I have tried the EXECUTE method with and without the square
: brackets, it works both ways except when I get to a table that has a
: space in the name like 'ORDER DETAILS', then the brackets are
: necessary.
:
: First Table: CATEGORIES
: There are 8 records in the table and none are deleted although the
: code executes without any errors, the RecordsAffected property returns
: 0.
:
: Second Table: CUSTOMERS
: There are 90 records and only one gets deleted
:
: Third Table: EMPLOYEES
: There are 15 records and only 6 of them get deleted
:
: Now, the fourth table has a space in the name: ORDER DETAILS
: There are 2000+ records in here and all of them get deleted, the rest
: of the tables all get cleared out fine too.
:
: I have looked at the relationships between the tables and can't find
: anything that would prevent some of the records from being deleted, if
: I was using a WHERE clause in the DELETE statement I could see this
: happening, but there's not a where clause in it.
: I have tried this several times with the same results. Now if I open
: the database in Access, I can delete everything without any problems
: but I need to do it via VB code.
:
: Any ideas or suggestions would be greatly appreciated.
:
: Ed Phillippe
:
:
:



Tue, 11 May 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Sample Apps don't work in 32-Bit VB4

2. Deleting a table from Access database - VB4 32 Pro

3. How recovery deleted records in mdb(97')?

4. Cannot add record to Visual Foxpro table after deleting all records in table

5. VB4/32 Sample Edit project

6. VB4 32 goodies (AddIn, OCX, Samples)

7. VB4.0-32 Assertion Failed W/ VC++ 2.0 Sample OCX

8. Russian(Cyrillic): vb4 32-bit works, but vb4 16-bit doesn't

9. Any sample of VB4 making calls to 32 bit dll

10. Fastest MDB Database Access from VB4/32

11. error message with Nwind.mdb

 

 
Powered by phpBB® Forum Software