Multiple Access to Reports

[businessman]

I have a Database that is on a network and allows everyone to be able to use this database and make changes at the same time. When more than one person try to use a button that i created that prints a report, It doesn't work. Why?

 

[Banana]

did you split it? If not, look up on splitting database.

 

[businessman]

well the way the button works is that I have a series of checkboxes that filter a form. i don't know if its continuous or another type because i'm not that familiar with the terminology yet. in my form code, an SQL string is set at the recordsource for the filter that is applied and set as the record source for the report. this means that the user can check the boxes to filter, click the report button, and a report will print showing just the filtered data. The button works fabulous with one person but if you have two people in the database at one time, it doesn't work. I don't think that I can split it cause everyone needs to used the same forms and such. i should be the only one who will be creating new forms or anything in this database. The only thing others will be doing is using my forms to use, enter, and munipulate data.

 

[Banana]

Did you read about splitting?

What it mean is to move tables to a external database and have all your forms link to those table. This will allow users to share same tables. There will be no changes, except for that tables will be in a different file.

 

[businessman]

will the users access the database the same way? or do they have to choose either the table database or the form/report/ect database?

 

[Uncle Gizmo]

Banana's advice is good.

You should do as advised and read up on splitting your database.

If you had done this you would have returned with some sensible questions!

Banana suspects, and I think correctly, that you have one single MS Access database on the server. The splitting refered to (and there is a wizard to help you with this) is making several new databases, one, the main one which will reside on the server with just the tables in, and another which is called a frontend. You have multiple copies of the frontend, everyone has a copy on their local machine, with links back to the data store database on the server.

 

[businessman]

I did it and it didn't change anything. There is no need to be rude Uncle Gizmo. I'm new at this and just trying to learn more from others. I find there is a lot that the help and other things leave out.

 

[Uncle Gizmo]

I must apologize if my blunt comment came across as rudeness! It was not rude just blunt correct statement of the fact that you have not read up anything on splitting databases.

If you read my other posts you will find that I am generally very tolerant. Although I occasionally have had lapses! If I wanted to be rude then you would be in no doubt about it.

Why I was blunt was because your answer appeared to indicate that you had totally ignored the good advice from Banana! If you have tried it, why did you not say so? in fact you actually say "I don't think that I can split it cause everyone needs to used the same forms and such" so you haven't tried splitting it yet have you?

I repeat again, read up on splitting your database, as I said earlier, this basically involves creating two new databases one with the tables in only and one with the other objects in, the forms, the reports, queries, everything else except the tables. Now you link this with the linked table manager to your database containing the tables, once you have completed this, then make copies of it and distribute the copies to your local Desktop machines.

Once you have read up on splitting your database, come back with some sensible questions!

 

[Banana]

And, when you said you did it and didn't work, that is not very helpful.

Please explain exactly what you did, (did you use Database Splitter Wizard? or not?), and tell us what happened, and what error you got.

Also, while I agree that Mircosoft Help suck big green donkey balls, you can search this forum, MSDN, and even google on whatever Mircosoft Help is short on.

 

[businessman]

when i said it(splitting the database) doesn't work i mean that the record source of the report doesn't change when multiple users are in the database at one time. when one of my check boxes are checked, it changes the recordsource of the report so it will print what the checkboxes say to filter. when multiple users are in the database it will only print the report with the last recordsource prior to multiple users in the database

I tried splitting the database, and when I did (using the wizard) nothing changed.

 

[Banana]

Ok. Recordsource shouldn't change; that's expected.

Tell me, after splitting the database, do you get blue arrows next to the list of tables names in your database windows? That indicates that the tables is now externally referenced.

If it is now linked, you would then move the back end (the database with tables only) to the server, and copy the front end to each of your user.

Did that make sense?

 

[businessman]

yes it does.

After splitting i did get the blue arrow next to the table names. it didn't work when front and back end were in the same folder. when i moved the front end to the user computer it worked fine. I have two more questions, is there a way to unsplit once split and is there a way to look for a server name without a drive name so there is no problem with people who have the drive mapped on their computer with a different drive name?

 

[Banana]

Of course it wouldn't work if both BE/FE were used together; the difference is in having multiple copies of FE for each user to use so BE is shared along the users. If users used the same copy of FE, it'd be just another unsplitted database.

I hope you made a back up before you splitted the database... If not, well, that's one more lesson learned.

But yes, you can unsplit it. File -> Get External Data -> select the back end and select all objects.

Second, Tools -> Database Utilites -> Linked Table Manager, select all and check the box to alway prompt for update, then you can navigate to whereever the BE is. Make sure you do that before you give FE out to user so they don't get confused when FE is unable to locate the BE.

 

[businessman]

I thank your for your patience. It works when I split the database. It's too bad MS doesn't try and get something that teaches you more about Access and the ntricacies of how it works. I feel like I dont' know nearly enough but at the same time have passed up the help menu.

 

[businessman]

oh and i definately made 3 back-ups...lol

 

[Banana]

Glad it worked. I do sympathize with you re: MS help, as long you keep searching the forum, MSDN and other sources, you can't go far too wrong.