Access Deleting

[SAMS]

Hi,
I hope someone can help me with this problems:
1) I'm able to delete all the data from the table in access using VB, but the problem is can i don't delete the first six rows of the data & delete the rest of the data from the table?
2) Can i copy a whole rows(around 50 fields) of data from one table to another table?

Pls Help.
Thks.

[Serge]

For you first question:
You can use the folowing example. In this example I used imaginary table called Customers, but you can use whatever table you want:

    Dim db As Database
    Dim strSQL As String
    
    Set db = Workspaces(0).OpenDatabase("c:\mydb.mdb")
    
    strSQL = "DELETE Customers.*  "
    strSQL = strSQL & "From Customers "
    strSQL = strSQL & "WHERE Customers.CustomerID Not In (Select Top 5 CustomerID From Customers);"
    db.Execute strSQL, dbFailOnError

For you second question:
I used same Customers table and Customer2 table to get the records from:

    Dim db As Database
    Dim strSQL As String
    
    Set db = Workspaces(0).OpenDatabase("c:\mydb.mdb")
    
    strSQL = "INSERT INTO Customers "
    strSQL = strSQL & "SELECT Customers2.* "
    strSQL = strSQL & "FROM Customers AS Customers2;"
    db.Execute strSQL, dbFailOnError

Regards,

------------------

Serge

Software Developer
[email protected]
[email protected]

[SAMS]

Hi, Thks for your reply serge. But the code u gave me seen to be working for the first time only . After the seq. time the data is still in the table. Is it because this code only work for single rows? As my table consists of around 50 rows & col. Hope that u can help me.

Thks again for your time

[Serge]

If you mean that its now alowing you to add the same records from table 2, then check your table structure (i.e. if you have primary key in that table then its not going to give a chance to save the same record twice)

Regards,



------------------

Serge

Software Developer
[email protected]
[email protected]