CODEWITHSUNDEEP
vbams-accesslocking
magavo
magavo

Reputation: 61

Access Locking Shared Mode - Editing a Report

I'm doing a db in access 2002 and I've had some problems with the locking shared mode. I have an app that has a lot of programs and almost 10 users logged on. And one of things that the app do is open a report, that user choose the program and the number (usually has 4 numbers for each program), and before the report open, I open it in hidden mode and I edit the report for current program/number, with this rotine I just have one report that may turn in hundreds... saving memory and optimizing it. Then I save it and open it again in view mode.

But the problem is when has other person using the app, so the access can't edit and save report... just in exclusive mode!

Has a cmd in vba to allow momentarily changes in shared mode? I don't know, like freeze all user, save and then unfreeze them?

Or any other suggestion?

About create a Front-End/Back-End I think that is impracticable, cause is a beta version and I have to update it often and I already tried to do this also, but it's became too slow... I splitted then in a database(just tables) into network and front-end with all querys, forms, reports and linked tables in local PC, but it really became tooo slow. If someone can help me let it faster would solve my problems too

I splitted the db and I'm trying optimize it. I read a lot about it on the web and I changed all Access setting that I saw that need to be changed and now I get a faster program. But slower than with a single app.

But now there are just fell Forms that making my app slow.

For example I have some Forms that always when I close it I spends a long time waiting it to close ;( So I realised that this Forms are always saving before close. And always that a form need to save(with linked tables taht are in the network) waste a lot of time, so I need to avoid this.

But I didn't get it so far...

I realise that this forms are saving because in form_open I hidde some columns(that are different for each program) and edit it caption. And then if I need to close form, it saves and waste this such time!

How could I hidde/edit this columns withou need to save form? Or how could I close form without save structure changes?

I know how to do that with just a button, but these forms are datasheets and I can close it only in "X" Form button. And unfortunattely Access dont have BeforeClose event, and in a OnClose event it save before go to this sub!

Upvotes: 2

Views: 757

Answers (1)

HansUp
HansUp

Reputation: 97100

See if you can make use of a WhereCondition with the DoCmd.OpenReport Method to avoid the need to modify your report's design at run time.

The WhereCondition is applied to your report's existing record source query as if it where written into that query's WHERE clause.

So if the record source for YourReport is ...

SELECT program_id, some_number, another_field
FROM YourTable;

... then this ...

DoCmd.OpenReport "YourReport", _
    WhereCondition:="program_id = 7 AND some_number = 22"

would give you the same set of rows as would revising the record source to this ...

SELECT program_id, some_number, another_field
FROM YourTable
WHERE
        program_id = 7
    AND some_number = 22;

This advantage of this approach, if you can make it work for your situation, is that you would no longer need exclusive access to the db since you're not actually changing the report's design.

The related issue about beta status making it impractical to split the application is something you should re-consider carefully. Splitting ensures you can easily preserve the data in the BE when you roll out changes to the FE application. Even if you've come up with another method to avoid losing data when you change versions, that method can not be simpler than segregating the data into a BE file.

And when you split the application, each user should get their own copy of the FE file which is stored locally on their machine's hard drive; those FE files will contain links to the tables in the BE file which is stored on a file share.

Keeping the users' FE applications updated as you release new versions is a problem which has been solved. For example, see Tony Toews' Auto FE Updater. And you can find other approaches by searching the web.

If your concern is performance with a split application, check Tony's Microsoft Access Performance FAQ.

Upvotes: 1

Related Questions