REMOVE VB PASSWORD FROM ACCESS 2000 DB

[nicmic]

I HAVE A PROBLEM ! I HAVE CREATED A DB IN ACCESS 2000. I HAVE PROTECTED THE VB CODE OF THE FORMS FROM VISUAL BASIC EDITOR IN ACCESS 2000. I NEED TO KNOW HOW CAN I DISABLE AND REENABLE THE PROTECTION. I WOULD LIKE TO DO IT FROM A LITTLE VB PROGRAM THAT I MUST CREATE. I NEED TO DISABLE THE PROTECTION BECAUSE I OFTEN UPDATE THE DB BUT CANNOT IMPORT FORMS FROM A DB TO ANOTHER WITHOUT DISABLING THE VB PROTECTION. I KNOW I CAN DO IT MANUALLY WITH ACCESS 2000 BUT I SHOULD GO TO MY CUSTOMERS AND DO IT FOR ALL OF THEM AND FOR ALL UPDATES. I HAVE TO SEND VIA EMAIL A PROGRAM THAT CAN UPDATE FORMS AND REPORTS THAT ARE PROTECTED IN ANOTHER DB. PLEASE HELP ME!!!

 

[Francescina]

AFAIK this can not be done programmatically. In a correct FE/BE architecture you simply replace the FE file (which is always in MDE format).
If you find another solution, please let us know.
Good luck!

 

[CVigil]

Elaborating on Francescina's post...

FE/BE = Front-end/Back-end

The front end and back end are used together to form a complete application. Users typically have direct access only to the front end. The front end is the user interface. The back end is typically hidden from the users; they don't need to know it exists. In this way, with each "end" of the application isolated from the other, changes can be made to each without affecting the other.

This is a general concept which applies to more than just databases.

Specifically dealing with the current context (MS-Access databases):

Front End:
A database containing: 1) all of the forms, queries, reports, macros, modules, etc., that are used to manipulate the data in the tables; and 2) *links* to the tables in the back end.

Back End:
A database containing all of the tables to be manipulated via the front end.

Use of the FE/BE design with databases is particularly useful because it isolates the data from the interface used to change the data. The front end uses *links* to the tables in the back-end.

So when I make changes to a copy of the front-end, and put the modified copy in place of the (other most recent) original, the *links* stay the same, pointing to the actual tables in the back end. (I work in a copy when implementing and testing changes, so that I don't interfere with other people's work while I am making those changes.)

If, instead, I had the tables themselves *with* the interface stuff (in the same database), then when I put a modified copy in place of the (other most recent) original, I would also be replacing the tables and data contained in them.

For instance: 1) I make (or already have) a copy of a database in which I can develop changes for the users, while they go on working in the original. 2) Some time after that copy is made, Mary adds sixteen orders to the database. 3) I finish my changes, and put my copy of the database in place of the old one so that users can take advantage of the nifty features I've added for them. 4) Mary gets back into the database and finds that her sixteen new orders are gone, because I've replaced the whole database including the tables into which she entered information while I was developing. 5) More problems ensue.

With the FE/BE design and the previous situation: 1) I make (or already have) a copy of a database (front end) in which I can develop changes for the users, while they go on working in the original (front end). But in this case, both the original and copy have *links* to the tables in another (back end) database; what gets copied isn't the data, but pointers to the place where the data is stored. 2) Mary adds her orders to the database while I work. 3) When I finish developing, in place of the original I put my copy, with nifty new features and *links* to the tables containing Mary's added data (and the rest, of course). 4) There is no 4) really; Mary just goes on working because her data still exists and she isn't having problems. 5) I ask her if she likes the nifty new features and find out if she has even noticed them .

Francescina rightly suggests using MDE (Microsoft Database Executable) files as the front-end, because MDE files don't allow users to change the design of forms, queries, reports, VB code modules, macros and other good stuff. For more info on that, read in MS-Access' own help files; search for "MDE" (without quotes). Note that MDE files still require MS-Access to use them; they are "Executable" only in the sense that the aforementioned design elements are no longer accessible, having been compiled and removed.

When you make an MDE, KEEP YOUR ORIGINAL MDB. This is your development copy, because you CAN NOT develop in an MDE, and you can't get an MDB from an MDE. You will always make changes to your MDB, and as you wish to make them available to your users ("roll out the changes&quot create a new MDE from the MDB each time, replacing the old MDE. (I even keep separate folders for my DATASTORAGE, DEVELOPMENT, and MDE databases. This keeps them neatly separated and organised.)

Using these techniques, you can even have multiple front ends using the same back ends, and vice-versa! The key point is using links to the appropriate tables, so that changes in your front ends are isolated from changes to the data.

End of lecture ...
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[Tofias1]

CVigil,

Can this also be done by spliting the database?

Tofias1

 

[CVigil]

Thank you for the star! It was a nice warm fuzzy, and a better starting point for the day than walking over a sprinkler that turned on beneath me this morning ;-) .

- - - -

(By the way, "splitting the database" is different from "splitting a table". End precautionary clarification of terms.)


I believe that "splitting the database" is the phrase used by MS-Access for the process of converting a single database (data + objects) into a front end database (objects) and a back end database (data). Hang on ... okay, yes, I've confirmed it. Only takes a moment . ... In other words, splitting the database *is* setting up a front-end/back-end architecture in your database.

In my copy of Access 97, an add-in was provided to automate the process. (While it is a simple one, if there are a lot of table, I can see that it could get tedious.) Click through these menus and menu items: Tools|Add-Ins|Database Splitter.

The Database Splitter is probably available in later copies of Access, too. In any copy of Access, it may or may not have been installed when Access was -- so if it isn't there, check the Add-In Manager (also available under Tools|Add-Ins in Access 97) and the installation disk.

This paragraph should be preceded and followed by the phrase, "...but you'll have to check it out for yourself". I haven't used the Database Splitter, but have no reason to think that it should be difficult or complex to use. My guess is that it would probably only need to ask you for path and filenames for the two new databases. Really, all the Database Splitter ought to be doing is moving the tables to a new database and creating links to that new database in the database that will end up containing the non-table objects.

Make a separate copy of your database before you use the Database Splitter, though -- a) in case anything goes wrong, and b) in case the splitter actually changes your original database. That way, you'll have something to go back to if anything does go wrong. And if you're interested, you can even compare the results to the original.

Even if you don't have the Database Splitter add-in, you can do the same thing to an existing database manually. Copy the tables from your database to a different database -- BEING SURE TO RECREATE RELATIONSHIPS AS YOU ADD TABLES. Relationships are a very important part of most Access databases, *worth* shouting about to the back of the class! To do the copying you can Export, or drag-and-drop, whatever. Once you have them in place, and have all of the relationships recreated, then you can delete the tables from your original. (You *do* have a back-up copy, right?) Then in your original, add a new table; you should be given a list of choices. Choose to add a new link or linked table, however the choice is phrased. You'll first select the database file into which you moved the tables; then you select the individual table(s) you wish to link. In my copy of Access 97, I can choose multiple tables in the same step. You're done.

If there are relationships defined between tables in the source database (your back end), *at the time of linking*, Access will automatically reproduce those relationships in any database that links those tables. (And yes, even if you add the tables separately, or at different times.) If relationships are created in the source database *after* the tables participating in the relationships have been linked in another database, that other database does *not* pick up the relationships. When Access links a table, it checks *at the time of linking* to see if the source table is participating in any relationships with any of the tables in the source database already linked; and it recreates any relationships it finds. You are able to add new relationships yourself in your front end database without affecting the back end database's relationships. Likewise, you can delete relationships in the front end without affecting the back end.

If you want to pick up relationships from your back end database some time after having linked the tables already, at the expense of any relationships already defined in your front end, just delete the relevant links and recreate them. The Linked Table Manager add-in can come in handy here, and is very easy to use. Note the "prompt me regarding selected tables" checkbox (caption may be different, but that's the idea). It forces Access to ask you for a database and table, and recreates the link, even if Access finds that the original link is still valid.

Miscellaneous notes: *In Access 97, anyway* (I just don't know about other versions)... You can not link to a linked table. That is, a link must point to a physical table, not to another link -- you can't build a chain of links. For this reason, if you want a relationship to be shared between two given tables, and you want that relationship to exist in any database referencing both tables, you should have both physical tables in the same database, with the relationship defined. Other databases linking to both tables would then automatically inherit the relationship as described above. (Whether you then delete the relationship in any of the linking databases is a separate question.) If the physical tables were in separate databases, a relationship could not be defined between them. They both could still be linked in some other database, in which you could create the relationship. But since links can't point to links, that relationship couldn't be inherited by other databases with the same two tables; you'd have to recreate the relationship every time.

The auto-inheriting of relationships is especially handy when you have multiple front ends using the same back end for data storage. That scenario, in fact, is what I'm advocating when I suggest putting multiple tables together if they have a consistent relationship. In this instance, considering the front ends that use the data aren't as important as considering which portions of the data will be used together consistently.

For instance, imagine having a Parts table and a Customers table, where each Part is made for a specific Customer. If you manufacture and sell Parts, you might have separate front ends for your Manufacturing and Sales departments, but both would still reference some of the same data. Manufacturing might run reports about the number of pieces of each Part it produced for each Customer, along with other Manufacturing-specific data. Sales might run reports about the number of pieces of each Part sold to each Customer, along with other sales-specific data. In both cases, information from the Parts table and from the Customers table is desired -- say, at least the Part ID and the Customer Name, for grouping purposes -- and in both cases, the relationship between Parts and Customers is the same -- one Customer has many Parts, but each one Part has only one Customer. So if we wanted separate front ends for the Manufacturing and Sales departments, we would certainly want the Parts and Customers physical tables in the same back end database, with the relationship defined there, so that the relationship could be inherited by both front ends when we first create them. (Yes, you could have forms, reports, etc., for both departments in one database, of course. And there are situations where that sort of thing would be appropriate. There are also, however, situations where it is definitely advantageous to have separate front ends -- and these are cases where you definitely want a separate back end database, so that everybody is still working on the same data set! For instance, in my example, you might want to make sure neither department could mess with the other department's department-specific data, without having to implement special security or permissions; they each have only their own front end in which they have as free a rein as you give them. Some companies or organisations might not want different departments to be able to view information from other departments, even while they share some info! (Can anyone say "Government"?) And if you have to make a change to a Sales form, you don't have to kick Manufacturing out of their (separate) front end to replace Sales' front end. So those are some other advantages to this whole Front-End/Back-End thing .)
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot