Multi-User Lockout When Running Reports

[StacyStacy]

Hi.

I need some assistance in solving an issue whereby I have a total of 3 people sharing 1 database simontaneously. The problem occurs when 1 user begins to create reports via the wizard tool and/or runs reports. While doing this, the other 2 users are unable to use the database. The database is configured under the "options" menu, "advanced" tab, "default open mode" and set to "shared". Also, all users are also configured under the Add/Remove Programs by informing Access to run the application from user's pc and not the network.

Please advise.

Thanks,

 

[StacyStacy]

After reading your response, I may be pulling straws, but what would happen if I were to use an ODBC connection?

I understand that the one user will need to create and run reports possibly on a daily basis, while the other 2 users work in the database, possibly keying information.

Here's a thought: Should I set up two different databases. For
the 2 users that key in information, I would configure them to share the database. For the 3rd user, I would create an ODBC connection in an entirely different database. That way, she can pull the data and create her reports without affect the other 2 users. Your thoughts?

Thanks.

 

[Autoeng]

Hi Stacy. Having an ODBC backend (where your data is stored) won't help your situation as the reports are stored in the front end of the db. You should split your db and let each user have a copy of the front end. That way they can change what they want. There have been a lot of posts concerning splitting a db and distributing the front end to users so if you search for those you'll get the info you need.

 

[Fizzio]

There may be a work around to this - but it would require some nifty programming.

In theory, you could have a central copy of your frontend on the server which you schedule to run when all have gone home.

At the shutdown of everyones own frontend, the local copy creates a copy of itself (maybe from another database) to a separate part of the server (each user could have their own folder for example)

The central copy then interrogates each frontend and imports the newly created objects into itself (hoping that there are not 2 of the same name!)

The next day, the local pc's check the server for a new version of the central Db and download it.

I'm sure this could be done but would it really be worth the effort?

 

[StacyStacy]

I still need help ...

Ok. I found out more to this issue: It's now 4 people needing access to the database. 2 are keying in data only into the "dislocated workers" database. The other 2 are running reports. Some of the reports are standard reports that have already been created. Otherwise, they have to create reports based on specialized requests. One user asked me if the 2 users can only have access to the dislocated workers database whereas the other 2 had full rights.

I did read though some of the other topics, but also am a little confused if whether or not I can create an ODBC connection on the backend and create a different front end.

Please help ...

 

[Autoeng]

Hi Stacy,

You don't need an ODBC connection at all. You need to split your db into the front and back ends. Now take your front end and make a copy. Take one of the front ends and secure it so that the data entry people use it. Take the other front end and do nothing so that the people who make reports use it.

Now when you need to make a change to the front end make a copy of the unsecured FE so that you get all of the reports that the users have created. Make your changes. Make a copy of the new front end. Strip out all the reports and stuff you don't want your data entry people to have and secure it.

A pain when you need to make changes but I don't really see any other way around it other that some "nifty" programming as Fizzio suggested.

 

[StacyStacy]

Thanks.

I have encountered an error msg that reads, "The Visual Basic for Applications Project in the Database is corrupt". I have tried to compact and repair without success. What do I do??

 

[Autoeng]

Stacy:

Post your new problem in a new thread. it will get some reads then. I will say that I have never seen that error so my suggestion might not be best.

There is a Jet repair that is discussed on Microsofts website. You might check into that. You can also create a new db, import all of your stuff from the corrupt db into that, which forces a repair to see if that works.

But first I would post a new thread under Modules.

 

[StacyStacy]

Thanks.

 

[StacyStacy]

Spitting db

Ok. I was able to successfully split the db into what appears to be BE only. I made a backup copy of it. Now, what is my FE? I can identify the BE. Help. This is new to me.

Thanks

 

[Autoeng]

Ok when you created the split db it added a _be to the backend. The front end is the original but now it doesn't have any tables in it. They are in the back end. What version of Access are you using? You now have a link from the FE to the BE tables but it depends on what version you are using as to where the link is.

 

[StacyStacy]

I am using Access 2000.

 

[StacyStacy]

My BE has tables only and my FE has everything.

 

[Autoeng]

In A2K table links are maintained in the FE under Tools-Database Utilities-Linked Table Manager.

Whenever you move the BE you will have to re-establish the table links using the Manager. To do so open the manager, check all of the tables you want to link to, click the "Always prompt for new location" box in the lower left corner and click OK. You will then be prompted for the new BE file location.

For instance in you application it will work like this. Locate the BE to a server directory that your users can access. Keep the FE on your pc for the moment. Now use the manager to link to the BE on the server. Now copy your FE and make the changes needed for the 2 user groups to them. Copy both FE's over to the server. On the user's pc copy the FE from the server to their machine. Now your users start making reports and stuff. When you need to update the FE make the changes to the copy that the data entry users use that you left on your PC. Then have the users that can make reports upload their FE's to separate directories that you have access to. Open the copy of the report creators FE that you left on your PC. Go to File-Import and select the first user's FE and import in any reports that they have created. Do the same with the second user's FE. Now import from the data entry FE on your pc anything that you changed. Now you have updated copies of the FE's for each user group. Upload the new FE's to the server, copy over to appropriate user's machine. Rinse, lather, repeat.

P.S. I sent you a private message.

 

[Autoeng]

The FE has tables too? Oh, I understand. Look at the table in the FE in the db window. Is there a little arrow beside the table name? That means they are linked tables (in another location).

 

[StacyStacy]

Yes. In the FE, it contains everything (tables, queries, forms & reports) with arrows pointing left ... which means they are linked.

The BE only contails tables, not the queries, forms or reports ...

The FE should be used for the users that create reports? Or should I import everything into the BE and assign a security group that allows only access to 1 form to key in data?

 

[StacyStacy]

PS too: I replied to your private msg.

 

[StacyStacy]

Please check msgs.

 

[StacyStacy]

Thanks. I did create the BE and the FE. Made copies of the FE and altered the db to only a switchboard and form.

For the data entry users, I completed the work and the db works --- here's the new problem: I created the entire project under a different directory. After I placed the users frontend back onto their drive along with a copy of the BE, I opened the db and immediately the switchboard opened. I clicked onto the button to open the form. It gave me an error message saying pointing back to my testing directory, not their's and prompted me to check the path.

Was it ok to split this db in my own directory and then move it to theirs? Or is there a simple solution to shortcut button in the switchboard? This db worked wonderfully on my computer, but not on their drive.

Thanks

 

[Autoeng]

Hey Stacy.

You forgot that in a previous post to this thread I told you about that. You need to relink the tables. First just put the BE on the server. Leave the FE on your computer in a directory that you can recreate on the users machine. Now open the db and go Tools-Datebase Utilities-Linked Table Manager. Check each of the tables and click the little box in the left hand bottom corner that says "Always prompt for new location". The rest of the process is self explainatory. Now copy the FE from you PC to the server. Go to the users machine and create a directory the same as yours to hold the FE. Copy the FE to there. Now you will not have to relink the tables because the locations are the same as on your PC (unless the server mapped drive is a different letter on the users machine than yours).