Make Table Query - Is There No One Who Can Tackle This? 1

[mrgulic]

I have a DB called "Future Events" that is located in a read-only DIR. Problem is that when the database is opened by someone the "ldb" is not not allowed to be updated since most people have only read-only access.
The solution i came up with was to create an second DB (lets call it FE-II) on a globally shared folder to "push" the data too each day. In the "Future Events" DB i created an SQL Table Delete Function that removes the old data from FE-II via a linked table to FE-II. This works great.

Problem is when i try to push new data there and someone is currently looking at the old data (which by the way has been deleted via the SQL function), it doesnt allow me because "table is currently locked my another user".

Ive tried an update querry but it either doesnt work, or it takes far to long.

I hope i have given enough information.
If my words are confusing, the diagram of this flow is linked below and may help clear up any confusion in my words.

http://photos.yahoo.com/bc/mrgulic/.../MiscPics&.dnm=SQLTableDeleteMake.jpg&.src=ph

Thanks in advance for any assistance. Please be detailed in explainations.

mrgulic@yahoo.com

 

[Smack]

Yo Hack,
I read the post and looked at the diagram, but I'm not sure what your trying to accomplish. If you want to update records, and you have permission to update and others have read only, why don't you just do it live on the network?
-Smack

 

[mrgulic]

The Future Events db will resdide in a folder that global users have only read-only access to. The "ldb" has to be able to "update" itself with whos using the db in oder for userto acces it. Global users cannot update anthing in that DIR, only read-only. If some one already has it open, anf they dont have "update" permission to that DIR then they are out of luck.

I need to push that data to a DIR (db) that is globally updateable so the "ldb" can "update".

I have no control over that protected DIR so i have to find another solution. They will not for any reason allow anyone outside of thier grop to have more than read-only permissions (except me, but that wont help in the long run)

when i speak of a Local db i actually mean an Network share that actually resides in out building. the other one is in TX.

Sorry for any unnecessary confusion.

 

[MichaelRed]

Rendom errors. If the 'greast nebolous "THEY"' don't want your input anyway, just do a daily update/copy of "their" table to one of your own. Use your own for your users/groups. Who cares what their db says?

do your current workaround on a per record basis. If the prcess of up-dating the "THEIR" db fails, just skip the delete of your records. On the next 'cycle' (or at some point) the update will succede. NO more problem.

Have you app connect through the "THEIR" db via code using YOUR username/password. If you structure it appropiatly, hte only time the "THEIR" db will be open reflecting YOUR username will be during the actual few seconds the table is being updated.

Tell the "THEIR" db admin to "grow up". These are either trused employees, in which case they should be TRUSTED to do as told, or they are suspects in som way and shouldn'y be accessing any company info, so should probably be in the que to visit the exit interview at H.R.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[mrgulic]

Thats pretty funny.

Unfortunatly it isnt as simplas that.

The information in their db can only be gotten from them, their group. This info has to be kept safeguarded from morons who could accidently alter or delete data accidently. The result of that could result in the loss of production, hence money.


Tonight at work i will create a working example that you all can DL and see the anomolie.

Maybe then it will be clear. Any other solution oither then a fix on my side of the network wont work. I could probably be much eaiser if they would use the workgroup feature "mdw". Im not sure the group has the knowledge. They will allow me to suggest smalle changes. The one db i deal with is in Europe, he inserted the code i wrote and the make table querries.

I fix cannot deal with permissions or access to their DIR. It has to come from my side.

Thanks to all for your assistance. I will insert a link tomorrow moring after work to a working example.

 

[mrgulic]

here is an example. Be sure to have opne the form in the shared db or you wont get the error.

http://us.f1.yahoofs.com/users/bef1165e/bc/MiscPics/Shared+97.mdb?bcmQb.7ApxYiRceW

http://us.f1.yahoofs.com/users/bef1165e/bc/MiscPics/Unshared+97.mdb?bcmQb.7AgDsxPVET

 

[mrgulic]

Do a R-Click, "Save Target As" to get the db's

 

[llocklin]

I have a similar situation with a datamart type of app. I perform all processing in a master and copy the result tables to the 'shared' copy on a routine basis. If the copy fails, I trap the error, read the .ldb to determine the user, 'net send' a get out message and wait for them to get out.

If they don't get out on my timetable, I write a semaphore file to the network share. All users must access the shared data through a database I provide. A hidden form routinely checks for the presence of this file. When the file is found, the code in the form closes everything and quits freeing up the shared database.

Periodically, I keep trying to perform the update.

While all this foolishness works, the real secret to success is having the entire process run automatically during a period when users aren't in (via the schedule service).

 

[BNPMike]

"I have a DB called "Future Events" that is located in a read-only DIR "

Read only -whatever for?? Access should run in read/write directories only. If you want to control reading and writing to data then you use Access's rich set of access privilege facilities. Get rid of this problem and everything else sorts itself out???

Never get involved in complex workarounds when there is a simple straightforward issue that can be solved. You will not make CIO if you are seen to waste time in that fashion. Confront the problem. Solve it. Move on to the next thing.
mike.stephens@bnpparibas.com

 

[mrgulic]

BNPMike:

Its read-only to everyone but persons in that group.
They do publish the data daily to HTML pages so everyone can see it. I want to be able to run querries on the data it, and you can't run querries on dates fileds when the link is to an HTML file. Ive tried.

 

[mrgulic]

llocklin:

at last, someone who listens to me and doesnt try to tell me how im doing everthing the wrong way.

Id be very interested in seeing a working example.

I've tried writing code that in Access that detects that the records have been changed (deleted) and and automaticall closes the form an wont allow it to open for a period of time. i just cant seem to get it to see that the records have have been changed (deleted). i thought that "me.dirty" might work, it doesnt.

i cant do it on a shchedule either. we are a global corporation with 24/7/365 operations.

is this app have code that could be inserted into access?

thanks again

 

[THoey]

Hack,

Not to sound mean, but when everyone tells you that you are "doing everthing the wrong way", it might be for a reason. A lot of us have either been there or have enough experience to see the possible problems you are going to have.

It seems to me like an awfully difficult way to implement the security that Ms. Access has already provided. You mentioned it earlier in one of your posts (MDW). I would (and that doesn't mean you have to, but...) research the information to establish this security (their is excellent help in the FAQ section, and then discuss this with the folks on your other end.

Sorry if that doesn't seem like the easiest way, but just because it doesn't seem easy, doesn't mean it is not the *best* way. Terry M. Hoey

 

[mrgulic]

Apparently I have failed to explain this to eveyones understanding. Thats my fault.

I cant force an admin to implement the use of the "workgroup" or any other feature to to address security concerns rather than his current "Read-Only" global plan.

I will just hope that "llocklin" replies to my request for the working example.

I thank all of those who replied to this thread, but please, no comments on how this can be done "the right way".

I know the right way to do it, i just dont have the authority to force others to conform to normal Access security protocols that would make this task so much eaiser.

Thanks again to all.