Utility to remove data

[John]

I need to share a database with private info with a developer (see
another post from me if you can help) that will help with reports.

Is there any utility to keep the structure of the database, but remove
all data records?

John

 

[George Nicholson]

- Create a new, blank database
- File>GetExternalData>Import>Browse to file
- In the resulting dialog:
-Select all objects (non-system tables, forms, reports, macros,
queries, modules, etc.)
-Under "Options: Import Tables" select "Definition Only" rather than
"Definition and Data"

That said, since reports are simply presentations of data, I'm not sure how
anyone would be able to give you much help with them if data tables are
empty. Consider an update query that randomizes (within specified ranges)
the values of certain fields ranges instead, so potentially confidential
information is replaced with test garbage. And/or replace client names with
a string of random letters [maybe keeping a reference table so you can
change client names back ].

HTH,


-

 

[Arvin Meyer [MVP]]

I need to share a database with private info with a developer (see
another post from me if you can help) that will help with reports.

Is there any utility to keep the structure of the database, but remove
all data records?

Create a new empty database

Select:

File >>> Get External Data

and choose your database. Then click the Import button. On the next dialog,
click the Options button and choose:

Definitions Only

 

[Pieter Wijnen]

Here's one

Sub ClearTables()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset, Rs2 As DAO.Recordset
Dim thQ As String
Dim DoReRun As Boolean

Set Db = Access.CurrentDb
thQ = "SELECT NAME FROM MSysObjects WHERE Type=1 And Name Not Like 'MSys'"
Set Rs = Db.OpenRecordset(thQ, DAO.dbOpenSnapshot)
ReRun:
Rs.MoveFirst
DoReRun = False
While Not Rs.EOF
thQ = "SELECT COUNT(*) FROM " & Rs.Fiels(0).Value
Set Rs2 = Db.OpenRecordset(thQ, DAO.DbOpenSnapshot)
If Rs2.Fields(0).Value > 0 Then
Db.Execute "DELETE FROM " & Rs.Fields(0).Value, DAO.dbSeeChanges
If Db.RecordsAffected <> Rs2.Fields(0).Value Then DoReRun = True '
Related Data exists
End If
Rs2.Close : Set Rs2 = Nothing
Rs.MoveNext
Wend
If DoReRun Then Goto ReRun
Rs.Close : Set Rs = Nothing
Set Db = Nothing
End Sub

HtH

Pieter

 

[RobWhood]

Thank you. I will enter a fake record on the tables.