Thankyou Plus!

[mimottershaw]

Dear all,

I have been posting quite a few problems over the last few days as I've been working on a project for work, and I just wanted to say thank you to everyone for their assistance and patience! You will be delighted to know that the project now seems complete, and all I have to do is install it at work. Fingers crossed!

In the past the only databases I have designed have been for my own use and maintenance, so this has been something new for me.

I read with interest the "Access Ten Commandments," particularly with reference to preventing users (with even less knowledge than me) from being able to access certain features that may make the database and therefore integrity of the data vulnerable.

I would be really grateful for any general advice about how to go about (in detail if possible) protecting this database to prevent the design being altered or the data inadvertantly deleted??

Additionally could somebody please educate me about how a database will work over a network, particularly if two peole access it at once? For information I am installing the database on a shared network server, to be accessed by several users who have access to that "Drive." Can two people access it at once, and what happens to data integrity if two people have it open at once, or try to update it at the same time>

I believe the version of Access at workk is Access 2000, if that makes any difference.

As always, thank in advance,

Regards,

Mark.

 

[Scooterbug]

You want to make sure that the database is split up into a front end (Has the forms, queries, reports) and a back end (Stores the tables). You link the tables from the back end to the front end. You also want to make sure that EACH user has their OWN copy of the front end on their local drive. This will prevent corruption and not bring down everybody if the front end experiences issues.

 

[Mr. B]

Mark,

By all means look in to splitting your database into a Front-end and a Backend. This is the method that is reccomended by Microsoft for deploying a database, especially one that will be being used by multiple users.

Depending on the version of Access you are using, there is an option on the menu that will split the database for you. You would then put the Backend file (which only has the tables in it) on a shared network drive. You provide each user with a copy of the Front-end. This file can be located in any folder on their hard drive. You should never try to allow multiple users to open the same file. If necessary you can use the "Linked Table Manager" to relink to the tables in the backend file.

 

[mimottershaw]

Hi Scooterbug,

Thanks for the advice. If I were to do this would it simply mean copying Forms into a fresh database and then re-linking them. I have a sneaky suspicion this would mean a lot of re-working? Also our computers at work "do not" have a local gard drive. Everything is run from server drives, with the local computer just being a shell without local hard drive access.

I was really hoping there was a simpler "don't let the user muck the design up" setting? I saw some things in the current database options about restricting views but wasn't 100% sure what they did?

Thanks again,

Mark.

 

[Scooterbug]

Thanks for the advice. If I were to do this would it simply mean copying Forms into a fresh database and then re-linking them. I have a sneaky suspicion this would mean a lot of re-working?

*Points to Mr.Bs post* You wont have to rework code or anything. The linked tables will act as local tables as far as forms, queries and such are concerned.

Also our computers at work "do not" have a local gard drive. Everything is run from server drives, with the local computer just being a shell without local hard drive access.

Well, the users have to have some place to store profiles and settings (A home folder). I would have a copy of the the front end put into that home folder. The important thing is to not have multiple people in the same copy of the front end.

I was really hoping there was a simpler "don't let the user muck the design up" setting? I saw some things in the current database options about restricting views but wasn't 100% sure what they did?

You can turn off the Database Window, disable special keys and a few other things. Under Tools -->Start Up are the options for that. There are other more secure ways as well (Workgroups and such). It all depends on the level of non-invasiveness you want in the database.

 

[mimottershaw]

Thanks again for the advice. Have found some help screens what you advised and should be able to take it from there.

Cheers,

Mark.

 

[mimottershaw]

Hi again,

I've followed the steps carefully to split my database, make a back end and front end, and then converted my front end to an ACCDE file.

I still seem able to see, open and edit tables, macros and queries.

Also I have a report module called IsLoaded in my original database, and although I can see it on the Front end, and have copied and pasted it into the Back End, reports now crash with the error message "The object doesn't contain the automation object 'IsLoaded'"

I should confess that this was just a "practice" on two locations on my hard drive. Don't know if that matters?

Thanks

Mark.

 

[gemma-the-husky]

splitting the database merely gives you a safer environment that will reduce the likelihood of data corruption


the next step is to prevent the users being able to access any of the design features of the database

now this involves some tricky stuff that will include

probably distributing a accde/mde rather than a accdb/mdb (ie a compiled database that removes all code and form/report design facilities from users)
limiting users ability to see the dbs window (eg in A2007 preventing F11 doing anything)
stopping users being able to bypass this protection
making your app sufficilently robust that any run time error will be handled within the app

etc etc

 

[mimottershaw]

Thanks Dave,

Could you point me in the right direction or help with some instructions for this please?

also do you have any idea about why the IsLoaded module now causes an error?

Thanks,

Mark.

 

[boblarson]

Thanks Dave,

Could you point me in the right direction or help with some instructions for this please?

also do you have any idea about why the IsLoaded module now causes an error?

Thanks,

Mark.

Well, for one here's info on splitting.

As far as the IsLoaded module (which shouild ONLY be in the FRONT END and not the backend), not sure exactly why but I hope you split correctly (Backend gets tables and Frontend gets everything else).

 

[mimottershaw]

Hi Bob,

Access 2007 doesn't give you many options for splitting; it just does it! However when the split was complete all Tables were in the Back End as Hoped. Everything else, including links to these tables and Module 1 (with IsLoaded function), was in the Front End.

The IsLoaded Module (reproduced below) is pre-written code (I assume by Microsoft) for use when using a form to gather criteria for a report (and checks if a form is loaded or not) (I don't mean to teach you to suck eggs, just providing as much info as I can):

"Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.

Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)

If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If"

The error message I get is: "The object doesn't contain the automation object 'IsLoaded'"

This happens on all of my reports where form data entry is required, and obviously makes the database completely unusable in ACCDE format.

Have you (or anybody else!) any ideas!?!?

Thanks

Mark.

 

[boblarson]

Actually IsLoaded is NOT an Access function. That is a function someone wrote. You do not have to use that (since Access 2000 it has a built-in one).

You just use

If CurrentProject.AllForms("YourFormNameHereInQuotes").IsLoaded Then...

But that appears to use that as well to make sure that you don't have a form open in design view (which can't happen in an MDE/ACCDE file anyway, so the check is pointless in that).

So, sometimes it is a pain trying to track down a problem in an ACCDE or MDE file. You need to rely on your error handling to give you good messages to find where this is happening. It seems to me that it may be having a problem with these two lines:

Set oAccessObject = CurrentProject.AllForms(strFormName)
And
If oAccessObject.IsLoaded Then

It would appear that you might be able to get around this if you changed your code to simply this:

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.

If CurrentProject.AllForms(strFormName).IsLoaded Then
   If CurrentProject.AllForms(strFormName).CurrentView <> acCurViewDesign Then
       IsLoaded = True
   End If
End If

And just bypass trying to throw another object in the mix.

 

[mimottershaw]

Hi Again Bob,

The modified code you suggested still works fine in the ACCDB file, but unfortunately gives me exactly the same error in the ACCDE format ?????

I am well out of my depth now. Anymore ideas gratefully received????

Regards,

Mark.

 

[boblarson]

You're running the ACCDE file from the same folder as the ACCDB file?

 

[mimottershaw]

Bob,

Your comment above........

"But that appears to use that as well to make sure that you don't have a form open in design view (which can't happen in an MDE/ACCDE file anyway, so the check is pointless in that)."

...... set me thinking, and what do you know? As I run all my forms in print preview view (and never have any other form open), the Module and IsLoaded function are both completely redundant and the database works just fine without them, including in the ACCDE. I've been using that code for years thinking it is essential!!! You've got to laugh right?

For info I was running the ACCDB and ACCDE files from my desktop, with the back end in a separate folder. I mentioned this (sort of) at the top of the thread. Would it make a difference??

Any way all's well that end's well (so far!).

Thanks again,

Mark.

 

[boblarson]

I was only looking to see if perhaps the file had been moved and was no longer in a trusted location. But it seems you have it working at least to what you need. So, sometimes you just gotta take that as a victory even though there is something going on which just seems odd.