How can i clear the contents of an existing db to start again.

[Guest]

I have an existing database but the entries are old. Can i clear the contents
and start again?

 

[Vincent Johns]

I have an existing database but the entries are old. Can i clear the contents
and start again?

Yes. (I assume you've made a backup copy.) Open each Table in
Datasheet View, click in the upper left corner to select all the
records, and press the Delete key.

This won't (or may not) work for linked Tables, however. For those,
you'll need either to empty the data from the linked source files, or
you'll need to link to new, empty source files.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Van T. Dinh]

***Make a back-up of your database first****

1. Delete all Records in every Tables and then Compact and Repair the
database.

2. Alternatively, you can simply create a blank database and import all
objects from the existing database but select theoption "Structure only" and
not "Structure and Data" for the Tables. If you have custom toobars or
import / export specs, make sure you check the options to import them also.

For this option, you may need to adjust the Start-up options and the
References to be the same as the old database. Make sure you compile the
code at the end.

 

[Douglas J. Steele]

Vincent and Van have told you the mechanics of how to delete from existing
tables. I thought I'd point out that if you've set up relationships between
the tables to enforce Referential Integrity, you may have to futz around to
determine the correct sequence in which to delete the data. For example, if
you've got RI set up on a 1-to-many relationship between two tables, you
need to delete from the many side before you can delete from the 1 side.

 

[Arno R]

Vincent and Van have told you the mechanics of how to delete from existing
tables. I thought I'd point out that if you've set up relationships between
the tables to enforce Referential Integrity, you may have to futz around to
determine the correct sequence in which to delete the data. For example, if
you've got RI set up on a 1-to-many relationship between two tables, you
need to delete from the many side before you can delete from the 1 side.

You could also delete all the tables in a loop and repeat the loop a few (3-4) times.
Something like this code executed from the backend (no linked tables here):
You can call this function in a macro if you like

Function DeleteDb ( )
On Error goto Err_DeleteDb
Dim db as Database, strSQL as string, i as integer, x as integer
Set db = CurrentDb
For i = 1 To 3 '3 or even 4 times may be needed because of RI
For x = 0 To db.Tabledefs.Count - 1
Select Case Left(db.Tabledefs(x).Name, 4)
Case "MSys" 'Don't delete the MSys* tables
Case Else
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End Select
Next x
Next i
Exit Function
Err_DeleteDb:
Msgbox Err & " " & Error$, vbCritical, "Error in function DeleteDb"
Exit Function
End Function

After this function you will need a compact.

Arno R

 

[Douglas J. Steele]

Good idea, Arno. Of course, you might have a need something to expand which
tables you're excluding from deletion: sometimes you have tables of
legitimate values you want to keep, like countries or provinces/states.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Vincent and Van have told you the mechanics of how to delete from existing
tables. I thought I'd point out that if you've set up relationships
between
the tables to enforce Referential Integrity, you may have to futz around
to
determine the correct sequence in which to delete the data. For example,
if
you've got RI set up on a 1-to-many relationship between two tables, you
need to delete from the many side before you can delete from the 1 side.

You could also delete all the tables in a loop and repeat the loop a few
(3-4) times.
Something like this code executed from the backend (no linked tables here):
You can call this function in a macro if you like

Function DeleteDb ( )
On Error goto Err_DeleteDb
Dim db as Database, strSQL as string, i as integer, x as integer
Set db = CurrentDb
For i = 1 To 3 '3 or even 4 times may be needed because of RI
For x = 0 To db.Tabledefs.Count - 1
Select Case Left(db.Tabledefs(x).Name, 4)
Case "MSys" 'Don't delete the MSys* tables
Case Else
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End Select
Next x
Next i
Exit Function
Err_DeleteDb:
Msgbox Err & " " & Error$, vbCritical, "Error in function DeleteDb"
Exit Function
End Function

After this function you will need a compact.

Arno R

 

[Douglas J. Steele]

Actually, rather than

Select Case Left(db.Tabledefs(x).Name, 4)
Case "MSys" 'Don't delete the MSys* tables
Case Else
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End Select

you might want to use

If (db.Tabledefs(x).Attributes And dbSystemObject) <> 0 Then
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End If

That way, you can easily add:

If (db.Tabledefs(x).Attributes And dbSystemObject) <> 0 Then
Select Case db.Tabledefs(x).Name
Case "Province", "Country"
' Do nothing
Case Else
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End Select
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good idea, Arno. Of course, you might have a need something to expand
which tables you're excluding from deletion: sometimes you have tables of
legitimate values you want to keep, like countries or provinces/states.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Vincent and Van have told you the mechanics of how to delete from
existing
tables. I thought I'd point out that if you've set up relationships
between
the tables to enforce Referential Integrity, you may have to futz around
to
determine the correct sequence in which to delete the data. For example,
if
you've got RI set up on a 1-to-many relationship between two tables, you
need to delete from the many side before you can delete from the 1 side.

You could also delete all the tables in a loop and repeat the loop a few
(3-4) times.
Something like this code executed from the backend (no linked tables
here):
You can call this function in a macro if you like

Function DeleteDb ( )
On Error goto Err_DeleteDb
Dim db as Database, strSQL as string, i as integer, x as integer
Set db = CurrentDb
For i = 1 To 3 '3 or even 4 times may be needed because of RI
For x = 0 To db.Tabledefs.Count - 1
Select Case Left(db.Tabledefs(x).Name, 4)
Case "MSys" 'Don't delete the MSys* tables
Case Else
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End Select
Next x
Next i
Exit Function
Err_DeleteDb:
Msgbox Err & " " & Error$, vbCritical, "Error in function DeleteDb"
Exit Function
End Function

After this function you will need a compact.

Arno R

 

[Arno R]

Actually, rather than

Select Case Left(db.Tabledefs(x).Name, 4)
Case "MSys" 'Don't delete the MSys* tables
Case Else
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End Select

you might want to use

If (db.Tabledefs(x).Attributes And dbSystemObject) <> 0 Then
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End If

That way, you can easily add:

If (db.Tabledefs(x).Attributes And dbSystemObject) <> 0 Then
Select Case db.Tabledefs(x).Name
Case "Province", "Country"
' Do nothing
Case Else
strSQL="DELETE * FROM [" & db.Tabledefs(x).Name & "]"
db.execute (strSQL)
End Select
End If

Hi Doug,
Just tested this line here:
If (db.Tabledefs(x).Attributes And dbSystemObject) <> 0 Then
this errors because you are trying to delete sys-tables here... typo ??

should be ...
If (db.Tabledefs(x).Attributes And dbSystemObject) = 0 Then

In fact I *do* have another Select Case -- End Select structure in the loop after my first Case Else to exclude some of my lookup-tables indeed.
I left that out because I thought the OP wanted to 'clean' the db completely.
But I guess its good for other readers you added it.

Arno R

 

[Douglas J. Steele]

Hi Doug,
Just tested this line here:
If (db.Tabledefs(x).Attributes And dbSystemObject) <> 0 Then
this errors because you are trying to delete sys-tables here... typo ??

should be ...
If (db.Tabledefs(x).Attributes And dbSystemObject) = 0 Then

In fact I *do* have another Select Case -- End Select structure in the loop
after my first Case Else to exclude some of my lookup-tables indeed.
I left that out because I thought the OP wanted to 'clean' the db
completely.
But I guess its good for other readers you added it.


Yup, should have been = 0. I copied and pasted from the wrong sample module.

Thanks for catching that.

(BTW, why don't I get > signs on your responses when I try to respond?)

 

[Arno R]

(BTW, why don't I get > signs on your responses when I try to respond?)

I have no idea really ...

But ...
Maybe something strange with OE because the newgroupaccount for this particular ng is 'presenting' me still as StopThisAdvertising...
So while answering you here I changed the account for this message back to normal...
(Sometimes I forget to do this...)

Arno R