How to make a copy of a split database?

[hedges98]

There is probably an easy solution to this but I'm paranoid about making any changes to the database in case I do something that can't be reverted back.

Basically, I have a split database - backend is on the shared drive and the frontend has been distributed on each users computer.

Someone has requested me to send them the database. Now, in the interest of confidentiality, I need to send him a blank version. Is this a simple case of copying both frontend and backend? I'm a bit apprehensive to delete all the data from the backend and then accidentally realise I've ballsed it up.

[NeoPa]

What about deleting the data after you've made the copy. That way you don't have any such worry.

[NeoPa]

The problem that is more likely is that the destination environment may not match the source one exactly, and that could cause the databases to be unusable. If possible, ensure the receiver of your databases knows where the original BE is stored.

EG. If you link to it and it is at G:\MyDatabases\DatabaseName.Mdb, then let them know that as they will most likely need to duplicate that on their systems otherwise the links will fail.

Alternatively, you could put some code in your FE that manages the links for you. Link Tables in VB may help with that.

[hedges98]

@NeoPa
Is it that simple?

I just need to send him a copy as he wants to look over the structure of it and stuff but there is obviously confidentiality issues with sending it as it is (not to mention the file size being too big to email with all the data!)

If I just copy the backend and a version of the front end - how will the front end not be attached to the original backend? Like, how do you link the copied backend to a frontend (or vice versa)? I'm probably overcomplicating things here!

[NeoPa]

When you make the link to the BE table, Access stores this same value in the database associated with the link.

If you use UNC versions then these will be stored. If you use drive letters instead, then these will be. This means that the user has to set their system up to match, or they can relink the tables if they're savvy enough.

If you're thinking of sending a copy via email then the following instructions may be of some use to you (although compiled for another purpose) :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[missinglinq]

As to the comfidentiality issue, to "copy" the back end

1. Create a new, blank database file
2. Goto File
3. Click on Get External Data
4. Click on Import
5. Select the back end file
6. Click on Options
7. Under Import Tables click on Definition Only
8. Click Okay

Making sure that the new file is not in the same folder/location as the original backend, rename it the same as the back end.

Linq ;0)>

[hedges98]

@missinglinq
Thanks for the suggestions guys, all I did was copy both the frontend and backend then relink the copy of the frontend to the copy of the backend.

Once I'd done that (and tested that I wasn't going to mess with the original!) I just manually deleted the data from all the tables! Bit of a backwards solution but I think it worked all the same.

[NeoPa]

That should certainly work, but some databases can contain literally hundreds of tables. In such a case having a separate step to clear down the data for each can be somewhat cumbersome. Linq's solution reduces the overhead so is well worth having as a suggestion even if you didn't see it in time on this occasion.

[hedges98]

@NeoPa
Yeah, luckily this database is relatively small so it only took a few minutes to remove the data manually. I will keep these suggestions in mind should I need to do something similar in future though. Thank you!