Re: ldb file and Exclusive Access question

Guys,
I appreciate the 'good advise'...just not the manner in which it was
delivered. If I had my way I'd be using an SQL server to pull the data and
Excel to report it. I'd bundle the whole thing into a nice little
schedulable SQL server package. I did this in my prior job where we ran 100
reports a day from a half dozen different data sources inclusing several we
made ourselves.

My current position is not as sophisticated nor is it really necesary to be
so. We essentially have a warehouse (source data) on an SQL server.
Typically an Access data base will link to that server, pull in some data and
report it via Access (Me I prefer to link Excel to the resultant table in
Access)

There really is little chance of data corruption as we do not UPDATE the sql
server warehouse from Access. It is possible that some YTD tables we keep
could beocome corrputed. They could easily be recreated by running a query
for the year instead of just appending the current month. Also, typically
there is ONLY 1 of 4 possible people in the db at a time. Yes, I have a
little task scheduler package that zips the dbs each night in addition to the
IT backup.

Again, we do not have an access APPLICATION running in my area with multiple
users logged in. Other areas do have such enterprises and they do split the
db. I am not arguing that it is a good practice. I would be nice to have
the time to consolidate our dbs, rename them, rewrite some queries for better
efficiency and even likely consolidate and eliminate some reports. Still we
are limited on heads and limited on budget and I can see why management would
not want us to invest out time doing this fo4 simple data extractions. Newly
created dbs sure but existing ones are not worth the effort at this point. I
am as proactive as I am able. This is why instead of hitting a button to run
a report that is generated every day, I chose to try to develop a quick
little method to schedule it. This is where I hit my little security snafu.

This problem arises when I access a function to open an Excel file, refresh
the pivots and save the file under a different name. It worked fine in the
first db fro the 1st report I wanted to schedule. I tried the same thing for
a different process in a second db and was getting General ODBC errors on the
functions: refresh ALL command and the task scheduler task died. I was
perpelexed until I realized that both myself and cohort could not be in the
db at the same time. It was then we started opening dbs and found that
sometimes we could each be in and sometimes not..

SHARED ACCESS in my experience always has meant who ever has the db opened
first can modify stuff. Subsequent users are allowed in but granted read-
only access.

EXCLUSIVE ACCESS in my experience always meant who ever opens the db first
owns it; no one else gets in.

The question I was asking was what made some of our dbs open EXLCUSIVELY and
others open SHARED when each was apprently defaulted to SHARED mode and why
do not see a .lbd file when opened exclusively.

So forgive me for perhaps overreacting but does anyone know the answers......


PS:
I have eperienced a problem in the past where the .lbd file stays out there
and the .mdb is gone; thus you delete the .ldb and you are fine. This has
nothing to do my issue.

PSS:
The 3 of us all have the same security.

Larry Linson wrote:
>The good advice you received isn't just a matter of personal preference; if
>you have multiple users logged in to the same front end or monolithic
>database, the probability of database corruption is significantly increased.
>
>Some people run unsplit for years with no problem, then after they make some
>minor, and apparently innocuous change, have frequent occurrences of
>database corruption.
>
>Of course, I don't know your management's attitudes regarding schedules
>versus data integrity, but I can assure you that in nearly 50 years in the
>computer business, I'd have found my job in serious jeopardy if data
>integrity was blown and any of my bosses found out that I knew that was
>likely to happen, had a good way to prevent it, and didn't. They would, at
>least, expect me to explain the situation so they could evaluate the risk of
>adhering to a (likely arbitrary) schedule versus making sure the data was
>safe.
>
>How often do you get all the users out and make your own backup copy of the
>databases? No, the daily backup performed by IT on the servers may NOT be
>sufficient, not if there are operations in progress in the Access
>applications.
>
>How serious are corruptions? Most of them are a minor inconvenience... get
>everybody out of the DB, run Compact and Repair, and you are back in
>business. If that happens frequently, during working hours, it can be a
>major inconvenience. If Compact and Repair does not fix the problem, it may
>be a lot more than an inconvenience.
>
>There are a number of things that can leave "something" running (some call
>it a "ghost" process) after the last user seems to have logged out. The best
>source of information I know about regarding multiuser performance and
>avoiding corruption in the multiuser environment is MVP Tony Toews' site,
>http://www.granite.ab.ca/accsmstr.htm.
>
>If the users are opening Access, and then running particular databases, I
>don't know any way to guard against them logging in for exclusive use. If
>they are creating Access objects, they must have exclusive Access in
>versions 2000 and later.
>
> Larry Linson
> Microsoft Access MVP


--
Message posted via http://www.accessmonster.com
.