Extracting just a few Access Objects
If you want to send someone just a few forms, reports or queries instead of your entire Microsoft® Access database, you have two choices: Copy the database and delete everything you do not want, or create a new, blank database and import just the desired objects. If you have a large number of objects, the second approach takes less time.
Remove VBA Lock
Either way, if any of the objects have VBA code attached, you will need to remove – temporarily – any password lock on your VBA code. To do this:
- Use the shortcut keys Alt+F11 to open the VBA editor. Alternatively, from the Access menu, click: Tools-> Macro-> Visual Basic Editor.
- From the Visual Basic Editor menu, click: Tools-> YourProject Properties, where “YourProject” will be the actual name given to the VBA project for your database.
- Enter the VBA project password and click OK.
- Click the Protection tab and uncheck the box labeled, “Lock project for viewing.”
- You can leave the existing password in the boxes at the bottom, or clear it out or change it. Depending on your version of Access, you may need to enter the password again to reach this dialog box even if the VBA code is no longer locked, so be sure not to lose the password unless you clear it out of the boxes.
- Click OK.
Find the Objects to Extract by Date
To make sure that you do not overlook any objects you need to export, check the modification dates. When you first display the database window, Access may only show you the object names without the associated details. Click on the “Details” icon, here shown by the mouse pointer in the picture below.
Now that you can see the dates (which may show better if you maximize your window), click the “Modified” column header to sort by these dates. Click it a second time to sort them in descending order, so that the ones changed most recently appear at the top. If you have copied the database and want to send only the newer ones, now it is easy to delete the objects below a certain date.
If you will be importing into a new, blank database, you will want to see these dates while in the other database. Here’s a tip: Keep this database open while you start a brand new Access session by running Access from the Programs option of your Windows Start menu (or from whatever shortcut to Access you normally use). Alternatively, once your new database has been saved, you can create a second Access session by double-clicking the file name within Windows Explorer without closing the first database. You can easily switch between the two using the shortcut key combination Alt+Tab.
Import Objects into an Empty Database
Create the new database in the usual way: From within Access, click File-> New. In the Task Pane, click on “Blank Database.” Import the objects you want:
- From the Access menu: File-> Get External Data-> Import.
- Navigate to the existing database and click the Import button.
- The "Import Objects" dialog box has a separate tab for each type of object.
You can import multiple types of objects at once by clicking on the desired tabs and clicking each object to select it.
(Clicking it a second time deselects it.) Note that there are buttons to "Select All" and "Deselect All" the objects for the tab currently showing.
You can also select a block of objects by selecting the first one and holding down the shift key while you click on the last one.
- When you have finished selecting all the desired objects on all the tabs, click OK.
Remember VBA Security
If you needed to remove the VBA lock on the original database, return to that database and follow the steps under "Remove VBA Lock," above, to put it back. When you reach the Protection tab, click the "Lock project for viewing" box to put the check mark in again.
Before you Send the File
Run the Access "Compact and Repair" utility before you send it on. (Tools-> Database Utilities-> Compact and Repair Database) This will make the file smaller and may fix any integrity problem as well. If you are emailing the file, also use a zip utility. This will not only reduce the file size further, thus shortening the time it takes to send and receive the email, but also will result in the file having a ".zip" file extension.
Files with the normal ".mdb" Access file extension are blocked by default by Outlook and Outlook Express because it is possible to include malicious code that would run immediately upon opening the file. Of course, you won't be including any nasty routines, but to avoid sending a file that the recipient cannot open (except by modifying the Windows registry), don't send it with the ".mdb" extension in the name. If either you or the recipient lacks a zip utility, you may alternatively simply change the file extension manually to something not on the prohibited extensions list. (Simply using a number - for example, renaming "MyDB.mdb" to "MyDB.1" - usually works.) Inform the recipient that the file extension will need to be changed back to ".mdb" in order to let Windows know that it should use the Access program to open it.
How to Show File Extensions
The default Windows setting is to hide extensions for all file types that Windows knows about, and you will not be able to change an extension unless you can see it. I have known users to think they had changed the extension successfully because they added a period followed by one to four characters, when in fact Windows continued to use the old extension without showing it. For example, when extensions are not showing, if you rename "MyDB" to "MyDB.1," Windows actually is renaming "MyDB.mdb" to "MyDB.1.mdb," and it is the final, hidden letters after the last period that are the true extension.
To show all extensions:
- Open Windows Explorer for any folder.
- From the menu, click: Tools-> Folder Options.
- Click the View tab.
- Uncheck the box, "Hide extensions for known file types." (While you are at it, you may also want to click on the line above, "Show hidden files and folders," which will cause such files and folders to be shown but in a dulled-out fashion so that you can still tell which ones would otherwise be hidden.)
Once the extensions show, you will need to be careful not to inadvertently delete an extension when you rename a file for other reasons, because Windows uses the extension to match the file with the correct program for opening it. However, Windows will issue a warning message any time you are about to change a file extension, so you are not likely to get into too much trouble.
Importing the Objects into Another Database
Objects are imported into an existing database the same way they are imported into a new one, but with several additional issues. First, if an object has the same name as an existing object, Access will rename the one being imported by appending the number "1" to the end of the name. If that name already exists, Access will increment the number until it creates a name that has not already been used. If the object is a form, report or query, simply rename or delete the original, and if you already imported the new object before doing that, rename the new one to the original name. Tables and modules are a little more complicated, as you will see, although Access will import and rename them in the same way.
The second issue is not as immediately obvious but will create bugs in your system where none existed before until it is fixed. The problem is that two VBA procedures ("subroutines," "functions," "routines" or whatever you want to call them) cannot have the same name in independent VBA modules. It is fine to have procedures named the same in different forms or reports, because those are normally limited to only those individual forms or reports. Public procedures in independent modules, on the other hand, can be called by any form, report, query or other procedure, and the module name itself is not used when the call is made. When two module procedures have the same name, Access does not know which one to invoke and will instead issue an error message. Depending upon what is calling the procedure, the error message may be misleading as to the real cause of the problem, so fix this when importing a module rather than wasting time debugging it later. Renaming one of the modules is not good enough; you need to decide which module should not be used and delete it, or else delete or comment out inside the modules the individual procedures with duplicate names.
The final issue concerns tables and what happens to their relationships and to queries based upon them when you rename the tables. In Access 2003 (and earlier versions, to varying extents), when you rename a table, the join relationships between it and other tables are maintained. Also, the queries based upon it will point to the renamed table. Tables that are imported together have their table joins imported along with them, but joins are never created automatically between a newly imported table and an existing table. What this means in practice is that, in order to replace a table with one you import, you will need to redo all the joins that relate to it. If you import any related queries along with the table, the queries will still be based on the imported table, but to use an existing query on an imported table, you will need to fix the query in design view to use the new table instead of the old one.
For example, suppose you have a "Contacts" table joined to an "Orders" table on the field "ContactID." You want to use a newly designed version of the "Orders" table that your coworker just sent you instead, so you rename your existing "Orders" table to "OrdersOLD" and import the new "Orders" table. If you go into the Access Relationships window, you will discover that "Contacts" is now joined to "OrdersOLD" and the new "Orders" table is not joined to anything. Worse, every query that used the original "Orders" table was changed to use "OrdersOLD" as soon as you renamed the table. (By the way, if you had simply deleted the old "Orders" table and never renamed it, once you imported the new "Orders" table, the queries would automatically point to it, but the joins at the table relationship level would still be gone.)
Now you may begin to understand why it can cost as much to redesign tables as it did to design them in the first place, since a table redesign usually means extra work on each query, form and report that uses it as well, not to mention the work that goes into creating special queries to move data from old to new tables, plus all the extra testing, once a system is already in production.
One more thing about tables: If you are importing a table simply for the data and not for the design, it is better to keep the old table under its original name and then create an append query to add the new data to the old table, rather than renaming the new table to the old one. This way, existing joins and queries remain intact.
Security
The Access VBA password was already discussed above. If you are receiving error messages related to not having proper permission, you are either not joined to the proper Access workgroup security file or you did not log in under a user name that has proper authorization for what you are trying to do. You will find more information about security in my previous article, "Microsoft Access Security for End Users."
Conclusion
Due to size, confidentiality of data, or the time savings in copying rather than recreating objects from scratch, there is often a need to extract a subset of an Access database into a new database. Hopefully, this article has provided the information you need to do that. If you have any questions or comments, please contact me.