Creating a sample of my database

[ChrisSedgwick]

Hi there!

I hope that what I'm about to write makes sense and I don't cause too much confusion...

We have quite an extensive database (or at least it's extensive for us), around 250mb. It's a split database with the back-end being held on our server. It holds around 6 years worth of information. Information such as jobs, products, quotes, deliveries, invoices, purchases, installations and so on. Its made up of numerous table, queries and VBA etc.

We're having some work carried out on the database and have been requested to send a copy of the database, so that the person doing the upgrades can see how it works. However, I'm reluctant to send the full database, simply because it holds so much information (data protection etc).

Now for my question.. is there a way I can send a sample of the database, either by exporting information into a new database or something similar, so that they can see how the database works? Obviously all the queries and the programming behind would have to go too.

I'm no expert in databases so am asking really, is there a way this can be done, simple enough for a dummy?

Any help would be greatly appreciated.

Many thanks,
Chris.

 

[Minty]

There is no super quick way of doing this, but the steps would be along the lines of
Make a copy of your whole database.

Look at the tables that hold the sensitive information and or loads of records.

If you have employee or customer info, then you're going to have to remove most of it, then "munge" the remaining records, whilst ensuring you have enough records that all the forms still work and referential integrity is maintained.
(e.g. It's pointless deleting all but 2 or 3 customer accounts but not having any job records for those customers.)

No short easy way I'm afraid with a complex system. The only other route is to empty all the tables, and simply create new dummy records, but that tends to not represent the real world very well, and can take just as long.

 

[CJ_London]

you could create a new db then import the table definitions only (see options button). Also include relationships

now link to the tables so you will have

myTable (local)
myTable1 (linked)

then write some queries to copy across the data required. Probably all the records in the lookup type tables. Then based on relationships, append perhaps say top 20 customers, then the invoice headers relating to those customers then the invoice lines etc. Point is start with the 'highest' parent

Even with autonumber fields, an append query will append the existing number if it doesn't already exist so you will preserve your parent/child relationships

Focus or even limit to those tables that relate to the work being carried out.

queries etc will be in the front end so do not contain data

once done, remove the linked tables