here is my question: the organization i work for uses this old, old VB application with an Access 97 (yes!, you heard it right, Access 97 in the year 2003. don't ask me why) database and it crashes all the time. i was hired to work on a new application to replace this one, but in the meantime they need to keep the old one going. so they brought in this consultant guy who told them that in order to fix/patch the database they need to do 3 things:
1. get rid of unused objects, i.e. lines of code, in the VB app.
2. decrease the size of the database by eliminating records older than 4 years.
3. convert the MDB file to MDE.
now, the first two i understand, but i don't know anything about MDE. so, anyone, what, if any, are the advantages of using MDE over MDB?
any suggestions would be appreciated.
"MDE files are Access databases containing modules that have been saves without the VBA source code. Instead, only the complied VBA pseudocode is stored. This makes for a much smaller database than normal a normal MDB file, and one that is inherently much more secure. Since the source code is not included, ther is no danger of an unscrupulous individual breaking in and stealing it"
Also, have a look in the help under MDE for further info'
If your database contains Visual Basic code, saving your database as an MDE file compiles all modules, removes all editable source code, and compacts the destination database. Your Visual Basic code will continue to run, but it cannot be viewed or edited, and the size of your database will be reduced due to the removal of the code. Additionally, memory usage is optimized, which will improve performance.
Saving your database as an MDE file prevents the following actions:
· Viewing, modifying, or creating forms, reports, or modules in Design view.
· Adding, deleting, or changing references to object libraries or databases.
· Changing code using the properties or methods of the Microsoft Access or VBA Object models of an MDE file contains no source code.
· Changing your database's VBA project name using the Options dialog box.
· Importing or exporting forms, reports, or modules. However, tables, queries, and macros can be imported from or exported to non-MDE databases. Any tables, queries, or macros in an MDE database can be imported into another database, but no forms, reports, or modules can be imported into another database.
Caution Be sure to save a copy of your original database. If you need to modify the design of forms, reports, or modules in a database saved as an MDE file, you must open the original database, modify it, and then save it as an MDE file again. Saving a database containing tables as an MDE file creates complications reconciling different versions of the data if you need to modify the design of the forms, reports, or modules later. For this reason, saving a database as an MDE file is most appropriate for the front-end database of a front-end/back-end application. Also, you won't be able to open, convert, or run code in a Microsoft Access 97 MDE file in future versions of Microsoft Access. The only way to convert a Microsoft Access 97 MDE file to a future version will be to open the original database the MDE file was created from, convert it, and then save the converted database as an MDE file.
Important Some restrictions may prevent you from saving your database as an MDE file:
· If your database is secured with user-level security, you must meet certain criteria.
· If your database is replicated, you must first remove replication system tables and properties.
· If your database references another database or add-in, you must save all databases or add-ins in the chain of references as MDE files.
MDE files are version specific, so there is no guarantee that they will work with future versions of Access
Changing to an MDE file al;ter the DB dramatically, so be sure to keep a copy of the original.