How many times have you heard, "You can't do that in Access!"? Well, there are often work-arounds for even the most stubborn "rules" that Access developers are supposed to abide by. Check back here often as we add more work-arounds and tricks to help you overcome those "rules"!
How to open a database file as "read-only" when it has already been opened exclusively.
Q: I bought an application that comes with a Microsoft Access MDB database file. Every time the application runs, it opens the Access database exclusively. Is there any way I can open the MDB file for "read-only" to peek at it when this application is accessing the database file?
A: Yes. You can use the "periscope method" (use double mirrors to look around a corner when the direct line of sight to the target is not available) -- as long as the application is not making design changes in this MDB file. This is unlikely, but check to make sure that this is not the case by looking at the dates of all objects in the database window. If objects are modified or created while your application is running, then you'll see modification dates on these objects more recent than the date you installed your application on your computer when you display the "Details" view of objects listed in the Access database window.
1. First, make sure that your application is closed and no one else and no other process is using the database file.
2. Rename the MDB file, but remember (or write down) the original name. (If the original name is MyDB.mdb, then change it to something like MyDB_orig.mdb. You won't be able to rename the MDB file if someone else or or some other process is using the database file, so this ensures that you don't copy the file while the database is in an inconsistent state, which could lead to database corruption.)
3. Make a copy (for backup) of this file and place the copy in another folder for safekeeping. (You always want to make a backup copy of your database whenever you are about to make major changes.)
4. Rename the MDB file back to its original name so that your application will be able to find it.
5. Open this MDB file in Microsoft Access.
6. Using the menus, select Tools --> Database Utilities --> Database Splitter.
7. Select the "Split Database" button.
8. Browse to the directory where you would like to save the "back end" of the database (the tables).
9. Access automatically names the back end database file with "_be" but you may change this name. Select the "Split" button to split the database into two files. When finished, Access will display the message "Database successfully split."
10. Select the "Tables" tab in the database window. Notice that all tables now have the "link" symbol next to each table icon.
11. Close this database file. This is now your front end database file.
12. Open the back end database file that you just created.
13. Using the menus, select File --> Get External Data --> Import.
14. Browse to the directory where your front end database file is located. Select its file name, and then select the "Import" button.
15. In the "Import Objects" dialog window, select the "Select All" button for every tab except the "Tables" tab until every object, except the tables, in the front end has been highlighted.
16. Select the "Options" button. The dialog window will expand to display the options that you want to import into this new database.
17. Select the check boxes next to "Menus and Toolbars" and "Import/Export Specs."
18. Select the "OK" button to import all of these objects. (Don't worry if you receive the message, "The source database has no import/export specifications to copy." This isn't an error, just an alert, since you selected the option to import the specifications.)
19. A word of warning: queries don't always import "verbatim." If any of your queries were moderately complex and had correlated subqueries or table aliases, then Access may have converted parentheses into brackets during the import, and these imported queries will not open and run without encountering an error. If this is the case, then you need to copy the SQL statement from the original query in the front end database file (use the SQL pane view, not the Query Design view) and paste it over the converted SQL statement in the back end database file, then save the query.
20. Close the back end database file.
21. Open Windows Explorer and browse to the directory which contains your back end database file.
22. Create a shortcut for this file by right-clicking to display the popup menu and select "Create shortcut."
23. Open the shortcut "Properties" dialog window.
24. You will notice the complete path and file name of your back end database file in the shortcut's "Target." Change this target to use the complete path and file name for the Access executable (enclose this entire string in a pair of double quotes if your operating system is Windows NT, 2000, or XP), followed by a space, followed by the path and file name of your back end database file, followed by a space, followed by /RO.
Example:
"C:\Program Files\Microsoft\OFFICE11\MSAccess.exe" C:\DB\MyDB_be.mdb /RO
The reason you must place the entire path of the Access executable file and not just the name of the executable (MSAccess.exe) before the path and file name of your back end database file is that the operating system will resolve the application associated with your MDB file based upon the file name extension listed in the Registry. This "default" application (MSAccess.exe) will then be launched with the default action ("Open") for the database file that you named, but none of the command line arguments will be evaluated.
25. Change the "Start In" directory in the shortcut to match the path for the Access executable. (You may need to enclose this string in double quotes also, depending upon your operating system.)
26. Save the shortcut.
27. Copy (or move) the shortcut file to your desktop (or other convenient location that you have easy access to whenever you run your application).
28. Run your application.
29. Use the shortcut to open the back end database file. You will receive the message that the database is opened "read-only," and you won't be able to save changes, but that's what you wanted to do anyway.
30. You may now browse around the database at your leisure while your application has your front end opened exclusively. Bear in mind that any form or query that you open in the back end database will be a "snapshot" in time of the Record Source of that particular object and won't reflect changes to the data that your application is making until you requery the data (close, then reopen the form or query, or set up a form timer to requery each form in the Forms collection every few seconds to get periodic "snapshots" of the data while your application is running).
Top
How to retrieve the Product Key for Microsoft Windows or Microsoft Office.
Q: I want to be able to reload the software on my computer in case the hard drive ever crashes. I still have the original CD's, but I've lost the Product Keys for Windows and Microsoft Office. Are these Product Keys saved in my Registry so that I can copy them down and keep them in a safe place?
A: Yes. The Product Keys are saved in the Windows Registry, but you'll have a hard time reading them. However, there's a freeware tool available that can read the Product Keys for software that is already installed on your computer. It supports Windows 95, 98, ME, NT 4.0, 2000, and XP and Server 2003. It also supports Microsoft Office 97 and XP. (It doesn't support Microsoft Office 2000 or 2003, because these Product Keys are stored differently than the other products.) Download the Magical Jelly Bean Key Finder from this Web page:
http://www.magicaljellybean.com/keyfinder.shtml
Top
What to do when there's no backup of the database.
Didn't make a backup of your database file? Here's a tune to make you feel better (sung to the rythm of the Beatles' Yesterday):
Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh, I believe in yesterday.
Suddenly,
There's not half the files there used to be,
And there's a panic coming over me.
The system crashed so suddenly.
I pushed something wrong;
What it was I could not say.
Now all my data's gone
and I long for yesterday-ay-ay-ay
Yesterday,
The need for backups seemed so far away.
I knew my data was all here to stay.
Now, I believe in yesterday.
Author: unknown
Copyright © 2004 - 2007 Q-Built Solutions. All rights reserved.
Top
|
