If I want to duplicate just a table, I can easily select “structure only” under paste options. Is there an easy way to do the same thing for an entire database (tables, queries, etc.) all at once?
In short, someone has a database. They’re willing to share the database itself, but not necessarily its contents. The database has a user interface, so the people who use the database don’t necessarily know much about Access. Therefore, I can ask them to follow a few basic, built-in Access menu options, but can’t ask them to do something manually, such as copying the entire file and then manually emptying the tables. This seems like something that should be easy, but I can’t find it. Any help?
Thanks,
Rhythm
Can you give them a query to run that deletes all the data from every table?
I don’t know anything about Access, but a Google search for “msaccess ddl export” turned up some promising looking pages.
The ones I looked at seemed to indicate that Access can’t do it by itself (i.e. through the GUI), but that you can run a script which will save the definitions out to a text file. The script here might be one such, but I’ve really no idea.
I’m not sure how they’d feel about that, or how I’d feel asking.
Here’s a bit more background on what I’m working on to give you an idea of the relationships (people, not database). Our client is a department in a gargantuan non-profit. They commissioned us to do a study of a particular practice. A part of that study is contacting similar offices to ask how they accomplish the same task. Some of these offices say “we use a database to help with these tasks.” They’re willing to send it (it’s all a big orgy of NGO goodness out there, and our client is pretty much at the top of the pile), but given the bureaucratic nature of things, if it includes any data the request will have to ascend the food chain. They’re willing to just send the structure right away.
Given that they’re already helping us out by taking the time to speak with us, and the fact that they’re not our clients, I’m hesitant to ask them to do much more than something like “select Tools>Options>Duplicate” or some such. Asking them to run a custom query would probably scare them (though I recognize this is just an assumption).
Rhythm
What you want to do is create a new, blank database. Then import all the objects from the database you want to copy without data. When selecting the tables to import, click the “Options” button, and select “Definition Only” instead of the default “Definition and Data”. Just make sure you also import all the other non-table objects as well.
Thanks mrklutz!
That looks just about perfect – I tried it on a database here, and it looks like all tables, queries, forms, and reports came over. I also checked the relationship window, and that looks intact as well. Unfortunately, some small things didn’t come over, such as which form opens on startup.
Know of anything else I should check to see if it missed?
Again, thanks!
No problemo. Aside from the startup options, the only thing that won’t come over are the object libraries registered in any VBA code you’ve written, if you used anything apart from the standard libraries.