Tbles, Records, Code

[ebasta]

Hi!!!!

A really easy question for you:

How can I Open a Table, put in it new records and in a second moment delete all the records?
Ah, of course, everything must be hidden to the user and I need to put this code in the event _click of a Button!

thank you very much!

Mara Meo

 

[scottn]

Hi Mara,

There's a variety of ways to do both, of course. To insert new records using ADO, you can do something like this:

Dim rst as ADODB.Recordset

Set rst = New ADODB.Recordset  'Instantiate a new recordset
With rst
    .Open "myTable", CurrentProject.Connection, adOpenDynamic  'Open recordset against your table

    .AddNew  'Add new record
        .Fields("FieldName") = someValue  'Set values of the various fields
        'etc
    .Update  'Commit the change
End With

rst.Close  'Always explicitly close your recordsets...
Set rst = nothing  '...and release the reference

You can use the recordset's Delete and MoveNext methods to write a loop that deletes all the records, but you could also just use a bulk SQL statement:

CurrentProject.Connection.Execute "DELETE FROM myTable"

Careful! A statement like that one really will delete ALL the records in the table -- without giving you a chance to confirm the action, either. If you want to delete only certain records, you can use a WHERE clause in the SQL above.